SQL Server stored procedures generate INSERT statements

You must have had such trouble, the same table, different database, join you can't perform select Insert

Then you definitely need a stored procedure that needs to be passed in and will give you the INSERT statement that generates the data.

Of course the number of data sheets is too large, you'd better use another way

Create proc [dbo]. [Spgeninsertsql] (@tablename varchar (256)) as BEGIN declare @sql varchar (8000) DECLARE @sqlValues varchar (8000) Set @sql = ' (' Set @sqlValu es = ' VALUES (' + ' Select @sqlValues = @sqlValues + cols + ' + ', ' + ', @sql = @sql + ' [' + name + '], ' from (Sele CT case when xtype in (48,52,56,59,60,62,104,106,108,122,127) Then ' case When "+ name + ' is null then ' ' null ' else ' + ' cast (' + name + ' as varchar) ' + '" when xtype in (5 8,61)--then ' "' +convert (char), ' +name+ ', 121) + '" '--datetime ' t
    
               Hen ' case when ' + name + ' is null then ' ' null ' else ' + ' ' ' + ' + ' ' + ' ' + ' ' + ' + ' as varchar ' + ' + ' ' When xtype in (167) then "case" + name + ' is null then ' null ' else ' + ' ' + ' replace (' + name+ ', ' ' ', ' ' ', ' ' ' ', ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' + ' ' + ' + ' ' + ' '] ' when xtyPE in (231) Then ' case when ' + name + ' is null then ' ' null ' ' Else ' + ' ' N ' ' ' + ' ' + ' + ' replace (' + Name+ ', ' ', ' ', ' ', ' ' ', ' ' ', ' ' ' + ' ' + ' ' ', ', ' ', ', ', ', ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ' ', ' En ' case Then ' + name + ' is null then ' ' null ' else ' + ' ' ' + ' + ' cast (replace (' + name+ ', ' ' ', ', ' ', ' ', ' ', ' ' AR (' + cast (length as varchar) + ')) + ' "," ' + ' "," xtype in (239) t Hen ' case when ' + name + ' is null then ' null ' else ' + ' ' N ' ' + ' ' + ' + ' cast (replace (' + name+ ', ' ' ', ', ' ', ' ', ' ' Char (' + cast (length as varchar) + ')) + ' "', ' ' + ' end ' Else ' ' NULL '" End as Col S,name from syscolumns where id = object_id (@tablename)) T Set @sql = ' SELECT ' INS
ERT into [' + @tablename + '] ' + left (@sql, Len (@sql)-1) + ' "+ Left (@sqlValues, Len (@sqlValues)-4) + ') ' + @tablename Print @sql exec (@sql) END SQL statement 

The final result:

INSERT into [Syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) VALUES (' 0002CA83-AF2F-4D8F-A345-33CA1CC7CF3C ', ' Task scheduling system ',, ' 2013-01-02 21:42:30.013 ', ', ', NULL, ' 2013-01-02 21:42:30.013 ')
INSERT into [Syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) VALUES (' 0004a6f3-ec28-4d1f-ba40-0fc4b2218c92 ', ' Task scheduling system ',, ' 2013-07-09 19:36:00.060 ', ', ', NULL, ' 2013-07-09 19:36:00.060 ')
INSERT into [Syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) VALUES (' 00094d35-7b51-4ea3-871e-ce17e293b157 ', ' Task scheduling system ',, ' 2013-05-16 15:21:20.070 ', ', ', NULL, ' 2013-05-16 15:21:20.070 ')
INSERT into [Syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) VALUES (' 000bfbb0-b37d-4d6e-9fa2-3069d4f18f84 ', ' Task scheduling system ',, ' 2013-04-11 11:41:50.030 ', ', ', NULL, ' 2013-04-11 11:41:50.030 ')
INSERT into [Syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) VALUES (' 000C2CBC-E358-4469-BC2C-04F4DDCD72CD ', ' Task scheduling system ',, ' 2013-05-06 16:07:00.037 ', ', ', NULL, ' 2013-05-06 16:07:00.037 ')
INSERT into [Syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) VALUES (' 000cb795-40ec-4783-b7a4-8d298df63b70 ', ' Task scheduling system ',, ' 2013-01-23 20:52:30.030 ', ', ', NULL, ' 2013-01-23 20:52:30.030 ')
Author: ymnets

Source: http://ymnets.cnblogs.com/

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/