Store EXEC Result into Table

Question

I have the following query:

EXEC ('SELECT  ID,        
  Date,        
  Type,                                
  FROM DB2T.BBT') AT DB2

How do I store this into a table so that I can add results from another query to do it? I'm very new to sql and have done a lot of searching but it seems everything I find is specific to that scenario. Is there a simple way to do this?

Based on the fact that you have already asked one or two questions on this exact topic it is obvious this is an xy problem. http://xyproblem.info/ — Sean Lange, Aug 01 '18 at 18:10

score 0 · Answer 1 · answered Aug 01 '18 at 17:40

0

You need SELECT. . . INTO :

SELECT ID, Date, Type INTO Table_name                             
FROM DB2T.BBT;

If you have already table then you can use INSERT INTO statement :

INSERT INTO Table_name (ID, Date, Type)
     SELECT  ID, Date, Type                     
     FROM DB2T.BBT;

answered Aug 01 '18 at 17:40

Yogesh Sharma

49,870
5
26
52

I get an error saying "Invalid object name 'DB2T.BBT'". I think I need the "at DB2" at the end to make the query work – tjc Aug 01 '18 at 17:43
@tjc. . . `DB2T.BBT` is your object or table_name (`Schema.Table_name`), you have to ensure `DB2T.BBT` exists in your current database. – Yogesh Sharma Aug 01 '18 at 17:46
I was told that this exec statement is the only way to get the data that I want, is there no way to have that exec statement and then have something after that stores it somewhere? – tjc Aug 01 '18 at 17:51
@tjc, "Exec... At DB2' is allowing you to run dynamic sql on a linked server. Place all of Yogesh's syntax in your exec statement, wrapped in single tics. – Ryan B. Aug 01 '18 at 17:52
This answer is a poor one. There is a substantial difference between accessing the linked server through a `SELECT` and an `EXEC AT` and it can have major performance implications. – Siddhartha Gandhi Jun 28 '21 at 03:59

Store EXEC Result into Table

1 Answers1