Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Scripting INSERTs

By Tony Webster,

I found myself using DTS fairly frequently to transfer trivial amounts of data from one database to another - frequently fairly small volumes, such as new additions to coding tables, and restoring that one record or two that someone had accidentally deleted. I do make use of tools like Red Gate's SQL Data Compare for more complex data transfers, and we have an in-house engine that can keep tables synchronized between servers, but I have found this little script very useful for smaller, more "ad hoc" tasks.

The spScriptInsert Procedure

From this came a stored procedure to write a collection of INSERTs for the data (together with a couple of helper functions):


--======================================================================
-- Function to format the output value for inclusion in the VALUES list
--======================================================================
CREATE FUNCTION spVal(@str nvarchar(4000)) RETURNS nvarchar(4000) AS BEGIN DECLARE @res nvarchar(4000) IF @str IS NULL
SET @res = 'NULL'
ELSE
SET @res = 'N''' + REPLACE(@str, '''', '''''') + '''' RETURN(@res) END GO --======================================================================
-- Function for the special case of formatting the output value for -- inclusion in the VALUES list
--======================================================================
CREATE FUNCTION spMVal(@val money) RETURNS nvarchar(4000) AS BEGIN DECLARE @res nvarchar(4000) IF @val IS NULL
SET @res = 'NULL'
ELSE
SET @res = CONVERT(varchar(20), @val, 2) RETURN(@res) END GO --======================================================================
-- Create a script for inserting data into the specified table, based -- on the optional condition
--======================================================================
CREATE PROC spScriptInsert(@table varchar(80), @condition varchar(80) = '1=1') AS DECLARE @fields nvarchar(4000), @values nvarchar(4000), @SQL nvarchar(4000)
DECLARE @colorder int, @fieldname nvarchar(128), @type varchar(40) SELECT top 100 colorder, syscolumns.name, systypes.name as type
INTO #fields
FROM syscolumns
JOIN sysobjects ON sysobjects.id = syscolumns.id
JOIN systypes ON systypes.xusertype = syscolumns.xusertype
WHERE sysobjects.name = @table and systypes.name <> 'text'
ORDER BY colorder DECLARE fieldscursor CURSOR FOR
SELECT colorder, name, type
FROM #fields
ORDER BY colorder OPEN fieldscursor FETCH NEXT FROM fieldscursor INTO @colorder, @fieldname, @type SET @fields = ''
SET @values = '' WHILE @@FETCH_STATUS = 0
BEGIN
SET @fields = @fields + ',[' + @fieldname + ']'
IF @type = 'money'
-- Special case for "money" type
SET @values = @values + '+'',''+dbo.spMVal([' + @fieldname + '])'
ELSE
SET @values = @values + '+'',''+dbo.spVal([' + @fieldname + '])' FETCH NEXT FROM fieldscursor INTO @colorder, @fieldname, @type
END DEALLOCATE fieldscursor SET @SQL = 'SELECT ''INSERT INTO ' + @table + '(' + SUBSTRING(@fields, 2, 2000) +
') VALUES (''+ ' + SUBSTRING(@values, 6, 1000) + '+'')'' FROM ' +
@table + ' WHERE ' + @condition EXEC sp_executeSQL @sql GO

What this does is allow you to specify a table and optionally a condition - for example, in "Northwind":

spScriptInsert 'Products', 'ProductID BETWEEN 70 AND 73'

produces as its results the INSERT statements:

INSERT INTO Products([ProductID],..) VALUES ('70','...
INSERT INTO Products([ProductID],...) VALUES ('71','...
INSERT INTO Products([ProductID],...) VALUES ('72','...
INSERT INTO Products([ProductID],...) VALUES ('73','...

You can then copy the results set, and paste it into another query window in SQL Management Studio or Query Analyser connected to the intended target database.

How it works

The script make liberal use of the system tables, a cursor, and some dynamic SQL at the end. It uses the "old" format system tables, so it works on both SQL Server 2000 and 2005 (no opportunity to try 2008 at the moment!).

The first job is to assemble the field list for the table into the temporary table "#fields", including the type of the field. The cursor "fieldscursor" is then used for a query on this table to iterate thorough the fields to construct "@fields" - a list of the fieldnames (surrounded by brackets "[" and "]" in case the field name happens to be a reserved word), and "@values" - which is what to select from the table to return the information to construct the INSERT statements. Note the use of the "spVal" function to return either a quoted version of the column contents in each case, or "NULL" if appropriate, and the spMVal function for the special case of the "money" data type, which needs a CONVERT function. Note that these functions are executed for every single value returned, evaluated in the dynamic SQL under construction.

The fields list and the values list to be returned are then used to construct the SELECT statement into the @SQL variable, and this is executed to return a list of INSERT statements for each record in the target table, subject to the condition (or defaulting to the condition "1=1" (i.e. all records) if not supplied.

Summary

It is a bit "quick and dirty", but it has proved useful in a number of cases to do some little ad-hoc data transfers with the minimum of effort. For production updates, I would use a tool such as Red Gate's SQL Data Compare to roll out from test via a staging environment, but just occasionally, this can be useful to script up some basic coding table additions. It's perhaps more useful for copying data between test environments.

Another use I have found for it is if a particular record is deleted in error in a production database, and you have maybe a log-shipped copy of the database, or access to a backup which still has the record in question. This can be used to script an INSERT for the missing record from the backup version of the database - and of couse two invocations will script both master and detail record inserts.

Some caveats, and possibilities for improvement:

  • For tables with a large number of fields, the generated SQL may overflow the available length of the variable receiving the SQL to be executed (the helper functions have short names for this reason!)
  • If the table has an IDENTITY field (as indeed the "Products" table in "Northwind" does) it is up to you to enable inserts for it in the target database (i.e. "SET IDENTITY_INSERT Products ON") and then off again when you have finished.
  • It does not attempt to copy "text" fields.
  • As you can see from the code, it is necessary to have a special case for the "money" type.
  • For tables with a lot of fields, the variables used in the spScriptInsert procedure overflow, and it can fail with a fairly criptic error message, due to the syntax errors produced in the constructed SQL. It would be possible to detect this and produce a more "friendly" error messages.
  • It uses the "old" system tables, so it works with both SQL Server 2000 and 2005.

These restrictions aside, I hope others find this as useful as I do!

Total article views: 11283 | Views in the last 30 days: 4
 
Related Articles
FORUM

Insert spaces into a decimal field

Insert spaces into a decimal field

FORUM

published reports in production server not functioning

published reports in production server not functioning

FORUM

"function"

"function"

FORUM

insert null in smalldatetime field

how to insert null in smalldatetime field

FORUM

CONVERT nvarchar to datetime

INSERT Query

Tags
copying    
replication    
scripting    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones