asked on

Take dynamic sql result with variable and place into a table on sql server

Take dynamic sql result and place into a table on sql server

I have this Dynamic sql query working fine.

DECLARE @StartDate AS VARCHAR(10),
      @SQL NVARCHAR(MAX);

SET @StartDate = '01-JAN-19';

SET @SQL = 'SELECT  * FROM OPENQUERY(XREF_PROD, ''SELECT leavetype, leavereason FROM XREF.XREF_CALENDER  WHERE createdon >= ''''' + @StartDate + ''''''')';

   EXEC sp_executesql @SQL;

Open in new window

I need to take this result now and INSERT it into table on sql server.
Database name = Work_Flow
Table Name= Customer_Calendar
Fields = leavetype, leavereason

I have looked at kinds of examples on the internet..but gets confusing because most of the examples use a temp table.

Thanks
fordraiders

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ste5an

E.g.

DECLARE @StartDate AS VARCHAR(10) = '2019-01-19';
DECLARE @Statement NVARCHAR(MAX) = N'
    SELECT Q.leavetype, Q.leavereason
    FROM OPENQUERY(
        XREF_PROD,
        ''SELECT leavetype, leavereason FROM XREF.XREF_CALENDER WHERE createdon >= {D}''
    ) Q;
';

SET @Statement = REPLACE(@Statement, '{D}', '''''' + @StartDate + '''''');

INSERT INTO Work_Flow.Customer_Calendar ( leavetype ,
                                          leavereason )
EXECUTE sys.sp_executesql @Statement;

Open in new window

Use unambiguous date literals if possible.

Fordraiders

ASKER

Thanks to both answers..

I will be asking another question based on this one in regards to having 2 parameters..

@Startdate @ Enddate

fordraiders

Andrei Fomitchev

DECLARE @StartDate AS VARCHAR(10),
@SQL NVARCHAR(MAX);

SET @StartDate = '01-JAN-19';

SET @SQL = ' INSERT INTO Work_Flow.dbo.Customer_Calendar
SELECT * FROM OPENQUERY(XREF_PROD, ''SELECT leavetype, leavereason FROM XREF.XREF_CALENDER WHERE createdon >= ''''' + @StartDate + ''''''')';

EXEC sp_executesql @SQL;

Andrei Fomitchev

INSERT INTO Work_Flow.dbo.Customer_Calendar (leavetype, leavereason) ...