Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scripting INSERTs


Scripting INSERTs

Author
Message
emiddlebrooks
emiddlebrooks
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2503 Visits: 343
Yelena Varshal (12/29/2008)
Why Insert... Select would not do?


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.



Yelena Varshal
Yelena Varshal
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3634 Visits: 594
I mostly use CSV / BCP or MS ACCESS for that.


Regards,
Yelena Varshal

Mike Sofen-356112
Mike Sofen-356112
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 59
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?
luckyHailong
luckyHailong
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 17
if the data has "--" , "[" or other keyword does it work?
NewOzzie
NewOzzie
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 197
This is also not catering for binary/varbinary columns correctly
Tony Webster
Tony Webster
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 321
Yelena Varshal (12/29/2008)
I mostly use CSV / BCP or MS ACCESS for that.


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.
Adam Tappis
Adam Tappis
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 97
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
Tony Webster
Tony Webster
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 321
Adam Tappis (12/30/2008)
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!

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.

- 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)

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.

- Using NVARCHAR(MAX) so as not to exceed the 4000/8000 character limit

I still have some SQL 2000 databases it needs to work on. No reason not to change it for SQL 2005 and above ...

- handling binary data correctly

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.

- why not generate a single insert instead of 1 per row e.g. INSERT ... SELECT ... UNION ALL SELECT ...

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.

- why not make the insert re-runnable e.g. using a WHERE NOT EXISTS clause to only insert data that's not already there

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.

- even better with SQL 2008 you could generate a MERGE query to INSERT, UPDATE or DELETE as appropriate

See SQL 2000 comment earlier.
Joachim Mutter
Joachim Mutter
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 31
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.

Joe


Here is the code:

--======================================================================
-- 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
--
-- 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 @sql

GO
Daniel Macey
Daniel Macey
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 129
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search