I have a Table (call it A_table) in a database (call it A_db) in Microsoft SQL Server Management Studio, and there are 10 rows.
I have another database (call it B_db), and it has a Table (call it B_table), which has the same column settings as A_table has. But the B_table is empty.
What I want:
A_table to B_table.Is there any option in Microsoft SQL Server Management Studio 2012, to create an insert SQL from a table? Or is there any other option to do that?
I know this is an old question, but victorio also asked if there are any other options to copy data from one table to another. There is a very short and fast way to insert all the records from one table to another (which might or might not have similar design).
If you dont have identity column in table B_table:
INSERT INTO A_db.dbo.A_table
SELECT * FROM B_db.dbo.B_table
If you have identity column in table B_table, you have to specify columns to insert. Basically you select all except identity column, which will be auto incremented by default.
In case if you dont have existing B_table in B_db
SELECT *
INTO B_db.dbo.B_table
FROM A_db.dbo.A_table
will create table B_table in database B_db with all existing values
I wrote this T-SQL code, which allows to generate an import script (that use compress/decompress function available since sql server 2016) this script export able as xml, the xml is convert to varbinary, and this varbinay is compress.
declare @tablename nvarchar(255) = 'dbo.toto'
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
set nocount on
declare @codetab nvarchar(255) = replace(replace(replace(replace(@tablename,' ','_'), '.','_'),'[', ''),']', '')
declare @nl nvarchar(2)= char(10) -- + char(13)
declare @tab nvarchar(1)= char(9)
declare @export nvarchar(max)
declare @exportcompress nvarchar(max)
declare @sqlgenxml nvarchar(max)
declare @sqlimport nvarchar(max)
declare @sqlstruct nvarchar(max)
declare @sqlinsert nvarchar(max)
declare @nbcarmax int
declare @index int = 0
declare @cc int = 0
declare @maxsize int = 1024
declare @struct table (
name nvarchar(255),
codename nvarchar(255),
col_id int,
is_nullable int,
is_identity_column int,
is_updateable int,
type nvarchar(255)
)
insert into @struct (name, col_id, type, is_nullable, is_identity_column, is_updateable)
select
name,
column_ordinal,
system_type_name,
is_nullable,
is_identity_column,
is_updateable
from
sys.dm_exec_describe_first_result_set('select * from '+@tablename, NULL, 0)
update @struct set codename = replace(replace(replace(replace(name,' ','_'), '.','_'),'[', ''),']', '')
select @nbcarmax=max(len(codename)) from @struct
select @sqlgenxml = coalesce(@sqlgenxml + @tab + ',', @tab + ' ')+ name + replicate(' ', @nbcarmax-len(codename)+3)+' as ' +QUOTENAME(codename)+@nl from @struct order by col_id
select @sqlstruct = coalesce(@sqlstruct + @tab + ',', @tab + ' ')+ quotename(name) + replicate(' ', @nbcarmax-len(codename)+3)+type+case when is_identity_column=1 then ' identity(1,1)' else '' end +@nl from @struct order by col_id
select @sqlinsert = coalesce(@sqlinsert + '--'+ @tab + ',', '--'+@tab + ' ')+ quotename(name) + @nl from @struct order by col_id
set @sqlgenxml = 'set @s =(select'+@nl+@sqlgenxml+'from'+@tab+@tablename+@nl+'for xml path(''row''), root('''+@codetab+'''))'+@nl
exec sp_executesql @sqlgenxml, N'@s nvarchar(max) output', @s=@export output
select @exportcompress = convert(nvarchar(max), COMPRESS(cast(@export as varbinary(max))), 1)
print 'set nocount on'+@nl+@nl
+ '/*'+@nl
+ 'create table '+@tablename+' ('+@nl
+ @sqlstruct
+ ')' + @nl
+ '*/'+@nl + @nl
+@nl
+'declare @import nvarchar(max) ='''''+@nl
+'declare @xml xml'+@nl
+@nl
+'declare @importtab table ('+@nl
+@tab+'id int identity(1,1),'+@nl
+@tab+'row nvarchar(max)'+@nl
+')'+@nl
+@nl
while @index<LEN(@exportcompress)
begin
set @cc+=1
print 'insert into @importtab (row) values ('''+SUBSTRING(@exportcompress, @index, @maxsize)+''') --'+CAST(@cc as varchar(10))
set @index+=@maxsize
end
print @nl
+'select @import += row from @importtab order by id'+@nl
+'select @import = cast(decompress(convert(varbinary(max), @import, 1)) as nvarchar(max))'+@nl
+ @nl
+'set @xml=cast(@import as xml)'+@nl
+ @nl
select
@sqlimport =
coalesce(@sqlimport+@tab+',',@tab+' ')
+ 't.row.value(''./'+codename+'[1]'','
+ replicate(' ', @nbcarmax-len(codename)+3)
+ ''''+type+''''
+ replicate(' ', 20-len(type))
+ ') as '+QUOTENAME(name)
+ @nl
from
@struct
set @sqlimport='select'+@nl+@sqlimport+'from'+@nl+@tab+'@xml.nodes(''/'+@codetab+'/row'') as t(row)'
print '-- truncate table '+@tablename+@nl
if exists(select top 1 1 from @struct where is_identity_column = 1)
print '-- set identity_insert '+@tablename+' on'+@nl
print '-- insert into '+@tablename+' ('+@nl+@sqlinsert+'-- )'+@nl+@sqlimport+@nl
if exists(select top 1 1 from @struct where is_identity_column = 1)
print '-- set identity_insert '+@tablename+' off'+@nl
below you can see an example of this script generate (copy/paste from ssms resultat text)
set nocount on
/*
create table dbo.toto (
[id] int identity(1,1)
,[code] nvarchar(10)
,[value] numeric(18,9)
,[creationdt] datetime
)
*/
declare @import nvarchar(max) =''
declare @xml xml
declare @importtab table (
id int identity(1,1),
row nvarchar(max)
)
insert into @importtab (row) values ('0x1F8B0800000000000400E4DDD16E246B9A5EE...DE52') --1
...
insert into @importtab (row) values ('890639F9...69A8C486F8405') --3140
select @import += row from @importtab order by id
select @import = cast(decompress(convert(varbinary(max), @import, 1)) as nvarchar(max))
set @xml=cast(@import as xml)
-- truncate table dbo.toto
-- set identity_insert dbo.toto on
-- insert into dbo.toto (
-- [id]
-- ,[code]
-- ,[value]
-- ,[creationdt]
-- )
select
t.row.value('./id[1]', 'int' ) as [id]
,t.row.value('./code[1]', 'nvarchar(10)' ) as [code]
,t.row.value('./value[1]', 'numeric(18,9)' ) as [value]
,t.row.value('./creationdt[1]', 'datetime' ) as [creationdt]
from
@xml.nodes('/dbo_toto/row') as t(row)
-- set identity_insert dbo.toto off
hop you will find usefull
Command to get the database backup from linux machine terminal.
sqlcmd -S localhost -U SA -Q "BACKUP DATABASE [demodb] TO DISK = N'/var/opt/mssql/data/demodb.bak' WITH NOFORMAT, NOINIT, NAME = 'demodb-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
We just need to use below query to dump one table data into other table.
Select * into SampleProductTracking_tableDump
from SampleProductTracking;
SampleProductTracking_tableDump is a new table which will be created automatically
when using with above query.
It will copy the records from SampleProductTracking to SampleProductTracking_tableDump
