using SELECT INTO with multiple rows

Question

This is re I want to create a table using the results of a query by utilizing SELECT INTO. The syntax

SELECT *
INTO Persons_Backup
FROM Persons

is very close to what I want to achieve, with the difference being that I want the FROM to use a query as source.

My situation is a bit more complicated than these simple examples.

I need to create a table and insert multiple rows at the same time. If I could (I can't) use a previously created table the statement would look like this:

INSERT INTO Person_Backup12 (Col1, Col2, Col3)
Select 1, 'a','2001-01-01 12:00'
UNION ALL
Select 83, 'z','2011-09-30 13:27'
UNION ALL
Select 777, 'k','1997-04-25 09:27'

Can I do that while creating a table at the same time?

If you're moving large amounts of data then SELECT INTO is not the most efficient way of doing this. If possible, rather follow the CREATE TABLE INSERT INTO approach. — tobias86
– tobias86, Commented Apr 6, 2011 at 12:29

Martin Smith · Accepted Answer · 2011-04-06 12:22:45Z

6

You can put your query into a common table expression or derived table then SELECT ... INTO from that.

;WITH cte (Col1, Col2, Col3) AS
(
Select 1, 'a','2001-01-01 12:00'
UNION ALL
Select 83, 'z','2011-09-30 13:27'
UNION ALL
Select 777, 'k','1997-04-25 09:27'
)
SELECT *
INTO NewTable
FROM cte

In this case you would probably need some explicit casts to get the desired column datatype (datetime rather than char etc.)

answered Apr 6, 2011 at 12:22

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

callisto Over a year ago

You are a star! thanks. I did not even consider using a CTE. Nice!

JNK · Accepted Answer · 2011-04-06 12:36:41Z

4

A CTE shouldn't be necessary:

Select 1 as 'Col1', 'a' as 'Col2','2001-01-01 12:00' as 'Col3'
INTO Person_Backup12
UNION ALL
Select 83, 'z','2011-09-30 13:27'
UNION ALL
Select 777, 'k','1997-04-25 09:27'

Worked fine for me in 2008 r2.

answered Apr 6, 2011 at 12:36

JNK

65.5k15 gold badges127 silver badges139 bronze badges

1 Comment

JNK Over a year ago

@Martin - a lot of folks I work with use SELECT INTO frequently so I know more than I want to about it :)

tobias86 · Accepted Answer · 2011-04-06 12:27:58Z

2

It is possible, yes:

SELECT *
INTO Persons_Backup
FROM 
(
    Select 1 AS Col1, 'a' AS Col2,'2001-01-01 12:00' AS Col3
    UNION ALL
    Select 83 AS Col1, 'z' AS Col2,'2011-09-30 13:27' AS Col3
    UNION ALL
    Select 777 AS Col1, 'k' AS Col2,'1997-04-25 09:27' AS Col3
) AS SomeQuery

answered Apr 6, 2011 at 12:27

tobias86

5,0291 gold badge23 silver badges31 bronze badges

Collectives™ on Stack Overflow

using SELECT INTO with multiple rows

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related