Tool to export result set from SQL to Insert statements?

Question

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.

Answer 1

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.

Answer 2

Look here.

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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