Sqlserver export and insert script code

During work, we often encounter the need to copy data from a remote client database to a local machine for testing. It is not worthwhile to download the entire database. The following script can be used to generate an Insert script based on the specified table, copy the script to a local machine for execution, which is a lot faster.

During work, we often encounter the need to copy data from a remote client database to a local machine for testing. It is not worthwhile to download the entire database. The following script can be used to generate an Insert script based on the specified table, copy the script to a local machine for execution, which is a lot faster.

Of course there are other tools to do this, but this script will be useful if the customer does not allow you to install things in the server.
The Code is as follows:
DECLARE @ tbImportTables table (tablename varchar (128), deleted tinyint)

-- Append tables which you want to import
Insert Into @ tbImportTables (tablename, deleted) values ('tentitytype', 1)
Insert Into @ tbImportTables (tablename, deleted) values ('tattribute', 1)
-- Append all tables
-- Insert Into @ tbImportTables (tablename, deleted) select table_name, 1 from INFORMATION_SCHEMA.tables where table_type = 'base table'

DECLARE @ tbImportScripts table (script varchar (max ))

Declare @ tablename varchar (128 ),
@ Deleted tinyint,
@ Columnname varchar (128 ),
@ Fieldscript varchar (max ),
@ Valuescript varchar (max ),
@ Insertscript varchar (max)

Declare curImportTables Cursor
Select tablename, deleted
From @ tbImportTables

Open curImportTables
Fetch Next From curImportTables Into @ tablename, @ deleted

WHILE @ Fetch_STATUS = 0
Begin
If (@ deleted = 1)
Begin
Insert into @ tbImportScripts (script) values ('truncate table' + @ tablename)
End

Insert into @ tbImportScripts (script) values ('set IDENTITY_INSERT '+ @ tablename + 'on ')

Set @ fieldscript =''
Select @ fieldscript = @ fieldscript + column_name + ', 'From INFORMATION_SCHEMA.columns where table_name = @ tablename and data_type not in ('timestamp', 'image ')
Set @ fieldscript = substring (@ fieldscript, 0, len (@ fieldscript ))

Set @ valuescript =''
Select @ valuescript = @ valuescript + 'case when' + column_name + 'is null then'' 'null''' else ''' + convert (varchar (max ), '+ column_name +') + ''' end + '','' + 'from INFORMATION_SCHEMA.columns where table_name = @ tablename and data_type not in ('timestamp ', 'image ')
Set @ valuescript = substring (@ valuescript, 0, len (@ valuescript)-4)

Set @ insertscript = 'select' insert into '+ @ tablename +' ('+ @ fieldscript + ') values ('+ ''' +' + @ valuescript + '+ '') ''from' + @ tablename
Insert into @ tbImportScripts (script) exec (@ insertscript)

Insert into @ tbImportScripts (script) values ('set IDENTITY_INSERT '+ @ tablename + 'off ')

Insert into @ tbImportScripts (script) values ('Go ')
Fetch Next From curImportTables Into @ tablename, @ deleted
End

Close curImportTables
Deallocate curImportTables

Select * from @ tbImportScripts