Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Scripting INSERTs Expand / Collapse
Author
Message
Posted Monday, December 29, 2008 3:21 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:02 PM
Points: 2,098, Visits: 272
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.



Post #626867
Posted Monday, December 29, 2008 3:25 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:12 PM
Points: 3,475, Visits: 582
I mostly use CSV / BCP or MS ACCESS for that.


Regards,
Yelena Varshal

Post #626869
Posted Monday, December 29, 2008 4:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:40 AM
Points: 3, Visits: 49
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?
Post #626910
Posted Monday, December 29, 2008 6:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 30, 2011 1:00 AM
Points: 14, Visits: 17
if the data has "--" , "[" or other keyword does it work?
Post #626938
Posted Monday, December 29, 2008 8:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 PM
Points: 42, Visits: 197
This is also not catering for binary/varbinary columns correctly
Post #626991
Posted Tuesday, December 30, 2008 3:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:08 AM
Points: 115, Visits: 313
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.
Post #627175
Posted Tuesday, December 30, 2008 4:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 19, 2014 12:47 AM
Points: 9, Visits: 88
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
Post #627204
Posted Tuesday, December 30, 2008 5:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:08 AM
Points: 115, Visits: 313
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.
Post #627236
Posted Thursday, January 1, 2009 4:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 2:46 PM
Points: 1, Visits: 29
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

Post #628536
Posted Thursday, January 1, 2009 8:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:03 PM
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.
Post #628567
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse