对多行使用SELECT INTO [英] using SELECT INTO with multiple rows
问题描述
这是 我想通过利用SELECT INTO使用查询结果创建一个表. 语法
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
与我要实现的目标非常接近,不同之处在于我希望FROM将查询用作源.
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?
推荐答案
您可以将查询放入公共表表达式或派生表中,然后从该表中放入SELECT ... INTO
.
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
在这种情况下,您可能需要进行一些显式转换才能获得所需的列数据类型(datetime
而不是char
等)
In this case you would probably need some explicit casts to get the desired column datatype (datetime
rather than char
etc.)
这篇关于对多行使用SELECT INTO的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!