Hello Friends,
As a part of maintenance tasks, some times I had to database fixes like inserting new records into the tables.Since we are following a release process, we have to insert the same data in all dev, qa and live environments. But most of the times I forgot to keep these insert statements. In such cases I always endup with using DTS even for a samll lookup table which contains small amout of data, which is a time consuming task especially at the time of releases.
To simplify this data transfer task, I have written the followng sproc which will generate the executable insert statements for the given table.
I hope you find this article is useful in your job J
CREATE PROCEDURE GenerateInsertStatement(@TableName AS VARCHAR(100))
AS
BEGIN
DECLARE @ColumnName AS VARCHAR(50)
DECLARE @ColumnType AS VARCHAR(50)
DECLARE @ColumnLength AS VARCHAR(50)
DECLARE @SqlQry AS VARCHAR(8000)
DECLARE @Values AS VARCHAR(8000)
DECLARE CInsert CURSOR
LOCAL
FORWARD_ONLY
STATIC
FOR
SELECT a.[Name] As ColumnName, b.[Name] As ColumnType, a.Length FROM SysColumns a
INNER JOIN SysTypes b
ON a.Xtype = b.XType
WHERE Id IN(SELECT Id FROM Sysobjects WHERE [Name] = @TableName) AND b.[Name] <> ’sysname’
SET NOCOUNT ON
SET @SqlQry =”
SET @Values =”
SET @SqlQry = @SqlQry +‘SELECT ‘ +CHAR(13)+””+ ‘INSERT INTO ‘ + @TableName
SET @SqlQry = @SqlQry + ‘(‘ +CHAR(13)
OPEN CInsert
FETCH NEXT FROM CInsert INTO @ColumnName, @ColumnType, @ColumnLength
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColumnType = ‘nchar’ OR @ColumnType = ‘nvarchar’
BEGIN
SET @SqlQry = @SqlQry + ‘[' + @ColumnName + ']‘ + ‘,’ + CHAR(13)
SET @values = @values + ”” + ‘N’ + ””+ ‘+’ + ””””” + ‘+’ + ‘REPLACE(CAST(['+@ColumnName+'] As NVARCHAR(4000)),””””,””””””)’ + ‘+’ + ””””” + ‘+’ + ”’,”’ +‘+’+CHAR(13)
END
ELSE
BEGIN
SET @SqlQry = @SqlQry + ‘[' + @ColumnName + ']‘ + ‘,’ + CHAR(13)
SET @values = @values + ””””” + ‘ + ‘ + ‘REPLACE(CAST(['+@ColumnName+'] As VARCHAR(8000)),””””,””””””)’ + ‘+’ + ””””” + ‘+’ + ”’,”’ +‘+’+CHAR(13)
END
FETCH NEXT FROM CInsert INTO @ColumnName, @ColumnType, @ColumnLength
END
SET @SqlQry = SUBSTRING(@SqlQry,1,LEN(@SqlQry)-2)+‘)’+CHAR(13)+ ‘VALUES’+‘(‘ + ””+ ‘+’ + CHAR(13)
SET @Values = SUBSTRING(@Values,1,LEN(@Values)-4)+‘)’+””
SET @SqlQry = @Sqlqry + @Values + CHAR(13) + ‘ FROM ‘ + @TableName
SELECT @SqlQry
CLOSE CInsert
DEALLOCATE CInsert
END
Go
Example:-
Create Table Employee (Employee_id Int, FirstName Varchar(100), LastName Varchar(40), DOB DateTime)
Go
Insert Into Employee values(101,‘Chandra’,‘Y’,‘01-01-1980′)
Insert Into Employee values(101,‘VVS’,‘V’,‘01-01-1981′)
Insert Into Employee values(101,‘Satish’,‘S’,‘01-01-1980′)
Insert Into Employee values(101,‘Shivan’,‘M’,‘01-01-1983′)
Go
Exec GenerateInsertStatement ‘Employee’ – This statement produces a select statement as below, copy this into SQL Editor and run the query
Go
SELECT ‘INSERT INTO Employee( [Employee_id], [DOB], [FirstName], [LastName]) VALUES(‘+ ”” + REPLACE(CAST([Employee_id] As VARCHAR(8000)),””,”””)+””+‘,’+ ”” + REPLACE(CAST([DOB] As VARCHAR(8000)),””,”””)+””+‘,’+ ”” + REPLACE(CAST([FirstName] As VARCHAR(8000)),””,”””)+””+‘,’+ ”” + REPLACE(CAST([LastName] As VARCHAR(8000)),””,”””)+””+‘)’ FROM Employee
OutPut:-
INSERT INTO Employee( [Employee_id], [DOB], [FirstName], [LastName]) VALUES(‘101′,‘Jan 1 1980 12:00AM’,‘Chandra’,‘Y’)
INSERT INTO Employee( [Employee_id], [DOB], [FirstName], [LastName]) VALUES(‘101′,‘Jan 1 1981 12:00AM’,‘VVS’,‘V’)
INSERT INTO Employee( [Employee_id], [DOB], [FirstName], [LastName]) VALUES(‘101′,‘Jan 1 1980 12:00AM’,‘Satish’,‘S’)
INSERT INTO Employee( [Employee_id], [DOB], [FirstName], [LastName]) VALUES(‘101′,‘Jan 1 1983 12:00AM’,‘Shivan’,‘M’)
Click here to download the script
Note:-
1.We can add where clause to the above Statement like any other SQL query to fitler the output.
2.Currently the above sproc doesn’t work properly if the table contains large data types like ntext, text etc
3.If any column / field in a particular record contains NULL value then insert statement for that record will be NULL
