Directly Create SQL Insert Statements of Data in a Table

This Statements are Used to directly create SQL insert statements of data in a table.
we just need to pass the name of the SQL Table as i passed
Replace 'MyTestTable' with your table name and execute these statement.

Query for SQL Server 2005 and 2008:

DECLARE @TempString VARCHAR(7999),@Tablename varchar(200),@ColumnName varchar(800)
SET @Tablename=''
SET @TempString = ''
SET @ColumnName=''

SELECT @TempString = @TempString + '+'''''',''''''+ISNULL('+SYS.Columns.Name +',''NULL'')'
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (239,175,167,231,99,35,34)

IF(@TempString <>'')
SET @TempString='''''''' + @TempString


SELECT @TempString = @TempString + '+'''''',''''''+ISNULL(CONVERT(varchar(30),'+SYS.Columns.Name+'),''NULL'')'
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (61)

SET @TempString=@TempString+'+'''''''''

SELECT @ColumnName = @ColumnName + ','+SYS.Columns.Name
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (239,175,167,231,99,35,34)

SELECT @ColumnName = @ColumnName + ','+SYS.Columns.Name
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (61)

SELECT @TempString = @TempString + '+'',''+ISNULL(CONVERT(varchar(10),'+SYS.Columns.Name+'),''NULL'')'
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id NOT IN (239,175,167,231,99,35,34,61)

SELECT @ColumnName = @ColumnName + ','+SYS.Columns.Name
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id NOT IN (239,175,167,231,99,35,34,61)

SET @TempString= SUBSTRING(@TempString,9,LEN(@TempString)-8)
SET @ColumnName= SUBSTRING(@ColumnName,2,LEN(@ColumnName)-1)
--SELECT @TempString,@ColumnName

DECLARE @SubQuery varchar(5000),@SQLQuery varchar(max)
SET @SubQuery='INSERT INTO '+@Tablename+'('+@ColumnName+') VALUES ('+@TempString+'+'''+')'
SET @SQLQuery='SELECT '''+@SubQuery+''' FROM '+@Tablename

EXEC (@SQLQuery)

Query for SQL Server 2000:

DECLARE @TempString VARCHAR(7999),@Tablename varchar(200),@ColumnName varchar(500)
SET @Tablename='tblProdline'
SET @TempString = ''
SET @ColumnName=''

SELECT @TempString = @TempString + '+'''''',''''''+ISNULL('+SYSColumns.Name+','''')'
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype IN (239,175,167,231,99,35,34)

IF(@TempString <>'')
SET @TempString='''''''' + @TempString

SELECT @TempString = @TempString + '+'''''',''''''+CONVERT(varchar(30),ISNULL('+SYSColumns.Name+',0))'
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype IN (61)

SET @TempString=@TempString+'+'''''''''

SELECT @ColumnName = @ColumnName + ','+SYSColumns.Name
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype IN (239,175,167,231,99,35,34)

SELECT @ColumnName = @ColumnName + ','+SYSColumns.Name
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype IN (61)

SELECT @TempString = @TempString + '+'',''+CONVERT(varchar(10),ISNULL('+SYSColumns.Name+',0))'
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype NOT IN (239,175,167,231,99,35,34,61)

SELECT @ColumnName = @ColumnName + ','+SYSColumns.Name
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype NOT IN (239,175,167,231,99,35,34,61)

SET @TempString= SUBSTRING(@TempString,9,LEN(@TempString)-8)
SET @ColumnName= SUBSTRING(@ColumnName,2,LEN(@ColumnName)-1)
--SELECT @TempString,@ColumnName

DECLARE @SubQuery varchar(5000),@SQLQuery varchar(8000)
SET @SubQuery='INSERT INTO '+@Tablename+'('+@ColumnName+') VALUES ('+@TempString+'+'''+')'
SET @SQLQuery='SELECT '''+@SubQuery+''' FROM '+@Tablename

EXEC (@SQLQuery)