generate inserts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

generate inserts

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-24 : 13:43:29

is there any easy way I can take a select statment
(such as select from payments where datetime>'20071122' and output a sql insert statment for these records?

I basically need to move a specific set of records from one sql server to another (both sql server 2005)
any suggestions for the best way to do this?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-24 : 14:38:52

Just add the INSERT syntax as character expressions like this

SELECT 'INSERT NewPayments VALUES ('
    + CAST(numericcol AS varchar(10)) + ', '
    + '''' + charactercol + ''', '
    + ')'
FROM payments 
WHERE datecol > '20071122'

Note, that I have converted a numeric value into a character value so that it can be concatenated into the INSERT and I have added the necessary two single quotes inside the character expression for each one single quote that needs to be in the resulting INSERT around the character column value - so the resulting INSERT is syntactically correct.
If you are not selecting values for every column in the INSERT then you need to add the column list after the table name as you would for a regular INSERT.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-26 : 03:53:41

http://vyaskn.tripod.com/code/generate_inserts.txt

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-26 : 05:10:35

thanks but didn't work in 2005

when trying to do the sp i got

Using Master database
Checking for the existence of this procedure
Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'master.dbo.sp_MS_upd_sysobj_category'.
Created the procedure
Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'master.dbo.sp_MS_upd_sysobj_category'.
Granting EXECUTE permission on sp_generate_inserts to all users
Done

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-26 : 05:13:50

Try
http://vyaskn.tripod.com/code/generate_inserts_2005.txt

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources