Convert data in the database to an insert statement

Create procedure sp_generate_insert_script @ table_list varchar (8000) = '*'
-- Format of @ table_list:
-- 'Tb' -- single table
-- 'T1, T2, T3 '-- table list
-- '*' -- All tables in the database
As
-- Author: pbsql
Declare @ table_name nvarchar (128)
Declare @ column_list varchar (8000)
Declare @ values_list varchar (8000)
Declare @ SQL varchar (8000)
Declare @ MSG varchar (8000)

Create Table # result (SQL varchar (8000 ))

If @ table_list = '*'
Begin
Select @ table_list = @ table_list + ',' + name
From sysobjects
Where xtype = 'U' and name <> N 'dtproperties'
Set @ table_list = stuff (@ table_list, 1, 2 ,'')
End

While @ table_list <>''
Begin
If charindex (',', @ table_list)> 0
Select @ table_name = left (@ table_list, charindex (',', @ table_list)-1 ),
@ Table_list = stuff (@ table_list, 1, charindex (',', @ table_list ),'')
Else
Select @ table_name = @ table_list,
@ Table_list =''

If exists (select 1 from sysobjects where xtype = 'U' and name = @ table_name)
Begin
Select @ column_list = '', @ values_list =''
Select
@ Column_list = @ column_list + ',' + name,
@ Values_list = @ values_list + '+ '','' +' +
Case when xtype in (175,167, 36) then -- char, varchar, uniqueidentifier
Isnull (''' + Replace ('+ name + ','''''''', ''''' ''') + ''', ''null '')'
When xtype in (239,231) then -- nchar, nvarchar
'Isnull (''n''''' + Replace ('+ name + ','''''''', ''''' ''') + ''', ''null '')'
When xtype in (61,58) then -- datetime, smalldatetime
''Isnull (''' + convert (char (23), '+ name +', 121) + '''''''', ''null '')'
Else -- Digital
'Isnull (convert (varchar (20), '+ name +'), ''null '')'
End
From (select a. Name, A. xtype
From syscolumns A, sysobjects B
Where B. xtype = 'U' and B. Name = @ table_name and A. ID = B. ID
And a. xtype not in (173,165, 34,35, 99,98, 189)
-- Not binary, varbinary, image, text, ntext, SQL _variant, timestamp
) T

Select @ column_list = stuff (@ column_list, 1, 1 ,''),
@ Values_list = stuff (@ values_list, 1, 4 ,''),
@ SQL = 'select' insert into '+ @ table_name +' ('+ @ column_list + ')'
+ 'Values (''' + @ values_list + '+ '')'' SQL from [' + @ table_name + ']'

If objectproperty (object_id (@ table_name), 'tablehasauth') = 1
Insert into # result (SQL)
Exec ('select' -- table name: '+ @ table_name + ''' SQL Union all'
+ 'Select' 'set identity_insert '+ @ table_name + 'on' SQL Union all'
+ @ SQL + 'Union all'
+ 'Select' 'set identity_insert '+ @ table_name + 'off'' SQL ')
Else
Insert into # result (SQL)
Exec ('select' -- table name: '+ @ table_name + ''' SQL Union all' + @ SQL)
End
Else
Begin
Set @ MSG = 'can't generate the insert script of the table ''' + @ table_name
+ ''', Because it does not exist in the system catalog .'
Drop table # result
Raiserror (@ MSG, 16, 1)
Return
End
End
Select SQL from # result
Drop table # result
Go

Call format example:
Sp_generate_insert_script 'tb' -- convert data in table TB
Sp_generate_insert_script 'T1, T2, T3 '-- convert data in tables T1, T2, and T3
Sp_generate_insert_script '*' -- convert data in all tables

Advantages:
1. Three Parameter formats are supported for convenient calling
2. Supports the export of ID Columns
3. Put the results in a result set for convenient copying

Disadvantages:
1. The following types are not supported: binary, varbinary, image, text, ntext, SQL _variant, timestamp
2. due to the influence of the SQL Server String Length (B), if the table contains too many fields or the table's data length is too long, it will be truncated and an error will occur, but the general requirements can be met, A maximum of 100 fields can be supported per table, and a maximum of 6-7 kb can be supported for each record (depending on the number of fields and the length of the field name)