Stored Procedure to Generate Insert Statements with Columns and values for a Table in SQL Server

Requirement-

I wanted to generate Insert statements for all the rows in the table with the column name and their values.

For example,

If the Table name is Emp and with following structure and data

EmpId	EmpName	EmpLastName	Department
1	Amit	Gupta	Technical
2	Sumit	Verma	Functional
3	Ritesh	Sharma	Financial
4	Hitesh	Dubey	Sales

On calling the Stored procedure

GetInsertStatement 'Emp'

It should generate the following

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘1’, ‘Amit’, ’Gupta’, ’Technical’)

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘2’, ‘Sumit, ’Verma’, ’Functional’)

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘3’, ‘Ritesh’ ,Sharma’, ’Financial’)

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘4’, ‘Hitesh’, ’Dubey’, ’Sales’)

Following is the stored procedure to generate the desired result.

NOTE- Currently it includes identity column also in the Insert Statement since that was the requirement, but you can see the other version also without Identity column in Insert statement.



GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetInsertStatement]

(@tableName varchar(100)) as

--Declare a cursor to retrieve column specific information
--for the specified table

DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns
WHERE table_name = @tableName

OPEN cursCol

DECLARE @string nvarchar(3000) --for storing the first half
--of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data
--(VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned
--for respective columns

SET @string='INSERT INTO '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END

WHILE @@FETCH_STATUS=0

BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
SET @stringData=@stringData+'''''''''+
isnull('+@colName+','''')+'''''',''+'
END

ELSE
if @dataType in ('text','ntext') --if the datatype
--is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END

ELSE

IF @dataType = 'money' --because money doesn't get converted
--from varchar implicitly
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(200)),''0.0000'')+'''''',''+'
END

ELSE
IF @dataType='datetime'
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END

ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast(convert(varbinary,'+@colName+')
as varchar(6)),''0'')+'''''',''+'
END

ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType
END

DECLARE @Query nvarchar(4000) -- provide for the whole query,
-- you may increase the size

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ')
VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''
FROM '+@tableName
exec sp_executesql @query --load and run the built query

CLOSE cursCol
DEALLOCATE cursCol