How to export all data from table to an insertable sql format?

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:

- **Copy** every rows from `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 have not seen any option in Microsoft SQL Server Management Studio 2012 to-date that will do that.

I am sure you can write something in T-SQL given the time.

Check out [TOAD from QUEST][1] - now owned by DELL.

[To see links please register here]

Select your rows.
Rt -click -> Export Dataset.
Choose Insert Statement format
Be sure to check “selected rows only”

Nice thing about toad, it works with both SQL server and Oracle. If you have to work with both, it is a good investment.


[1]:

[To see links please register here]

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

Another way to dump data as file from table by DumpDataFromTable sproc

EXEC dbo.DumpDataFromTable
@SchemaName = 'dbo'
,@TableName = 'YourTableName'
,@PathOut = N'c:\tmp\scripts\' -- folder must exist !!!'

Note: *SQL must have permission to create files, if is not set-up then exec follow line once*

EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;

By this script you can call the sproc: DumpDataFromTable.sql and dump more tables in one go, instead of doing manually one by one from Management Studio

By default the format of generated scrip will be like

INSERT INTO <TableName> SELECT <Values>

Or you can change the generated format into

SELECT ... FROM

by setting variable @BuildMethod = 2

full sproc code:

<blink>

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DumpDataFromTable]') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.[DumpDataFromTable]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Oleg Ciobanu
-- Create date: 20171214
-- Version 1.02
-- Description:
-- dump data in 2 formats
-- @BuildMethod = 1 INSERT INTO format
-- @BuildMethod = 2 SELECT * FROM format
--
-- SQL must have permission to create files, if is not set-up then exec follow line once
-- EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;
--
-- =============================================
CREATE PROCEDURE [dbo].[DumpDataFromTable]
(
@SchemaName nvarchar(128) --= 'dbo'
,@TableName nvarchar(128) --= 'testTable'
,@WhereClause nvarchar (1000) = '' -- must start with AND
,@BuildMethod int = 1 -- taking values 1 for INSERT INTO forrmat or 2 for SELECT from value Table
,@PathOut nvarchar(250) = N'c:\tmp\scripts\' -- folder must exist !!!'
,@AsFileNAme nvarchar(250) = NULL -- if is passed then will use this value as FileName
,@DebugMode int = 0
)
AS
BEGIN
SET NOCOUNT ON;

-- run follow next line if you get permission deny for sp_OACreate,sp_OAMethod
-- EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;

DECLARE @Sql nvarchar (max)
DECLARE @SqlInsert nvarchar (max) = ''
DECLARE @Columns nvarchar(max)
DECLARE @ColumnsCast nvarchar(max)

-- cleanUp/prepraring data
SET @SchemaName = REPLACE(REPLACE(@SchemaName,'[',''),']','')
SET @TableName = REPLACE(REPLACE(@TableName,'[',''),']','')
SET @AsFileNAme = NULLIF(@AsFileNAme,'')
SET @AsFileNAme = REPLACE(@AsFileNAme,'.','_')
SET @AsFileNAme = COALESCE(@PathOut + @AsFileNAme + '.sql', @PathOut + @SchemaName + ISNULL('_' + @TableName,N'') + '.sql')


--debug
IF @DebugMode = 1
PRINT @AsFileNAme

-- Create temp SP what will be responsable for generating script files
DECLARE @PRC_WritereadFile VARCHAR(max) =
'IF EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND name = ''PRC_WritereadFile'')
BEGIN
DROP Procedure PRC_WritereadFile
END;'
EXEC (@PRC_WritereadFile)
-- '
SET @PRC_WritereadFile =
'CREATE Procedure PRC_WritereadFile (
@FileMode INT -- Recreate = 0 or Append Mode 1
,@Path NVARCHAR(1000)
,@AsFileNAme NVARCHAR(500)
,@FileBody NVARCHAR(MAX)
)
AS
DECLARE @OLEResult INT
DECLARE @FS INT
DECLARE @FileID INT
DECLARE @hr INT
DECLARE @FullFileName NVARCHAR(1500) = @Path + @AsFileNAme

-- Create Object
EXECUTE @OLEResult = sp_OACreate ''Scripting.FileSystemObject'', @FS OUTPUT
IF @OLEResult <> 0 BEGIN
PRINT ''Scripting.FileSystemObject''
GOTO Error_Handler
END

IF @FileMode = 0 BEGIN -- Create
EXECUTE @OLEResult = sp_OAMethod @FS,''CreateTextFile'',@FileID OUTPUT, @FullFileName
IF @OLEResult <> 0 BEGIN
PRINT ''CreateTextFile''
GOTO Error_Handler
END
END ELSE BEGIN -- Append
EXECUTE @OLEResult = sp_OAMethod @FS,''OpenTextFile'',@FileID OUTPUT, @FullFileName, 8, 0 -- 8- forappending
IF @OLEResult <> 0 BEGIN
PRINT ''OpenTextFile''
GOTO Error_Handler
END
END

EXECUTE @OLEResult = sp_OAMethod @FileID, ''WriteLine'', NULL, @FileBody
IF @OLEResult <> 0 BEGIN
PRINT ''WriteLine''
GOTO Error_Handler
END

EXECUTE @OLEResult = sp_OAMethod @FileID,''Close''
IF @OLEResult <> 0 BEGIN
PRINT ''Close''
GOTO Error_Handler
END

EXECUTE sp_OADestroy @FS
EXECUTE sp_OADestroy @FileID

GOTO Done

Error_Handler:
DECLARE @source varchar(30), @desc varchar (200)
EXEC @hr = sp_OAGetErrorInfo null, @source OUT, @desc OUT
PRINT ''*** ERROR ***''
SELECT OLEResult = @OLEResult, hr = CONVERT (binary(4), @hr), source = @source, description = @desc

Done:
';
-- '
EXEC (@PRC_WritereadFile)
EXEC PRC_WritereadFile 0 /*Create*/, '', @AsFileNAme, ''


;WITH steColumns AS (
SELECT
1 as rn,
c.ORDINAL_POSITION
,c.COLUMN_NAME as ColumnName
,c.DATA_TYPE as ColumnType
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE 1 = 1
AND c.TABLE_SCHEMA = @SchemaName
AND c.TABLE_NAME = @TableName
)

--SELECT *

SELECT
@ColumnsCast = ( SELECT
CASE WHEN ColumnType IN ('date','time','datetime2','datetimeoffset','smalldatetime','datetime','timestamp')
THEN
'convert(nvarchar(1001), s.[' + ColumnName + ']' + ' , 121) AS [' + ColumnName + '],'
--,convert(nvarchar, [DateTimeScriptApplied], 121) as [DateTimeScriptApplied]
ELSE
'CAST(s.[' + ColumnName + ']' + ' AS NVARCHAR(1001)) AS [' + ColumnName + '],'
END
as 'data()'
FROM
steColumns t2
WHERE 1 =1
AND t1.rn = t2.rn
FOR xml PATH('')
)
,@Columns = ( SELECT
'[' + ColumnName + '],' as 'data()'
FROM
steColumns t2
WHERE 1 =1
AND t1.rn = t2.rn
FOR xml PATH('')
)

FROM steColumns t1

-- remove last char
IF lEN(@Columns) > 0 BEGIN
SET @Columns = SUBSTRING(@Columns, 1, LEN(@Columns)-1);
SET @ColumnsCast = SUBSTRING(@ColumnsCast, 1, LEN(@ColumnsCast)-1);
END

-- debug
IF @DebugMode = 1 BEGIN
print @ColumnsCast
print @Columns
select @ColumnsCast , @Columns
END

-- build unpivoted Data
SET @SQL = '
SELECT
u.rn
, c.ORDINAL_POSITION as ColumnPosition
, c.DATA_TYPE as ColumnType
, u.ColumnName
, u.ColumnValue
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn,
'
+ CHAR(13) + @ColumnsCast
+ CHAR(13) + 'FROM [' + @SchemaName + '].[' + @TableName + '] s'
+ CHAR(13) + 'WHERE 1 = 1'
+ CHAR(13) + COALESCE(@WhereClause,'')
+ CHAR(13) + ') tt
UNPIVOT
(
ColumnValue
FOR ColumnName in (
' + CHAR(13) + @Columns
+ CHAR(13)
+ '
)
) u

LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON c.COLUMN_NAME = u.ColumnName
AND c.TABLE_SCHEMA = '''+ @SchemaName + '''
AND c.TABLE_NAME = ''' + @TableName +'''
ORDER BY u.rn
, c.ORDINAL_POSITION
'

-- debug
IF @DebugMode = 1 BEGIN
print @Sql
exec (@Sql)
END

-- prepare data for cursor

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
CREATE TABLE #tmp
(
rn bigint
,ColumnPosition int
,ColumnType varchar (128)
,ColumnName varchar (128)
,ColumnValue nvarchar (2000) -- I hope this size will be enough for storring values
)
SET @Sql = 'INSERT INTO #tmp ' + CHAR(13) + @Sql

-- debug
IF @DebugMode = 1 BEGIN
print @Sql
END

EXEC (@Sql)

-- Insert dummy rec, otherwise will not proceed the last rec :)
INSERT INTO #tmp (rn)
SELECT MAX(rn) + 1
FROM #tmp

IF @DebugMode = 1 BEGIN
SELECT * FROM #tmp
END

DECLARE @rn bigint
,@ColumnPosition int
,@ColumnType varchar (128)
,@ColumnName varchar (128)
,@ColumnValue nvarchar (2000)
,@i int = -1 -- counter/flag
,@ColumnsInsert varchar(max) = NULL
,@ValuesInsert nvarchar(max) = NULL

DECLARE cur CURSOR FOR
SELECT rn, ColumnPosition, ColumnType, ColumnName, ColumnValue
FROM #tmp
ORDER BY rn, ColumnPosition -- note order is really important !!!
OPEN cur

FETCH NEXT FROM cur
INTO @rn, @ColumnPosition, @ColumnType, @ColumnName, @ColumnValue

IF @BuildMethod = 1
BEGIN
SET @SqlInsert = 'SET NOCOUNT ON;' + CHAR(13);
EXEC PRC_WritereadFile 1 /*Add*/, '', @AsFileName, @SqlInsert
SET @SqlInsert = ''
END
ELSE BEGIN
SET @SqlInsert = 'SET NOCOUNT ON;' + CHAR(13);
SET @SqlInsert = @SqlInsert
+ 'SELECT *'
+ CHAR(13) + 'FROM ('
+ CHAR(13) + 'VALUES'
EXEC PRC_WritereadFile 1 /*Add*/, '', @AsFileName, @SqlInsert
SET @SqlInsert = NULL
END

SET @i = @rn

WHILE @@FETCH_STATUS = 0
BEGIN

IF (@i <> @rn) -- is a new row
BEGIN
IF @BuildMethod = 1
-- build as INSERT INTO -- as Default
BEGIN
SET @SqlInsert = 'INSERT INTO [' + @SchemaName + '].[' + @TableName + '] ('
+ CHAR(13) + @ColumnsInsert + ')'
+ CHAR(13) + 'VALUES ('
+ @ValuesInsert
+ CHAR(13) + ');'
END
ELSE
BEGIN
-- build as Table select
IF (@i <> @rn) -- is a new row
BEGIN
SET @SqlInsert = COALESCE(@SqlInsert + ',','') + '(' + @ValuesInsert+ ')'
EXEC PRC_WritereadFile 1 /*Add*/, '', @AsFileNAme, @SqlInsert
SET @SqlInsert = '' -- in method 2 we should clear script
END
END
-- debug
IF @DebugMode = 1
print @SqlInsert
EXEC PRC_WritereadFile 1 /*Add*/, '', @AsFileNAme, @SqlInsert

-- we have new row
-- initialise variables
SET @i = @rn
SET @ColumnsInsert = NULL
SET @ValuesInsert = NULL
END

-- build insert values
IF (@i = @rn) -- is same row
BEGIN
SET @ColumnsInsert = COALESCE(@ColumnsInsert + ',','') + '[' + @ColumnName + ']'
SET @ValuesInsert = CASE
-- date
--WHEN
-- @ColumnType IN ('date','time','datetime2','datetimeoffset','smalldatetime','datetime','timestamp')
--THEN
-- COALESCE(@ValuesInsert + ',','') + '''''' + ISNULL(RTRIM(@ColumnValue),'NULL') + ''''''
-- numeric
WHEN
@ColumnType IN ('bit','tinyint','smallint','int','bigint'
,'money','real','','float','decimal','numeric','smallmoney')
THEN
COALESCE(@ValuesInsert + ',','') + '' + ISNULL(RTRIM(@ColumnValue),'NULL') + ''
-- other types treat as string
ELSE
COALESCE(@ValuesInsert + ',','') + '''' + ISNULL(RTRIM(
-- escape single quote
REPLACE(@ColumnValue, '''', '''''')
),'NULL') + ''''
END
END


FETCH NEXT FROM cur
INTO @rn, @ColumnPosition, @ColumnType, @ColumnName, @ColumnValue

-- debug
IF @DebugMode = 1
BEGIN
print CAST(@rn AS VARCHAR) + '-' + CAST(@ColumnPosition AS VARCHAR)
END
END
CLOSE cur
DEALLOCATE cur

IF @BuildMethod = 1
BEGIN
PRINT 'ignore'
END
ELSE BEGIN
SET @SqlInsert = CHAR(13) + ') AS vtable '
+ CHAR(13) + ' (' + @Columns
+ CHAR(13) + ')'
EXEC PRC_WritereadFile 1 /*Add*/, '', @AsFileNAme, @SqlInsert
SET @SqlInsert = NULL
END
PRINT 'Done: ' + @AsFileNAme
END

</blink>

Or can be downloaded latest version from

[To see links please register here]

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`

![enter image description here][1]


[1]:

Quick and Easy way:

1. Right click database
2. Point to `tasks` `In SSMS 2017 you need to ignore step 2 - the generate scripts options is at the top level of the context menu` Thanks to [Daniel][2] for the comment to update.
3. Select `generate scripts`
4. Click next
5. Choose tables
6. Click next
7. Click advanced
8. Scroll to `Types of data to script` - Called `types of data to script` in SMSS 2014 Thanks to [Ellesedil][1] for commenting
9. Select `data only`
10. Click on 'Ok' to close the advanced script options window
11. Click next and generate your script

I usually in cases like this generate to a new query editor window and then just do any modifications where needed.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

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"

[Backup and restore SQL Server databases on Linux][1]

[1]:

[To see links please register here]

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