SQL import/export

Question

What is the most straightforward way to run a select statement such that the output can be inserted back into SQL later? The data set size will be very small, (probably less than 100 rows). I realize that there are DB specific import/export tools but I'm wondering if this can be done with SQL only (plus maybe some unix command line).

EDIT:

To clarify, I want to save a set of rows along the lines of:

select * from my_table where category='test'

And then, some time later, I want those matching rows to be inserted back into the same table. I had assumed that the SQL select output would be saved into a file, and the file would be re-played through a sql command line utility.

Can you be more specific? This problem has about 50 nice solutions, in general (e.g. concatenating strings to generate `INSERT` statements directly in SQL)... also, please specify the database you're using — Lukas Eder, May 14 '12 at 14:08
This is very vague, but with more information it could be a worthwhile question so I hope it doesnt get closed. To answer we will need to know what database you are using. Also why are you trying to do this? is it a backup of a table? duplicating data etc? — Purplegoldfish, May 14 '12 at 14:21
Put the select statement into a table, and then insert the results back. This will work in any database. However, the particular syntax may depend on the database. — Gordon Linoff, May 14 '12 at 14:30

score 2 · Accepted Answer · edited May 23 '17 at 12:00

One way to do it would be generate the SQL by just joining some strings together to generate the INSERT statement. Making the assumption you are using MSSQL:

SELECT'INSERT INTO Table1(Col1,Col2) VALUES('+CAST(Col1 AS VARCHAR(500))+',' +CAST(Col2 AS VARCHAR(500))+')' FROM Table1

This isn't a great way to do it as obviously it doesn't scale so well but for small uses it isn't too bad, especially if you want a quick answer.

I don't know how your database is structured but make sure you remember to check the data you are inserting wont be affected by any PK / FK constraints.

Also check out this question: What is the best way to auto-generate INSERT statements for a SQL Server table?

You could also look into using BCP http://msdn.microsoft.com/en-us/library/aa196743(v=sql.80).aspx A google for SQL BCP will show you loads of results which should be helpful.

I know you want code based solutions but I will just mention that you can also export this kind of data from SQL Management Studio (Assuming that is what you are using).

score 0 · Answer 2 · answered May 14 '12 at 14:28

I'm not sure if I understood correctly but you want to run a select and insert the result into a table on the same command?

Not sure if it can be done, but what I can think that mostly looks like what you want is the OUTPUT statement:

declare @mytables table
(
   name varchar(50)
)

INSERT INTO @mytables    
output inserted.*   
select name 
from sys.tables

here you are inserting into @mytables the result of the query select name from sys.tables and presenting the result with the OUTPUT clause

score 0 · Answer 3 · answered May 14 '12 at 14:37

if database is TableA { Id, Column1, Column2 }

SELECT 
     'INSERT INTO [TableA] ([Id], [Column1], [Column2])' 
     + VALUES (' + [Id] + ', ''' + [Column1] + ''', ''' + [Column2] + ''')'
FROM TableA

Result will be:

INSERT INTO [TableA] ([Id], [Column1], [Column2]) VALUES (1, 'Value1', 'Value2')
INSERT INTO [TableA] ([Id], [Column1], [Column2]) VALUES (2, 'Value11', 'Value22')

Then you can copy the result into a file

SQL import/export

3 Answers3