SQL Server Export Insert script code _mssql

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