We help IT Professionals succeed at work.

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

139 Views
Last Modified: 2019-03-16
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
Comment
Watch Question

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
Thanks to both answers..

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

@Startdate  @ Enddate

fordraiders
CERTIFIED EXPERT

Commented:
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;
CERTIFIED EXPERT

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions