Introduction
The stored procedure InsertGenerator generates the INSERT
..VALUES
statements for the specified table name.
Background
SQL Server doesn�t allow generation of INSERT
statements for the table data, when Generate SQL Script option is selected. The workaround is to make use of DTS for transferring data across servers. However, there exists a need to generate INSERT
statements from the tables for porting data. Simplest example is when small or large amount of data needs to be taken out on a removable storage media and copied to a remote location, INSERT
..VALUES
statements come handy.
Using the code
This small yet useful stored procedure will take as parameter the table name and generates the
INSERT
SQL statements for the same. The output can be redirected to either text format (Ctrl+T in Query Analyzer) or Output to a text file. The procedure accepts an input
varchar
type parameter that has to be the table name under consideration for statement generation.
CREATE PROC InsertGenerator
(@tableName varchar(100)) as
Then it includes a cursor to fetch column specific information (column name and the data type thereof) from information_schema.columns pseudo entity and loop through for building the INSERT
and VALUES
clauses of an INSERT
DML statement.
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns
WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000)
DECLARE @stringData nvarchar(3000)
DECLARE @dataType nvarchar(1000)
SET @string='INSERT '+@tableName+'('
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType
IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END
WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
SET @stringData=@stringData+'''''''''+
isnull('+@colName+','''')+'''''',''+'
END
ELSE
if @dataType in ('text','ntext')
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money'
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+
isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
SET @stringData=@stringData+'''convert(datetime,''''''+
isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast(convert(varbinary,'+@colName+')
as varchar(6)),''0'')+'''''',''+'
END
ELSE
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END
SET @string=@string+@colName+','
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
After both of the clauses are built, the VALUES
clause contains a trailing comma which needs to be replaced with a single quote. The prefixed clause will only face removal of the trailing comma.
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ')
VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''
FROM '+@tableName
exec sp_executesql @query
Eventually, close and de-allocate the cursor created for columns information.
CLOSE cursCol
DEALLOCATE cursCol
After the procedure is compiled and created, just run it in Query Analyzer by using the following syntax:
InsertGenerator <tablename>
E.g.:
USE pubs
GO
InsertGenerator employee
GO
Then copy the INSERT
statements and run�em in the query analyzer.
Before the INSERT
s are run, SET IDENTITY_INSERT <TABLENAME>ON
should be passed for adding values in an identity-based column.
Points of Interest
D: T-SQL lovers might wish to extend this procedure for providing support for binary data types such as IMAGE
etc.
History
- Ver 0.1b added Dec 5, 03.