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