Get Insert Query from given table name

Sometimes i forgot insert script a particular tables. that time i take script in Generate Script. i thought its boring to take script. that time i searching net i got one script. Its very useful to me.

Insert script given tables

DECLARE @QUERY VARCHAR(MAX) = ‘Dbo.TableName WHERE 1=1′

SET NOCOUNT ON

DECLARE @WithStrINdex AS INT
DECLARE @WhereStrINdex AS INT
DECLARE @INDExtouse AS INT

DECLARE @SchemaAndTAble VARCHAR(270)
DECLARE @Schema_name VARCHAR(30)
DECLARE @Table_name VARCHAR(240)
DECLARE @Condition VARCHAR(MAX)

SELECT @WithStrINdex = 0
SELECT @WithStrINdex = CHARINDEX(‘WITH’, @Query), @WhereStrINdex = CHARINDEX(‘WHERE’, @Query)

IF(@WithStrINdex != 0)
SELECT @INDExtouse = @WithStrINdex
ELSE
SELECT @INDExtouse = @WhereStrINdex

SELECT @SchemaAndTAble = LEFT(@Query, @INDExtouse – 1)
SELECT @SchemaAndTAble = LTRIM(RTRIM(@SchemaAndTAble))

SELECT @Schema_name = LEFT(@SchemaAndTAble, CHARINDEX(‘.’, @SchemaAndTAble ) – 1)
,@Table_name = SUBSTRING(@SchemaAndTAble, CHARINDEX(‘.’, @SchemaAndTAble ) + 1, LEN(@SchemaAndTAble))
,@CONDITION = SUBSTRING(@Query, @WhereStrINdex + 6, LEN(@Query))–27+6
DECLARE @COLUMNS TABLE([Row_number] SMALLINT, Column_Name VARCHAR(MAX))
DECLARE @CONDITIONS AS VARCHAR(MAX)
DECLARE @Total_Rows AS SMALLINT
DECLARE @Counter AS SMALLINT

DECLARE @ComaCol AS VARCHAR(MAX)
SELECT @ComaCol = ”, @Counter = 1, @CONDITIONS = ”

–print @Schema_name
–print @Table_name

INSERT INTO @COLUMNS
SELECT ROW_NUMBER() OVER(ORDER BY ORDINAL_POSITION) [Count] ,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @Schema_name
AND TABLE_NAME = @Table_name
AND COLUMN_NAME NOT IN (‘SYNCDESTINATION’,’PENDINGSYNCDESTINATION’ ,’SKUID’,’SALECREDITEDTO’)

SELECT @Total_Rows = COUNT(*) FROM @COLUMNS
SELECT @Table_name = ‘[‘+@Table_name+’]’
SELECT @Schema_name = ‘[‘+@Schema_name+’]’

WHILE (@Counter< = @Total_Rows )
BEGIN
SELECT @ComaCol = @ComaCol + ‘ [‘+Column_Name+’],’ FROM @COLUMNS
Where [Row_number] = @Counter

SELECT @CONDITIONS = @CONDITIONS+ ‘ + CASE WHEN [‘+Column_Name+’] IS NULL THEN ”NULL” ELSE ”””” +
REPLACE( CONVERT(VARCHAR(MAX),[‘+Column_Name+’]) ,””””,””)
+”””” END +’+”’,”’
FROM @COLUMNS WHERE [Row_number] = @Counter

SET @Counter = @Counter + 1
END

SELECT @CONDITIONS = RIGHT(@CONDITIONS, LEN(@CONDITIONS) -2)

SELECT @CONDITIONS = LEFT(@CONDITIONS, LEN(@CONDITIONS) -4)

SELECT @ComaCol = SUBSTRING (@ComaCol, 0, LEN(@ComaCol))

SELECT @CONDITIONS = ”’INSERT INTO ‘ + @Schema_name + ‘.’ + @Table_name + ‘(‘ + @ComaCol + ‘)’ +’ VALUES( ‘+”” + ‘+’ + @CONDITIONS
SELECT @CONDITIONS = @CONDITIONS + ‘+’ + ”’)”’

SELECT @CONDITIONS = ‘SELECT’ + @CONDITIONS + ‘FROM’ + @Schema_name + ‘.’ + @Table_name + ‘ WITH(NOLOCK) ‘ + ‘ WHERE ‘ + @Condition
PRINT(@CONDITIONS)
EXEC(@CONDITIONS)

 

Advertisements