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 ')