SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tony Webster / Scripting INSERTs / Latest Posts

RE: Scripting INSERTs

Tony Webster — Fri, 02 Jan 2009 09:57:01 GMT

This seems to be addressing a different issue to the one I was trying to solve. First of all, it's pretty tied to SQL server 2005 and above - e.g. use of "sys.objects" rather than "sysobjects" - I wanted to retain the backwards compatibility, at least for the time being![quote][b]Adam Tappis (1/2/2009)[/b][hr]It's not got lot's of bells and whistles, but supports multiple tables/schemas (using wildcard in table and schema name)[/quote]I think this, together with the omission of a condition parameter, is the biggest indication that it's trying to do something different to what I set out to do. The article starts:[quote]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.[/quote]So I'm not really setting out to transfer large quantities of data from several tables - in fact, I would tend not to use this if the output were more than I could comfortably scroll through and do a quick sanity check. I've got SQL Data Compare - I'm not seeking to reinvent that particular wheel.[quote]It shouldn't need to cursor through the fields either.[/quote]It certainly doesn't [i]need[/i] to use a cursor to iterate through the fields, but I don't really feel any reason not to - I think it makes what it is doing reasonably clear, and performance in this context isn't really an issue, since it is only runs occasionally on quite a small data set (i.e. the set of columns for the single table specified - it wasn't the original intention to iterate across several, in particular since in many cases a condition would be present, and the same condition might well not make sense on several different tables).

RE: Scripting INSERTs

Adam Tappis — Fri, 02 Jan 2009 07:57:27 GMT

It shouldn't need to cursor through the fields either.Below is a version I'd used in the past. I've just updated it for SQL 2005 and 2008. It's not got lot's of bells and whistles, but supports multiple tables/schemas (using wildcard in table and schema name) and as far my testing goes, supports all the new data types (some additional enhancement may be required to handle sql_variant and UDT's).It also uses a cursor, but only to iterate over all the tables that match the parameter definitions.Oh, and it supports scripting data from views.[code]CREATE PROCEDURE [dbo].[usp_generate_inserts]/*** $One-Liner Generates an insert script for the specified object(s) $Detail This stored procedure will generate an INSERTS script for the data contained in a table or view. It supports wildcards for both table anme and schema parameters. $Example script data from [dbo].[test] EXECUTE usp_generate_inserts 'test' script data from all tables that begin with dim in the warehouse schema EXECUTE usp_generate_inserts 'dim%', 'warehouse' $Support SQL Server 2005 and 2008 $Created By Date ===================================================== Adam Tappis 31 Dec 2008***/( @i_table_name NVARCHAR(255) = NULL, @i_schema_name NVARCHAR(255) = 'dbo')AS DECLARE @vsSQL NVARCHAR(MAX), @vsCols NVARCHAR(MAX), @vsTableName SYSNAME, @maxID INTCREATE TABLE #tmp ( id INT IDENTITY , [--sqltext] NVARCHAR(MAX))CREATE TABLE #tmp2 ( id INT IDENTITY , [--sqltext] NVARCHAR(MAX))SET NOCOUNT ONDECLARE csrTables CURSOR FORSELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME([name])FROM sys.objectsWHERE type IN ('U', 'V')AND [name] LIKE ISNULL(@i_table_name, [name])AND SCHEMA_NAME(schema_id) LIKE ISNULL(@i_schema_name, SCHEMA_NAME(schema_id))ORDER BY [name]OPEN csrTablesFETCH NEXTFROM csrTablesINTO @vsTableNameWHILE @@fetch_status = 0BEGIN SELECT @vsSQL = '' , @vsCols = '' SELECT @vsSQL = @vsSQL + CASE -- ignore timestamp columns WHEN st.name IN ('timestamp') THEN '' -- handle binary types WHEN st.name IN ('image','binary','varbinary','geography','geometry') THEN 'ISNULL(sys.fn_varbintohexstr(CAST(' + sc.name + ' AS VARBINARY(MAX))), ''NULL'')+'',''+' -- handle GUID columns WHEN st.name in ('uniqueidentifier') THEN 'ISNULL('''''''' + CAST(' + sc.name + ' AS CHAR(36)) + '''''''',''NULL'')+'',''+' -- handle date and time types WHEN st.name LIKE '%date%' OR st.name LIKE '%time%' THEN 'ISNULL('''''''' + CONVERT(VARCHAR(50),' + sc.name + ',113) + '''''''',''NULL'')+'',''+' -- handle string types WHEN st.name in ('sql_variant','varchar','char','nvarchar','nchar','sysname','xml', 'text', 'ntext','hierarchyid') THEN 'ISNULL(''' + CASE LEFT(st.name,1) WHEN 'n' THEN 'N' ELSE '' END + '''''''+' + 'REPLACE(CAST(' + sc.name + ' AS NVARCHAR(MAX)),'''''''','''''''''''')+'''''''',''NULL'')+'',''+' -- numeric types ELSE 'ISNULL(CAST(' + sc.name + ' AS VARCHAR(MAX)),''NULL'')+'',''+' END FROM sys.columns sc JOIN sys.types st ON sc.user_type_id = st.user_type_id WHERE sc.object_id = OBJECT_ID(@vsTableName) ORDER BY column_id SELECT @vsCols = @vsCols + sc.name + ',' FROM sys.columns sc JOIN sys.types st ON sc.user_type_id = st.user_type_id WHERE sc.object_id = OBJECT_ID(@vsTableName) AND st.name <> 'timestamp' ORDER BY column_id SELECT @vsSQL = STUFF(@vsSQL,LEN(@vsSQL) - 2, 3, '''') , @vsCols = STUFF(@vsCols,LEN(@vsCols), 1, '') INSERT #tmp EXEC ('SELECT ' + @vsSQL + ' FROM ' + @vsTableName) -- Check if any rows were present IF (SELECT COUNT(*) FROM #tmp) > 0 BEGIN SELECT @maxID = MAX(id) FROM #tmp UPDATE #tmp SET [--sqltext] = 'SELECT ' + SUBSTRING([--sqltext],1,DATALENGTH([--sqltext])-1) + CASE id WHEN @maxID THEN '' ELSE CHAR(13) + CHAR(10) + ' UNION ALL' END INSERT #tmp2 SELECT CHAR(13) + CHAR(10) + 'DELETE FROM ' + @vsTableName + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) IF (SELECT COUNT(*) FROM sys.columns WHERE object_id = OBJECT_ID(@vsTableName) AND is_identity = 1) > 0 BEGIN INSERT #tmp2 SELECT CHAR(13) + CHAR(10) + 'SET IDENTITY_INSERT ' + @vsTableName + ' ON' END INSERT #tmp2 SELECT CHAR(13) + CHAR(10) + 'INSERT ' + @vsTableName + '(' + @vsCols + ')' INSERT #tmp2 SELECT [--sqltext] FROM #tmp IF (SELECT COUNT(*) FROM sys.columns WHERE object_id = OBJECT_ID(@vsTableName) AND is_identity = 1) > 0 BEGIN INSERT #tmp2 SELECT CHAR(13) + CHAR(10) + 'SET IDENTITY_INSERT ' + @vsTableName + ' OFF' END INSERT #tmp2 SELECT 'GO' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + 'UPDATE STATISTICS ' + @vsTableName + CHAR(13) + CHAR(10) + 'GO' DELETE #tmp END ELSE BEGIN INSERT #tmp2 SELECT '-- Now rows in table ' + @vsTableName END FETCH NEXT FROM csrTables INTO @vsTableNameENDCLOSE csrTablesDEALLOCATE csrTablesUPDATE #tmp2SET [--sqltext] = SUBSTRING([--sqltext], 1, CHARINDEX(',)', [--sqltext]) - 1) + ',NULL)'WHERE CHARINDEX(',)', [--sqltext]) <> 0UPDATE #tmp2SET [--sqltext] = REPLACE([--sqltext], ',''''', ',NULL')WHERE CHARINDEX(',''''', [--sqltext]) <> 0UPDATE #tmp2SET [--sqltext] = REPLACE([--sqltext], '(''''', ',NULL')WHERE CHARINDEX('(''''', [--sqltext]) <> 0SELECT [--sqltext]FROM #tmp2ORDER BY idSET NOCOUNT OFFGO[/code]

RE: Scripting INSERTs

David Rodriguez-468078 — Fri, 02 Jan 2009 06:55:27 GMT

Mmm, cursor don't loops over rows in the source table, but fields in the source table...GreetingsDavid Rodríguez

RE: Scripting INSERTs

Daniel Macey — Thu, 01 Jan 2009 20:05:50 GMT

Handy bit of code.I personally use SSMS Tools Pack's "Generate Insert Statements" function (http://www.ssmstoolspack.com/) directly within SSMS which also works with SQL 2000 however your version caters for the need when the generation must be scripted.

RE: Scripting INSERTs

Joachim Mutter — Thu, 01 Jan 2009 16:34:00 GMT

Hi all,this is a really good idea. I struggled sometimes about the need to get a few thousend rows from one database into a local test database, which could be done vrey easy by this code, Thanks for this.But it would be fine, if such a functionality could be located in a "tools" database, so I improved the code a bit. Now it is possible to point to the the target database with a additional input parameter of the procedure. Hope, that is an improvement someone needs.JoeHere is the code:--======================================================================-- Function to format the output value for inclusion in the VALUES list--======================================================================CREATE FUNCTION spVal(@str nvarchar(4000)) RETURNS nvarchar(4000) ASBEGINDECLARE @res nvarchar(4000)IF @str IS NULL SET @res = 'NULL'ELSE SET @res = 'N''' + REPLACE(@str, '''', '''''') + ''''RETURN(@res)ENDGO--======================================================================-- Function for the special case of formatting the output value for -- inclusion in the VALUES list--======================================================================CREATE FUNCTION spMVal(@val money) RETURNS nvarchar(4000) ASBEGINDECLARE @res nvarchar(4000)IF @val IS NULL SET @res = 'NULL'ELSE SET @res = CONVERT(varchar(20), @val, 2)RETURN(@res)ENDGO--======================================================================-- Create a script for inserting data into the specified table, based -- on the optional condition---- i.e-- EXEC [dbo].[spScriptInsert]-- @database = N'Test',-- @table = N'Pricing'--======================================================================CREATE PROC spScriptInsert( @database sysname, @table varchar(80), @condition varchar(80) = '1=1') AS DECLARE @fields nvarchar(4000) , @values nvarchar(4000) , @SQL nvarchar(4000) , @fieldname nvarchar(128) , @colorder int , @type varchar(40) if object_id('tempdb..##fields') is not null DROP table ##fields SET @SQL = 'SELECT top 100 colorder, syscolumns.name, systypes.name as type INTO ##fields FROM ' + @database + '.dbo.syscolumns JOIN ' + @database + '.dbo.sysobjects ON sysobjects.id = syscolumns.id JOIN ' + @database + '.dbo.systypes ON systypes.xusertype = syscolumns.xusertype WHERE sysobjects.name = ''' + @table + ''' and systypes.name <> ''text'' ORDER BY colorder' EXEC sp_executeSQL @sql 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 ' + @database + '.dbo.' + @table + ' WHERE ' + @condition EXEC sp_executeSQL @sqlGO

RE: Scripting INSERTs

Tony Webster — Tue, 30 Dec 2008 05:08:58 GMT

[quote][b]Adam Tappis (12/30/2008)[/b][hr]Although I agree that there is merit in this technique (as I've used it myself on previous projects). I would say this "quick and dirty" attempt is like re-inventing the wheel and making it oval![/quote]I prefer the comparison that I show you a hammer, and you say "but it's not a screwdriver!". I continue to find it useful from time to time, and will continue to use it - if you don't - well, don't use it!This is nothing more than something I knocked up in an idle few minutes that I thought was worth sharing. I did google for something similar first, but didn't hit on the right combination of search terms. One thing I was trying to achieve was to keep things as simple as possible while getting to the intended result, so that the procedure itself would perhaps spark some ideas in people about what could be done in terms of using SQL to write SQL.[quote]- place the scripts in source control- perform simple data comparisson- ease deployment (you'd be surprised how many DBA's refuse to or are not competent to do anything other than run SQL scripts)[/quote]This really goes way beyond the intention here. I guess you could use it to script some key coding table data, and source control that, but really that's not what it is for (personally, I would tend to use something like Red Gate SQL Data Compare for that). I use this for much more "ad hoc" tasks - when setting up a SQL Compare project or a SSIS/DTS job would be overkill.[quote]- Using NVARCHAR(MAX) so as not to exceed the 4000/8000 character limit[/quote]I still have some SQL 2000 databases it needs to work on. No reason not to change it for SQL 2005 and above ...[quote]- handling binary data correctly[/quote]Personally, just didn't feel the need - if it gets much beyond what can be verified visually, either in number of records, number of fields or the data types it needs to operate on, using any of the several other available solutions might be more appropriate. I guess it would be easy enough to do.[quote]- why not generate a single insert instead of 1 per row e.g. INSERT ... SELECT ... UNION ALL SELECT ...[/quote]Certainly could do - but why? This is intended for a small number of records, so performance is not a particular issue. Mind you, t would mitigate considerably the string length limitations for tables with a lot of columns I guess. I tend not to use this where there are a lot of columns though - I would prefer using something like SQL Data Compare that gives me a more digestible preview of what is going to happen in those cases.[quote]- why not make the insert re-runnable e.g. using a WHERE NOT EXISTS clause to only insert data that's not already there[/quote]Presumably based on ascertaining the key fields? Again, I was staying away from the additional complexity because I didn't particularly feel the need, and also with the limitations on string length brought about by the general approach, this could itself cause some problems.[quote]- even better with SQL 2008 you could generate a MERGE query to INSERT, UPDATE or DELETE as appropriate[/quote]See SQL 2000 comment earlier.

RE: Scripting INSERTs

Adam Tappis — Tue, 30 Dec 2008 04:18:26 GMT

Although I agree that there is merit in this technique (as I've used it myself on previous projects). I would say this "quick and dirty" attempt is like re-inventing the wheel and making it oval! It's been done before and been done better as a quick search on the web will show. Personally I used the usp_generate_inserts procedure mentioned above with a few personal enhancements.In terms of when this technique is useful, sripting an entire database to text files (schema and data) means you can:- place the scripts in source control- perform simple data comparisson- ease deployment (you'd be surprised how many DBA's refuse to or are not competent to do anything other than run SQL scripts)I tend to script out the static metadata e.g. product_type and include the insert script with the create table script.There are many enhancements that could be made e.g.- Using NVARCHAR(MAX) so as not to exceed the 4000/8000 character limit- handling binary data correctly- why not generate a single insert instead of 1 per row e.g. INSERT ... SELECT ... UNION ALL SELECT ...- why not make the insert re-runnable e.g. using a WHERE NOT EXISTS clause to only insert data that's not already there- even better with SQL 2008 you could generate a MERGE query to INSERT, UPDATE or DELETE as appropriate

RE: Scripting INSERTs

Tony Webster — Tue, 30 Dec 2008 03:07:28 GMT

[quote][b]Yelena Varshal (12/29/2008)[/b][hr]I mostly use CSV / BCP or MS ACCESS for that.[/quote]One key point about ths approach is that it's "quick and dirty" - if all you want to do is copy a few records from one database to another, and particularly if you have query windows open to both already, it's considerably quicker, easier, and I would guess in most cases less error prone to do it this way than messing about exporting to CSV, re-importing etc. The cut and paste of the script serves as a sanity check - a preview of what it's going to do before you actually do it, which I think can be a bit more transparent than kicking a SSIS, DTS or BCP job off.

RE: Scripting INSERTs

NewOzzie — Mon, 29 Dec 2008 20:56:06 GMT

This is also not catering for binary/varbinary columns correctly

RE: Scripting INSERTs

luckyHailong — Mon, 29 Dec 2008 18:28:04 GMT

if the data has "--" , "[" or other keyword does it work?

RE: Scripting INSERTs

Mike Sofen-356112 — Mon, 29 Dec 2008 16:45:41 GMT

This type of query is used when we need to create a script that mirrors the data in a given table to a sql script. This often used to place a base taxonomy into source control, or to create build scripts for a new server, or rollback scripts for a release. In all of these cases, we need the actual insert statements that will create the data, not the data by itself. Make sense?

RE: Scripting INSERTs

Yelena Varshal — Mon, 29 Dec 2008 15:25:59 GMT

I mostly use CSV / BCP or MS ACCESS for that.

RE: Scripting INSERTs

emiddlebrooks — Mon, 29 Dec 2008 15:21:32 GMT

[quote][b]Yelena Varshal (12/29/2008)[/b][hr]Why Insert... Select would not do?[/quote]I assume that your question is why you couldn't directly insert into one DB by selecting the data from another?This is meant to be more portable than that so that you could ship the script from one place to another without being connected. Personally I do this a lot via email to a client so that all they have to do is open the script and run it.

RE: Scripting INSERTs

Yelena Varshal — Mon, 29 Dec 2008 11:52:52 GMT

Why Insert... Select would not do?

RE: Scripting INSERTs

Alexander Kuznetsov — Mon, 29 Dec 2008 10:06:12 GMT

I've been using sp_generate_inserts for a long time, no problems:http://vyaskn.tripod.com/code/generate_inserts.txt

RE: Scripting INSERTs

Tony Webster — Mon, 29 Dec 2008 09:07:25 GMT

[quote]That means, if a table has 107 columns, then you'll only script 100 of them...does that make any sense? If indeed there is an obscure sql fault on greater than 100 columns, then skip the table and return a message to the user saying just that. Ditto for the text column...don't partially script tables. Also, you strangely AREN'T filtering for calculated columns, which you cannot insert values for...those columns need to be remove from the #Fields table.[/quote]All good points - this was never indented to be complete to that degree. What actually is more likely to happen before you hit 100 columns I think is that the varchar variable used to assemble the SQL would overflow its length.I don't actually run what it produces without checking it visually first. All I was after was something simple to fulfill a particular need - a plank to get over the stream at the bottom of the garden - a fully stress-tested suspension bridge it certainly isn't! That said, there are some good ideas here and elsewhere in the thread for improving it relatively simply.

RE: Scripting INSERTs

dfarran — Mon, 29 Dec 2008 09:03:11 GMT

Nice article. You can actually drop the cursor completely and replace with the following [code]DECLARE @fields nvarchar(4000)DECLARE @values nvarchar(4000)SET @fields = ''SET @values = ''SELECT @fields = @fields + ',[' + sc.name + ']' , @values = @values + '+'',''+dbo.' + CASE WHEN st.name = 'money' THEN 'spMVal' ELSE 'spVal' END + '([' + sc.name + '])' FROM syscolumns scJOIN sysobjects so ON so.id = sc.idJOIN systypes st ON st.xusertype = sc.xusertypeWHERE so.name = @table AND st.name <> 'text'ORDER BY sc.colorderPRINT @fieldsPRINT @values[/code]In SQL 2000 there is the (mentioned in the caveats) issue with nvarchar(4000) meaning you will potentially run out of string space, this is much less of an issue in 2005 with nvarchar(MAX).I hope someone finds this SELECT @str = @str + ... useful, I know I have. I have found some occasions where it only returns one line, but if you include TOP 100 percent it will generally solve this issue.

RE: Scripting INSERTs

Etni — Mon, 29 Dec 2008 08:56:50 GMT

google sp_generate_inserts

RE: Scripting INSERTs

grant.owens — Mon, 29 Dec 2008 08:56:08 GMT

Nice stuff and very useful. There is a more sophisticated version of this at http://vyaskn.tripod.com/code.htm#inserts that i've used fairly extensively.

RE: Scripting INSERTs

Mike Sofen-356112 — Mon, 29 Dec 2008 06:10:52 GMT

Suggested revisions: eliminate the cursor with a "while" loop, and use the metadata more effectively to eliminate the function calls. The input param that is nullable simply needs the standard "= null", rather than " = '1=1' ". Use "set nocount on" at the beginning of the sproc to eliminate the bazillion return messages this sproc generates. And the biggies: you're taking the top 100 columns from a table. That means, if a table has 107 columns, then you'll only script 100 of them...does that make any sense? If indeed there is an obscure sql fault on greater than 100 columns, then skip the table and return a message to the user saying just that. Ditto for the text column...don't partially script tables. Also, you strangely AREN'T filtering for calculated columns, which you cannot insert values for...those columns need to be remove from the #Fields table.Mike SofenSan Diego, CA

RE: Scripting INSERTs

Adam Tappis — Mon, 29 Dec 2008 03:55:47 GMT

Nicely done. However I would look to apply the the filter condition as early as possible, because on a 1 million row table, the procedure will cursor over all 1 million rows even if the condition limits the result to just 3 rows...

RE: Scripting INSERTs

Anipaul — Mon, 29 Dec 2008 00:52:44 GMT

Nice article...

Scripting INSERTs

Tony Webster — Mon, 29 Dec 2008 00:15:44 GMT

Comments posted to this topic are about the item [B]Scripting INSERTs[/B]