create table table1 (id int identity(1,1),aa varchar(100),bb varchar(100),val int)
go
insert table1 values ('12345678901234567890123456789012345678901234567890123456789012345678901234567890','aa',1)
go
exec spGenInsertSQL 'table1'
create proc spGenInsertSQL
@TableName as varchar(100)
as
--declare @TableName varchar(100)
--set @TableName = 'orders'
--set @TableName = 'eeducation'
DECLARE xCursor CURSOR FOR
SELECT name,xusertype
FROM syscolumns
WHERE (id = OBJECT_ID(@TableName))
declare @F1 varchar(100)
declare @F2 integer
declare @SQL varchar(8000)
set @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''
OPEN xCursor
FETCH xCursor into @F1,@F2
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql =@sql +
+ case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end
+ 'replace(ISNULL(cast(' + @F1 + ' as varchar(8000)),''NULL''),'''''''','''''''''''')'
+ case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end
+ char(13) + ''','''
FETCH NEXT FROM xCursor into @F1,@F2
END
CLOSE xCursor
DEALLOCATE xCursor
set @sql = left(@sql,len(@sql) - 5) + ' + '')'' FROM ' + @TableName
exec (@sql)
这样就可以!!!!
SET IDENTITY_INSERT table1 ON
INSERT INTO table1 (id,aa,bb,val) VALUES(1,'xxx公司','xxx公司',NULL)
INSERT INTO table1 (id,aa,bb,val) VALUES(46,'xx部','xx部',1)
SET IDENTITY_INSERT table1 OFF
我运行下面的脚本:
SET IDENTITY_INSERT table1 ON
GO
INSERT INTO table1 VALUES(1,'xxx公司','xxx公司',NULL)
INSERT INTO table1 VALUES(46,'xx部','xx部',1)
GO
SET IDENTITY_INSERT table1 OFF
GO
为什么还出现:
Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.