Stored Procedure to Insert the result of a parameterized OPENQEURY into a local table

Question

So this is "fun", and by "fun" I mean mindbending. I have managed to:

Feed parameters to OPENQUERY and get results like this:

Declare @MyString    varchar(max)
,       @TheDateAfter datetime

set     @TheDateAfter = DATEADD(d, 1, @TheDate)
set     @MyString =  'Select * from "ORACLEDB"."' + @TheTable + '"
                     WHERE "EDITTIME" > to_date(''' +         convert(varchar(30),@TheDate,120) + ''', ''YYYY-MM-DD HH24:MI:SS'')
                     AND "EDITTIME" < to_date(''' +         convert(varchar(30),@TheDateAfter,120) + ''', ''YYYY-MM-DD HH24:MI:SS'')'
set     @MyString =  N'select * from   openquery  (LINKEDSERVER
                                              ,    ''' + REPLACE(@MyString, '''',         '''''') + '''
                                              )'

Exec(@MyString)

Return the results in a number of ways, including a stored procedure which takes my parameters:
```
sp_get_Deltas @TheDate = '1/2/03', @TheTable = 'Table'
```

But I have thus been unable to insert those results into a table. I think that if I can have the sproc return as a table, but the solution I found turns the query into a string and putting those results into a table have only resulted in errors.

The other thing I tried was to put the "Insert Into" portion in with the string, but that throws errors about "labels already being used", or that the Oracle provider doesn't have access to write to my SQL tables. Here was my attempt.

    Declare @MyString    varchar(max)
    ,       @TheDateAfter datetime

    set     @TheDateAfter = DATEADD(d, 1, @TheDate)
    set     @MyString =  'Insert Into [HO-RS1].[DELTAS].[dbo].[' + @TheTable +         '] 
                         Select * from "ORACLEDB"."' + @TheTable + '"
                         WHERE "EDITTIME" > to_date(''' +         convert(varchar(30),@TheDate,120) + ''', ''YYYY-MM-DD HH24:MI:SS'')
                         AND "EDITTIME" < to_date(''' +         convert(varchar(30),@TheDateAfter,120) + ''', ''YYYY-MM-DD HH24:MI:SS'')'
    set     @MyString =  N'select * from   openquery  (LINKEDSERVER
                                                  ,    ''' + REPLACE(@MyString, '''',         '''''') + '''
                                                  )'

    Exec(@MyString)

I tried some other things that I didn't think would work, just to rule them out:

    Insert Into DELTAS.TABLE
    Select * From sp_get_Deltas @TheDate = '1/2/03', 'TheTable'

... and various ludicrous stuff such as that.

Can somebody show me how I can apply the results of the first query into an "Insert Into DELTAS.TABLE ....." statement?

I know I'm close, but I've been wandering in circles for a little while now and I feel like I'm stepping on my tail.

Thanks!

The `INSERT INTO...` should work for you, if you can get the syntax right. Print out the @MyString statement, add that and the error to your post. — Andrew, Aug 01 '14 at 02:55
`Insert Into [HO-RS1].[DELTAS].[dbo]` won't work because that table is on the SQL Server, and this is executed on the Oracle server. — Nick.Mc, Aug 01 '14 at 03:03

score 0 · Accepted Answer · answered Aug 01 '14 at 02:59

0

You're almost there. This works for normal stored procedures. Lets see if it works for SP's that use mindbending OPENQUERY

Insert Into DELTAS.TABLE
EXEC sp_get_Deltas @TheDate = '1/2/03', 'TheTable'

Of course the column list from the EXEC must match the target table.

If you get errors please post them.

answered Aug 01 '14 at 02:59

Nick.Mc

18,304
6
61
91

That works! And now that you offer the solution, I can see why it makes sense. Thanks! – n8. Aug 01 '14 at 03:28
... I believe the reason it was erroring out was that the "nesting" (?) was hiding a "Select * Select *", which would obviously cause issues. – n8. Aug 01 '14 at 07:08
Well.. `select * from storedprocedure` is never valid in any circumstances. It _is_ valid if it was a table valued function though. – Nick.Mc Aug 02 '14 at 04:19

Stored Procedure to Insert the result of a parameterized OPENQEURY into a local table

1 Answers1