Of course there are other tools to do this, but the script will work if the client doesn't allow you to mess with the server.
Copy Code code 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 for
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 D Ata_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 + ') + ' ', ' ' ' ', ' ' ', ' ' from information_schema.columns where table_name = @tablename and Dat A_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
