tags:

views:

733

answers:

8

I would like to export a ad hoc Select query result sets from SQL Server to be exported directly as Insert Statements.

I would love to see a Save As option "Insert.." along with the other current available options (csv, txt) when you right-click in SSMS. I am not exporting from an existing physical table and I have no permissions to create new tables so the options to script physical tables are not an option for me.

I have to script either from temporary tables or from the result set in the query window.

Right now I can export to csv and then import that file into another table but that's time consuming for repetitive work.

The tool has to create proper Inserts and understand the data types when it creates values for NULL values.

+2  A: 

I know it's not exactly what you're looking for, but you can do an insert off of a select statement:

INSERT INTO tbl(a,b) SELECT c,d FROM tbl2 WHERE c IN(...)

Obviously this is quite rough, but I hope it get's the point of what I'm trying to say across.

Ian Jacobs
I want ready to execute Insert statements so I can copy them to be executed somewhere else.
Abdu
Plus I can't create new tables if you're saying tbl is a new table. I thought my question was pretty informative.
Abdu
+1  A: 

Look here.

Stu
I am not exporting from a physical table. This fact was mentioned in my question.
Abdu
+5  A: 

Personally, I would just write a select against the table and generate the inserts myself. Piece of cake.

For example:

SELECT  'insert into [pubs].[dbo].[authors](
        [au_id], 
        [au_lname], 
        [au_fname], 
        [phone], 
        [address], 
        [city], 
        [state], 
        [zip], 
        [contract])
    values( ''' + 
    [au_id] + ''', ''' + 
    [au_lname] + ''', ''' +
    [au_fname] + ''', ''' +
    [phone] + ''', ''' +
    [address] + ''', ''' +
    [city] + ''', ''' +
    [state] + ''', ''' +
    [zip] + ''', ' +
    cast([contract] as nvarchar) + ');'
FROM    [pubs].[dbo].[authors]

will produce

insert into [pubs].[dbo].[authors](
        [au_id], 
        [au_lname], 
        [au_fname], 
        [phone], 
        [address], 
        [city], 
        [state], 
        [zip], 
        [contract])
    values( '172-32-1176', 'White', 'Johnson', '408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'CA', '94025', 1);
insert into [pubs].[dbo].[authors](
        [au_id], 
        [au_lname], 
        [au_fname], 
        [phone], 
        [address], 
        [city], 
        [state], 
        [zip], 
        [contract])
    values( '213-46-8915', 'Green', 'Marjorie', '415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', '94618', 1);
... etc ...

A couple pitfalls:

  1. Don't forget to wrap your single quotes
  2. This assumes a clean database and is not SQL Injection safe.
John MacIntyre
Thanks but that's even more cumbersome than exporting to a file and then importing, specially even more when writing complex select statements. Plus too many single quotes to worry about! :)
Abdu
A: 

I wrote a script for this problem that should work on any table. (I say "should" because the script isn't 100% tested and is still a little rough.) You can find it at http://www.jessemclain.com/downloads/code/sql/spd_Tool_Get_Insert_Into_Values.sql.txt

There's some junk at the bottom of the file that the hosting company appends, just strip that off. To run, just change the value of @Source_Table to your table.

NOTE: the text file I posted renders ok in Firefox 3.0.11, but not in IE7.

Jesse
+1  A: 

take a look at the SSMS Tools Pack add in for SSMS which allows you to do just what you need.

Mladen Prajdic
This creates from an existing table. I need to create from the result set in the query window.
Abdu
it can do that too. right click on the result set and choose Script Grid Results.
Mladen Prajdic
+1  A: 

WinSQL at www.synametrics.com has that feature and it's pretty handy. Not sure if that feature is in the free version - but you get the Professional version to try for 30 days anyway.

It's a very handy and easy to use query tool for any ODBC connected database.

Ron

Ron Savage
WinSQL does it. Too bad it's only in the Pro version which is $249.
Abdu
+1  A: 

TOAD can do this from the data grid Save As menu.

Noah Yetter
Can't see this in the freeware version. I see an Export Wizard and a 'SQL Script' output format and then it says this format has no license. I assume it means I need to buy the commercial version. I am not willing to pay $595 (their least expensive version) to have this feature.
Abdu
This feature may not be available in all versions and/or platforms, I'm using 9.7 for Oracle. IMHO, TOAD is easily worth the price of admission, though obviously budgets and value scales differ.
Noah Yetter
A: 

Squirrel SQL can also do this.

Write an SQL query, execute it to test. Then highlight it, and select Script/Insert statement (don't remember the exact wording).

sleske