Generate insert data script

by Amol 21. March 2011 08:12

Problem: 

In many situations we need to populate table with required data and for that we need INSERT INTO statements in SQL Server. Writing such script would be time consuming if you have thousands of records. Mostly we need this script to populate Meta data tables from development box to production server. This can be achieve in couple of ways, like import export functionality, BCP out and BCP in functionality. These options are also time consuming and involved little bit efforts to do it. Also for these options you need some extra rights to perform these operations. 

Solution:

Isn't it be nice to call just one procedure to generate all insert data script and just run that script wherever you need to run? So here is the procedure to generate insert data script. Click to download the procedure prcCreateInsertScript and create this procedure into your database.

Create_Insert_Data_Script.sql (3.34 kb)

How it works?

Let’s take an example, you have Meta data table Country having the list of all the countries. 

 

CREATE TABLE [dbo].[Country] 
(
      Id INT NOT NULL,
      Name VARCHAR(255) NOT NULL
)
GO

 

Add some countries in country table. 

 

INSERT INTO [dbo].[Country]
SELECT 1, 'India'
UNION ALL SELECT 2, 'United States of America'
UNION ALL SELECT 3, 'Japan'
UNION ALL SELECT 4, 'Austrelia'
UNION ALL SELECT 5, 'England'
UNION ALL SELECT 6, 'Keneya'
UNION ALL SELECT 7, 'China'
UNION ALL SELECT 8, 'South Africa'
UNION ALL SELECT 9, 'West Indies'

GO

 

Run the above script to create Country table development box. There are many countries; I have just listed some of them to show as an example.  

Now execute the procedure [dbo].[prcCreateInsertScript] to generate insert script for countries listed in Country table.

 

EXEC [dbo].[prcCreateInsertScript] 
@ObjectName = 'Country', 
@Schema = 'dbo'

GO

 

Execute the above procedure to generate insert data script. It will show the resultset shown as below. Just copy these statements and run it wherever you needed.

 

 

[dbo].[prcCreateInsertScript]  procedure accept two parameters, one is @ObjectName, this is the object name (Table Name) for which you need to generate insert data script. And second one is @Schema, this is the schema (owner) of the table. By default it takes "dbo" as schema. The procedure will run without @Schema parameter if table owner is dbo otherwise you can specify the owner of the table. 

Another case here is to consider IDENTITY column while generating insert data script. Let's revise the above example by considering Id as identity column. 

 

CREATE TABLE [dbo].[Country] 
(
     Id INT IDENTITY(1,1) NOT NULL,
     Name VARCHAR(255) NOT NULL
)

GO

 

Add some countries in country table. 

 

INSERT INTO [dbo].[Country]

SELECT 'India'
UNION ALL SELECT 'United States of America'
UNION ALL SELECT 'Japan'
UNION ALL SELECT 'Austrelia'
UNION ALL SELECT 'England'
UNION ALL SELECT 'Keneya'
UNION ALL SELECT 'China'
UNION ALL SELECT 'South Africa'
UNION ALL SELECT 'West Indies'

GO

 

Execute the procedure [dbo].[prcCreateInsertScript] to generate insert script for countries listed in Country table having Id as IDENTITY column.

 

EXEC [dbo].[prcCreateInsertScript]  
       @ObjectName = 'Country',   
       @Schema = 'dbo'

 

 

 

You can observe the generated script, it adds "SET IDENTITY_INSERT [dbo].[Country] ON" as very first line to allow insert value into IDENTITY column. Also it sets OFF at the last line as "SET IDENTITY_INSERT [dbo].[Country] OFF" 

Limitations:

All the table datatypes has not been considered while generating insert data script. It may fail for new datatypes. 

Note: Please consider this script as baseline script and use it on your own risk.

Create_Insert_Data_Script.sql (3.34 kb)

Tags: , ,

SQL Server Scripts

Category

Recent Posts

Tag cloud