Search code examples
sqlstored-procedureslinked-serveropenquerydelta

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

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!

Solution

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