调用OPENROWSET时INSERT INTO和SELECT INTO之间的区别

select 1 as X,d.* into [TravelData] from OPENROWSET('SQLNCLI','Server=<redacted>',
     'exec [OtherDB].[GetTravelData]  1, ''28-Nov-2016 16:00'', ''28-Nov-2016 19:00''') as d

I have this as a way to slurp remote DB into a local table. This syntax appears to work BUT I get error:

我将此作为一种将远程数据库篡改为本地表的方法。这个语法似乎工作但我得到错误：

There is already an object named 'TravelData' in the database.

数据库中已经有一个名为“TravelData”的对象。

Makes sense, SELECT INTO is supposed to create the table. But thinking I'd simply change SELECT to INSERT I then get syntax errors. What should the correct syntax be to get this data into an existing DB table whose structure matches the query output?

有道理，SELECT INTO应该创建表。但是我想我只是将SELECT更改为INSERT I然后会出现语法错误。将此数据放入结构与查询输出匹配的现有数据库表时，正确的语法应该是什么？

2 个解决方案

#1

It has nothing to do with using OPENROWSET.

它与使用OPENROWSET无关。

INSERT INTO ... requires that the table already exist.

INSERT INTO ...要求表已存在。

SELECT ... INTO requires that the table not exist. The table will be created by the statement using the columns defined in the SELECT.

SELECT ... INTO要求表不存在。该表将由语句使用SELECT中定义的列创建。

#2

Here is the INSERT INTO SELECT syntax

这是INSERT INTO SELECT语法

INSERT INTO [TravelData]
            (X,
             col1,
             col2,
             ...)
SELECT 1 AS X,
       d.col1,
       d.col2,
       .....
FROM   OPENROWSET('SQLNCLI',
                  'Server=<redacted>',
                  'exec [OtherDB].[GetTravelData]  1, ''28-Nov-2016 16:00'', ''28-Nov-2016 19:00''') AS d

Note : Instead of * in select list add the column list. Also in Insert mention the column list

注意：而不是选择列表中的*添加列列表。同样在Insert中提到列列表

#1