从msaccess导入到SQL时如何设置目标表中列的标识

问题描述

我已成功使用链接服务器将数据库从msaccess导入到sql服务器，但未将身份属性设置为目标表中的列.

我已经编写了一个存储过程，将select *设置为query之前将identity_insert设置为on，但是它却出现了错误.

这是我的程序

I have successfully imported database from msaccess to sql server using linked servers but it doesn''t set is identity prpoerty to columns in destination table.

I have written one stored procedure with setting the identity_insert on before the select * into query but it is being gotten error.

This is my procedure

create proc 
SP_Create(@servername nvarchar(100),
@datasr nvarchar(MAX),@table nvarchar(100),
@table1 nvarchar(100),@db nvarchar(100)) 
as begin EXEC sp_addlinkedserver @server = @servername, 
@provider = 'Microsoft.Jet.OLEDB.4.0', 
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = @datasr,@provstr=''
exec sp_addlinkedsrvlogin @rmtsrvname=@servername, 
@useself = N'false',@locallogin = NULL,@rmtuser = N'Admin',
@rmtpassword = NULL declare @sql nvarchar(MAX) 
set @sql=' SET IDENTITY_INSERT '+@table1+' ON; select * into '+@table1+' from '+@servername+'...'+@table+' with (nolock) SET IDENTITY_INSERT '+@table1+' ON '
Exec(@sql) 
declare @sql1 nvarchar(MAX) set @sql1 ='EXEC master.sys.sp_dropserver '+@servername+',''droplogins''' exec(@sql1)
end

推荐答案

CREATE TABLE A ( ID INT NOT NULL IDENTITY PRIMARY KEY, Name NVARCHAR(16) NOT NULL )
INSERT INTO A ( Name ) SELECT 'X' UNION SELECT 'Y'
INSERT INTO A ( Name ) SELECT 'A' UNION SELECT 'B'
DELETE FROM A WHERE Name = 'A'
SELECT * INTO B FROM A WITH (NOLOCK)
SELECT * FROM B
-- Correctly returns {(1,X);(2,Y);(4,B)}

这篇关于从msaccess导入到SQL时如何设置目标表中列的标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！