SQL Server Stored Procedure generation insert statement instance

You must have had such troubles. The same table and different databases cannot be added to select insert.
Therefore, you must enter a stored procedure to indicate that the insert statement will generate data for you.
Of course, the number of data tables is too large. You 'd better use another method.

Copy codeThe Code is 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 (23), '+ name +', 121) + ''' -- datetime
Then 'case when' + name + 'is null then' 'null' 'else' + ''' +' + 'Cast ('+ name +' as varchar) '+ ''' + 'end'

When xtype in (167)

Then 'case when' + name + 'is null then' 'null' 'else' + ''' + 'replace (' + name +', ''', ''') '+ ''' + 'end'

When xtype in (231)

Then 'case when' + 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 + ', ''', ''') as Char ('+ cast (length as varchar) + ')) + ''' + 'end'

When xtype in (239)

Then 'case when' + name + 'is null then' 'null' 'else' + '''n' ''' +' + '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' insert into ['+ @ tablename +'] '+ left (@ SQL, len (@ SQL)-1) + ') '+ left (@ sqlValues, len (@ sqlValues)-4) +') 'from' + @ tablename
Print @ SQL
Exec (@ SQL)
End

SQL statement

Final result:
Insert into [SysSample] ([Id], [Name], [Age], [Bir], [Photo], [Note], [CreateTime]) values ('0002ca83-AF2F-4D8F-A345-33CA1CC7CF3C ', 'job scheduling system', 18, '2017-01-02 21:42:30. 013 ', '', NULL, '2017-01-02 21:42:30. 013 ')
Insert into [SysSample] ([Id], [Name], [Age], [Bir], [Photo], [Note], [CreateTime]) values ('0004a6f3-EC28-4D1F-BA40-0FC4B2218C92 ', 'job scheduling system', 18, '2017-07-09 19:36:00. 060', '', NULL, '2017-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', 18, '2017-05-16 15:21:20. 070', '', NULL, '2017-05-16 15:21:20. 070 ')
Insert into [SysSample] ([Id], [Name], [Age], [Bir], [Photo], [Note], [CreateTime]) values ('000bfbb0-B37D-4D6E-9FA2-3069D4F18F84 ', 'job scheduling system', 18, '2017-04-11 11:41:50. 030', '', NULL, '2017-04-11 11:41:50. 030 ')
Insert into [SysSample] ([Id], [Name], [Age], [Bir], [Photo], [Note], [CreateTime]) values ('000c2cbc-E358-4469-BC2C-04F4DDCD72CD ', 'job scheduling system', 18, '2017-05-06 16:07:00. 037', '', NULL, '2017-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', 18, '2017-01-23 20:52:30. 030', '', NULL, '2017-01-23 20:52:30. 030 ')