开发者

using SELECT INTO with multiple rows

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:

INSE开发者_如何学CRT 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?


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


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.


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜