CODEWITHSUNDEEP
sql-serversql-server-2005
xaxa
xaxa

Reputation: 1159

Export SELECT with WHERE as INSERTs

I need to make a set of insert statements from the result of a select.

I've found similar questions on SO, however as far as I can see, they all consider making a full export of a table, but I need to run a particular query with where conditions, say

select * from T where open_time > '2013-11-01 00:00:00'

UPD: I need to have a script as an output, because later I will insert these rows into separate DB.

Upvotes: 0

Views: 84

Answers (4)

M.Ali
M.Ali

Reputation: 69524

INSERT INTO Destination_Table (Column1, Column2, Column3, .....)
SELECT Column1, Column2, Column3, ..
FROM TABLE_Name   --<-- Source Table Name
WHERE Condition...    --<--- any conditions or Filters here

The number of Columns and DataTypes in INSERT and SELECT statement should match

If the destination Tables doesnt exist you can create a table on fly by doing this

Create a table on fly

SELECT Column1, Column2, Column3, ..     INTO   New_Table_Name 
FROM TABLE_Name   --<-- Source Table Name
WHERE Condition...    --<--- any conditions or Filters here

*warning * if a table by the name New_Table_Name already exists then you will have to use the INSERT INTO Method since using the SELECT INTO syntax will throw an error in that case

Upvotes: 1

Nripin Babu
Nripin Babu

Reputation: 1

select into can help you

SELECT LastName, FirstName, Phone
INTO dbo.PhoneList492
FROM Person.Contact
WHERE Phone LIKE '492%'

Upvotes: 0

anar khalilov
anar khalilov

Reputation: 17498

You can replace column names with the columns of your table.

INSERT INTO NEW_T (Column1, Column2, Column2, ...)
SELECT Column1, Column2, Column3, ...
FROM T
WHERE open_time > '2013-11-01 00:00:00'

I am assuming you already have created the new table NEW_T.

Upvotes: 0

Szymon
Szymon

Reputation: 43023

You can use dynamic SQL:

declare @sql nvarchar(max) = ''

select @sql = @sql + 'insert into table1(column1, column2) values (' 
    + T.column1 + ',' + T.column2 + '); ' 
from T where open_time > '2013-11-01 00:00:00'

print (@sql)

This will generate insert statements you can copy and use later.

You may have to modify it as depending on the data types you may need quotes and formatting for the columns returned from T.

Upvotes: 1

Related Questions