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
Copy Code code as follows:
Create proc [dbo]. [Spgeninsertsql] (@tablename varchar (256))
As
Begin
DECLARE @sql varchar (8000)
DECLARE @sqlValues varchar (8000)
Set @sql = ' ('
Set @sqlValues = ' VALUES (' + '
Select @sqlValues = @sqlValues + cols + ' + ', ' + ', @sql = @sql + ' [' + name + '], '
From
(Select 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) ' + ' end '
When xtype in (58,61)
--then ' "' +convert (char), ' +name+ ', 121) + '" ', "'--datetime
Then ' case when ' + name + ' is null then ' ' null ' else ' + ' ' ' + ' + ' ' + ' ' + ' ' + ' + ' as varchar ' + ' + ' '
When xtype in (167)
Then ' case when ' + name + ' is null then ' null ' else ' + ' ' + ' ' + ' + ' replace (' + name+ ', ' ' ', ' ', ' ', ' ', ' ' ', ' ' ' + ' + ' "" ' + ' end '
When xtype in (231)
Then ' case if ' + name + ' is null then ' null ' ' Else ' + ' ' N ' ' ' + ' ' + ' + ' replace (' + name+ ', ' ' ', ', ' ', ' ', ' ', ' ' ' + ' + ' "" ' + ' end '
When xtype in (175)
Then ' case when ' + name + ' is null then ' null ' ' Else ' + ' ' + ' + ' cast (replace (' + name+ ', ' ' ', ', ' ', ' ', ' ' Char (' + cast (length as varchar) + ')) + ' "', ' + ' end '
When xtype in (239)
Then ' case when ' + name + ' is null then ' null ' else ' + ' ' N ' ' + ' ' + ' + ' cast (replace (' + name+ ', ' ' ', ', ' ', ' ', ' ' Char (' + cast (length as varchar) + ')) + ' "', ' + ' end '
Else ' ' NULL '
End as Cols,name
From syscolumns
WHERE id = object_id (@tablename)
) T
Set @sql = ' SELECT ' INSERT into [' + @tablename + '] ' + left (@sql, Len (@sql)-1) + ' "+ Left (@sqlValues, Len (@sqlValues)-4) + ' From ' + @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 ')