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

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?

Mr. Boy
  • 60,845
  • 93
  • 320
  • 589
  • I think you mean "`SELECT INTO` is supposed to create the **table**", not DB. You can read a comparison of `SELECT INTO` and `INSERT INTO` here: [INSERT INTO vs SELECT INTO](http://stackoverflow.com/questions/6947983/insert-into-vs-select-into). The answer below from [Prdp](http://stackoverflow.com/users/3349551/prdp) seems to deal with your specific situation perfectly. – 3N1GM4 Dec 21 '16 at 17:00

2 Answers2

2

Here is the INSERT INTO SELECT syntax 

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

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
2

It has nothing to do with using OPENROWSET.

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

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

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66