从msaccess导入到SQL时如何设置目标表中列的标识 [英] How to set identity for a columns in destination table while importing from msaccess to 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

推荐答案

您可能需要提供一个更完整的示例.

也许在存储过程运行时看到错误消息，在执行SET IDENTITY语句时，新表(table1)尚不存在.新表(table1)由SELECT INTO语句创建.

例如，如果在独立的"SQL查询"窗口中执行此SQL，它将返回正确的预期结果:

You might need to provide a more complete example.

Perhaps you are seeing an error message when your stored procedure runs, at the time the SET IDENTITY statement is executed, your new table (table1) does not yet exist. The new table (table1) is created by the SELECT INTO statement.

For example, if you execute this SQL in a standalone SQL Query window, it returns the correct expected results:

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时如何设置目标表中列的标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！