SQL Server Portal

Conversion of Select statement result set into Insert statement is a very frequent activity that a DBA/Developer needs to create, mostly when they need to migrate small amount of data from one instance to another or from one environment to another. I recently created one of my customer’s new branch database from other branches database and came across this scenario. Fortunately, we do have a solution since SQL Server 2005 but it was very complicated specially when you need to do it for the tables as they have numerous columns. The reason I am writing this solution is that you can do it in few clicks in SQL Server 2012 and above.
Before proceeding with the solution, I would like to create a sample to demonstrate the solution.

Sample :
Given below is a select statement in which I modified the result set to demonstrate.

USE [AdventureWorks2012]
GO
SELECT [DepartmentID]
,[Name] + ' Department' As [Name]
,[GroupName]
,Getdate() As [ModifiedDate]
FROM [HumanResources].[Department]
GO

Given below are the two solutions, one of them is traditional solution and another one you can use it in SQL Server 2012 and above.

Solution 1 : Using String concatenation (Traditional Method)
In this solution, you need to concatenate the result set of the Select statement in order to convert into Insert statement (with some modifications in the data). You need to make sure that single quotes(‘) are in proper locations. In addition, if the data in the result set does not belong to string data type you must convert into string data type to concatenate. In case, the table is having identity column, you must pass the column name in the INSERT STATEMENT as well with SET IDENTITY_INSERT. The reason why I DO NOT recommend this solution is because if you have more number of columns in the table, it takes more time for the development and debug as well.

USE [AdventureWorks2012]
GO
SELECT
'INSERT INTO tbl_sample (
[DepartmentID],[Name],[GroupName],[ModifiedDate])
VALUES(' + CONVERT(VARCHAR(50),[DepartmentID])
+ ',''' + [Name] + ' Department'' ,'
+ ''''+ [GroupName] + ''','
+ ''''+ CONVERT(VARCHAR(50),GETDATE(),120) + ''')'
FROM [HumanResources].[Department]
GO

Solution 2 : Using Generate Script (New Method)
This method is applicable to SQL Server 2012 and above and you will find it quite simple. Let me explain this method using two simple steps.

Step 1 :
First of all, you need to develop a select statement like I did it in the sample based on your requirements and INSERT INTO A TABLE as shown below.

USE [AdventureWorks2012]
GO
SELECT [DepartmentID]
,[Name] + ' Department' As [Name]
,[GroupName]
,Getdate() As [ModifiedDate]
INTO [tbl_Department_Sample]  -- Result set inserted in a table
FROM [HumanResources].[Department]
GO

Step 2 :
Your select statement result set has been inserted into the table([tbl_Department_Sample]). Now, you just need to generate the script (data only) of the table ([tbl_Department_Sample]) using Generate Script feature in SQL Server 2012 and above.

Let me know if you come across these scenarios and their solutions.

Read Full Post »