Tip to Generate Insert Scripts for Master Tables

Introduction

This tip will help you to generate Insert data scripts for Master table Data scripts.

Background

In my Application development tenure; I am also involved in the development and deployment of code & DB scripts on LIVE application server. So it is difficult to remember what values are changed or added to master tables. I have to check individual table records and then make changes to LIVE SQL DB Server. Here, I tried to resolve this by generate scripts for these tables. (Of course, first I have to remove/ truncate table values from LIVE Server.)

Using the Code

This is a simple SQL Procedure that you have to add in your database.

CREATE PROC [dbo].[usp_Generate_InsertScript]      
(      
       @TableName VARCHAR(255) ,      
       @bitIncludeIdentityField BIT = 0      
)      
AS      
BEGIN      
       declare @FieldNames varchar(max),@FieldValues varchar(max),@SqlStatement varchar(max)      
       seLECT @FieldNames='',@FieldValues='+'      
Select @FieldNames = @FieldNames + '['+name+'],',    
    @FieldValues = @FieldValues+'isnull('+CASE WHEN xtype IN (58,61,167,175,231) _
    THEN 'Char(39)+' ELSE 'SPACE(0)+' END    
    +'REPLACE(convert(varchar(max),['+nAME+']),CHAR(39),Char(39)+Char(39))'+    
CASE WHEN xtype IN (58,61,167,175,231) THEN'+Char(39)'ELSE '+_
   Space(0)' END +','+cHAR(39)+'null'+cHAR(39)+')+'+cHAR(39)+','+cHAR(39)+'+'     
FROM Sys.SysColumns     
Where Id=Object_Id(@TableName) AND colstat IN(0,@bitIncludeIdentityField)      
       seLECT @FieldNames= left(@FieldNames,lEN(@FieldNames)-1),_
              @FieldValues = left(@FieldValues,lEN(@FieldValues)-5)      
       set @SqlStatement = 'select '+char(39)+'INSERT INTO '+@TableName+' _
        ('+@FieldNames+') values ('+char(39)+@FieldValues+'+'+Char(39)+')'+_
         Char(39)+' from '+@TableName      
       EXEC(@SqlStatement)      
END      
GO
-- how to Use/Execute this SP
EXEC usp_Generate_InsertScript 'TableName'

Points of Interest

SQL Query simplify