Directly Create SQL Insert Statements of Data in a Table

Lakhangarg
Posted by Lakhangarg under Sql Server category on | Views : 1699
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.

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

SELECT @TempString = @TempString + '+'''''',''''''+ISNULL('+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 @TempString = @TempString + '+'''''',''''''+CONVERT(varchar(30),ISNULL('+SYS.Columns.Name+',0))'
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 + '+'',''+CONVERT(varchar(10),ISNULL('+SYS.Columns.Name+',0))'
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)

Comments or Responses

Login to post response