Use SQL stored procedures to automatically generate INSERT statements for the contents of a table

Select your database

Execute the SQL that created the stored procedure

CREATE proc [dbo]. [Spgeninsertsql] (@tablename nvarchar, @sqlwhere varchar (4000)) asbegindeclare @sql varchar (max) DECLARE @sqlValues varchar (max) set @sql = ' (' Set @sqlValues = ' VALUES (' + ' Select @sqlValues = @sqlValues + cols + ' + ', ' + ', @sql = @sql + ' + ' + name +                            ', ' from (select-Case-xtype in (48,52,56,59,60,62,104,106,108,122,127) Then ' Coalesce (CAST (' + name + ' as varchar), ' null ') ' When xtype in (58,61) Then ' " "' +coalesce (varchar, ' + name + ', 121), ' null ') + '" ' When xtype in (167) t  Hen "" "" +coalesce (' +name+ ', ' null ') + ' "" "--then ' case ' + name + ' was null then ' null '" Else ' + ' + ' + ' replace (' + name+ ', ' "'" ' "'" ') ' + ' + ' + ' "+ '" + ' "" + ' + ' "+ ' when xtype in (231, 239,175) Then "" "+coalesce (' +name+ ', ' null ') + '" ' "---Then ' case w"Hen ' + name + ' is null then ' null ' ' Else ' + ' ' + ' ' + ' + ' replace (' + ' + name+ ', ' ' ' ' ' ' ' ' ' ' + ' ' + ' ') ' + ' + ' + ' '--when xtype in (175)-Then ' case ' + name + ' was null then ' null ' ' Else ' + ' "                ' + ' + ' cast (replace (' + name+ ', ' "', '" ' "') as Char (' + cast (length as varchar) + ')) + '" ' + ' "' + ' + ' + ' --when Xtype in (239)--then ' case when ' + name + ' was null then ' null ' ' Else ' + '                ' + ' cast (replace (' + name+ ', ' "'" ', ' "') ' as Char ' (' + cast (length as varchar) + ') + ') + '" ' + ' + ' end ' Else ' NULL ' ' End as Cols,name from syscolumns where id = object_id (@tablename)) T Set @sql = ' Select replace (' INSERT into ' + @tablename + "+ Left (@sql, Len (@sql)-1) + ') ' + Left (@sqlValues, Len (@sqlValues) -4) + '), ' ', ' ' ' ', ' ' ' ', ' ' ' ', ' ' ' ' ', ' [Email protected][email protected]print @sqlPRINT len (@SQL) EXEC (@sql) Endgo

Executing stored procedures

exec [Spgeninsertsql] @tablename = ' Roomconfig ', @sqlwhere = ' '

The above roomconfig is a table in my library, in the SQL 2008 I used the above statement will not find the stored procedure, can not control him

Use SQL stored procedures to automatically generate INSERT statements for the contents of a table