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.
Copy codeThe 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