SqlServer表数据导成Insert语句
1 create proc spGenInsertSQL
2 @TableName as varchar(100)
3 as
4 --declare @TableName varchar(100)
5 --set @TableName = 'orders'
6 --set @TableName = 'eeducation'
7 DECLARE xCursor CURSOR FOR
8 SELECT name,xusertype
9 FROM syscolumns
10 WHERE (id = OBJECT_ID(@TableName) )
11 declare @F1 varchar(100)
12 declare @F2 integer
13 declare @SQL varchar(8000)
14 set @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''
15 OPEN xCursor
16 FETCH xCursor into @F1,@F2
17 WHILE @@FETCH_STATUS = 0
18 BEGIN
19 set @sql =@sql +
20 + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end
21 + 'replace(ISNULL(cast(' + @F1 + ' as varchar(8000)),''NULL''),'''''''','''''''''''')'
22 + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end
23 + char(13) + ''','''
24 FETCH NEXT FROM xCursor into @F1,@F2
25 END
26 CLOSE xCursor
27 DEALLOCATE xCursor
28 set @sql = left(@sql,len(@sql) - 5) + ' + '')'' FROM ' + @TableName
29 exec (@sql)
30 go
31 --使用
32 exec spGenInsertSQL <TableName>
