SQL Script to Generate Insert Query Script from Given Table Name and Condition

This post will help developer create insert script from the table with some condition or in other term to create insert script from given select statement.
This will be helpful when we need to move some limited data from one environment to other.


DECLARE @Table_name varchar(240), @Condition Varchar(MAX) SET @Table_name='NotificationBase' SET @Condition = ' NotificationID IN (''11DB04B4-3F10-422F-B6C5-89914E7FD293'', ''4EB8D85E-6817-4220-93DF-54B84B3CFE62'')' DECLARE @COLUMNS table (Row_number SmallINT , Column_Name varchar(Max) ) DECLARE @Schema_name varchar(30), @Conditions as varchar(MAX), @Total_Rows as SmallINT, @Counter as SmallINT, @ComaCol as varchar(max), @Query AS VARCHAR(MAX), @InsertQuery VARCHAR(MAX), @FinalQuery VARCHAR(MAX) SET @Schema_name = 'dbo' SET @ComaCol='' Set @Counter=1 set @Conditions='' SET @Query ='' SET @InsertQuery = '' SET @FinalQuery = '' INSERT INTO @COLUMNS SELECT Row_number()Over (Order by column_id ) [Count] , Name FROM SYS.COLUMNS WHERE Object_Name(Object_ID) = @Table_name AND system_type_id <> 189 AND is_identity = 0 AND is_computed = 0 SELECT @Total_Rows= Count(1) FROM @COLUMNS Set @Table_name= '['+@Table_name+']' Set @Schema_name='['+@Schema_name+']' While (@Counter <= @Total_Rows ) BEGIN SELECT @ComaCol= @ComaCol + '['+Column_Name+'], ' FROM @COLUMNS WHERE [Row_number] = @Counter SELECT @Query = @Query + ' + 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 SET @Query = Right(@Query,LEN(@Query)-2) SET @Query= LEFT(@Query,LEN(@Query)-4) SET @ComaCol= substring (@ComaCol,0, len(@ComaCol) ) SET @InsertQuery= '''INSERT INTO ' + @Schema_name + '.' + @Table_name + '(' + @ComaCol + ')' + ' Values( ' + '''' + '+' + @Query + '+' + ''')''' SET @FinalQuery= 'SELECT ' + @InsertQuery +'FROM ' + @Schema_name + '.' + @Table_name + ' With(NOLOCK) ' + ' WHERE ' + @Condition PRINT(@FinalQuery) EXECUTE(@FinalQuery)

Delete Duplicate Data from SQL Table Using Common Table Expression (CTE)

DECLARE @DuplicateRcordTable TABLE (Col1 varchar(10), Col2 varchar(10)) INSERT INTO @DuplicateRcordTable SELECT 'A', 'B'
UNION ALL SELECT 'A', 'B' --duplicate
UNION ALL SELECT 'C', 'C' --duplicate
UNION ALL SELECT 'C', 'C'
UNION ALL SELECT 'C', 'C' --duplicate
UNION ALL SELECT 'D', 'E'
UNION ALL SELECT 'E', 'F'

SELECT * FROM @DuplicateRcordTable;

WITH CTES(COl1,Col2, DuplicateCount)
 AS
 (
            SELECT COl1,Col2, ROW_NUMBER()
              OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
           FROM @DuplicateRcordTable
 )
DELETE FROM CTES WHERE DuplicateCount > 1

SELECT * FROM @DuplicateRcordTable