Create Insert SQL Statement from the Records in an Existing Table
This tip describes how to generate insert SQL statement from the records in an existing table in SQL server database
Introduction
Sometimes, you want to export some SQL scripts of some records from some existing tables in your SQL Server database so that you can insert those data into a similar table of another database. There are lots of ways to do that. Here, I will share you a stored procedure to do that. Hope it helps.
Using the Code
First, please create the below stored procedure [dbo].[sp_CreateInsertScript] in your SQL Server database.
[dbo].[sp_CreateInsertScript] content:
--=============================================
-- Author: Mark Kang
-- Company: www.ginkia.com
-- Create date: 2016-03-06
-- Description: Generat the insert sql script according to the data in the specified table.
-- It does not support the columns with timestamp,text,image.
-- Demo : exec sp_CreateInsertScript '[dbo].[Country]','[continent_name]=''North America'''
-- Change History:
-- 1.2016-03-06 Created and published
-- 2.2016-03-08 Based on Mike's suggestions, I optimized the codes
-- 3.2019-03-09 1)Add code lines to avoid error when @con is empty string
-- 2)Based on Lukas Macedo's suggetstions, add surrounding brackets for column name
-- 3)Simplify WHEN...CASE
-- =============================================
CREATE PROC [dbo].[sp_CreateInsertScript] (
@tablename NVARCHAR(256) -- table name
,@con NVARCHAR(400) -- condition to filter data
,@ignoreIdentityCol bit=0 --indicate if ignore columne with identity
,@isDebug bit=0 --indicate if this is used to debug. when 1,output the internal sql string
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sqlstr NVARCHAR(MAX);
DECLARE @valueStr1 NVARCHAR(MAX);
DECLARE @colsStr NVARCHAR(MAX);
SELECT @sqlstr='SELECT ''INSERT '+@tablename;
SELECT @valueStr1='';
SELECT @colsStr='(';
SELECT @valueStr1='VALUES (''+';
IF RTRIM(LTRIM(@con))=''
SET @con='1=1';
SELECT @valueStr1=@valueStr1+col+'+'',''+'
,@colsStr=@colsStr+'['+name +'],'
FROM (
SELECT
CASE
/* xtype=173 'binary'
xtype=165 'varbinary'*/
WHEN sc.xtype in (173,165) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR('+CONVERT(NVARCHAR(4),sc.[length]*2+2)+'),['+sc.name +'])'+' END'
/*xtype=104 'bit'*/
WHEN sc.xtype =104 THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(1),['+sc.name +'])'+' END'
/*xtype=61 'datetime'
xtype=58 'smalldatetime'*/
WHEN sc.xtype in(58,61) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'''N''''''+'+'CONVERT(NVARCHAR(23),'+sc.name +',121)'+ '+'''''''''+' END'
/*xtype=175 'char'
xtype=36 'uniqueidentifier'
xtype=167 'varchar'
xtype=231 'nvarchar'
xtype=239 'nchar'*/
WHEN sc.xtype in (36,175,167,231,239) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'''N''''''+'+'REPLACE(['+sc.name+'],'''''''','''''''''''')' + '+'''''''''+' END'
/*xtype=106 'decimal'
xtype=108 'numeric'*/
WHEN sc.xtype in(106,108) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR('+CONVERT(NVARCHAR(4),sc.xprec+2)+'),['+sc.name +'])'+' END'
/*xtype=59 'real'
xtype=62 'float'*/
WHEN sc.xtype in (59,62) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(23),'+sc.name +',2)'+' END'
/*xtype=48 'tinyint'
xtype=52 'smallint'
xtype=56 'int'
xtype=127 'bigint'
xtype=122 'smallmoney'
xtype=60 'money'*/
WHEN sc.xtype in (48,52,56,127,122,60) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(23),['+sc.name +'])'+' END'
ELSE '''NULL'''
END AS col
,sc.colid
,sc.name
FROM syscolumns AS sc
WHERE sc.id = object_id(@tablename)
AND sc.xtype <>189 --xtype=189 'timestamp'
AND sc.xtype <>34 --xtype=34 'image'
AND sc.xtype <>35 --xtype= 35 'text'
AND (columnproperty(sc.id, sc.name, 'IsIdentity') = 0 OR @ignoreIdentityCol=0)
) AS t
ORDER BY colid;
SET @colsStr=left(@colsStr,len(@colsStr)-1)+') ';
SET @valueStr1=left(@valueStr1,len(@valueStr1)-3)+')''';
SELECT @sqlstr=@sqlstr+@colsStr+@valueStr1+' AS sql FROM '+@tablename + ' WHERE 1=1 AND ' + isnull(@con,'1=1');
IF @isDebug=1
BEGIN
PRINT '1.columns string: '+ @colsStr;
PRINT '2.values string: '+ @valueStr1
PRINT '3.'+@sqlstr;
END
EXEC( @sqlstr);
SET NOCOUNT OFF
END
GO
Example
Next, I do an example to help you understand how to use it. We assume you have a table Country as below in your database and you want get insert SQL statements from some records in this table. These records should be items that their continent_name is North America.
Example table script:
CREATE TABLE [dbo].[Country](
[geoname_id] [varchar](50) NULL,
[locale_code] [varchar](50) NULL,
[continent_code] [varchar](50) NULL,
[continent_name] [varchar](50) NULL,
[country_iso_code] [varchar](50) NULL,
[country_name] [varchar](50) NULL
) ON [PRIMARY]
According to your requirements, you can call the stored procedure you just created to generate SQL scripts like this:
exec sp_CreateInsertScript '[dbo].[Country]','[continent_name]=''North America'''
After that, you will get some insert SQL statement records:
By now, you can copy the records or save the output to a query or txt file by commands in right-click menu so that you use it in another database.
Thanks!