SQL SELECT * FROM OPENROWSET with Variable

_{Friday, May 17, 2013 11:12 AM}



I am trying to pass a variable into a SELECT statement in OPENROWSET but I keep getting an error `DECLARE @dDateTIME DATE

SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate)

INSERT INTO dbo.tblSales SELECT * FROM OPENROWSET('MSDASQL', 'dsn=mydsn;uid=myid;pwd=mypwd;', 'SELECT ID, TranDate, ProductID, CostValue, SalesValue, QtySold, FROM tblSales WHERE TranDate='' + @dDateTIME + ''')`

_{Friday, May 17, 2013 11:26 AM ✅Answered | 1 vote}

Try the below:

DECLARE @dDateTIME DATESET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate)INSERT INTO dbo.tblSales SELECT * FROM OPENROWSET('MSDASQL', 'dsn=mydsn;uid=myid;pwd=mypwd;','SELECT ID, TranDate, ProductID, CostValue, SalesValue, QtySold FROM tblSales'  ) WHERE TranDate=@dDateTIME 

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

_{Friday, May 17, 2013 11:40 AM ✅Answered}

Set up a linked server. Then you can do:

INSERT tblSales
   EXEC ('SELECT ID, TranDate, ProductID, CostValue, SalesValue, QtySold, FROM tblSales WHERE TranDate= ?', @dDateTune) AT LINKEDSERVER

If you insist on doing this with OPENROWSET, you will be lost in a quagmire of nested quotes.

The solution suggested by Latheesh avoids the problem, but the entire remote table is dragged over to the local server. Not very efficient.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Friday, May 17, 2013 11:22 AM}



SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate WHERE <SOME CONDITION>)

Hi try to put where condition:

SET @DDATETIME=(SELECT SALESDATE FROM DBO.SALESDATE WHERE <SOME CONDITION>)

That Means sub query should return only one Record.

bala krishna

_{Friday, May 17, 2013 12:59 PM}

Hi,

Try something like this..

DECLARE @Query VARCHAR(MAX)SET @Query =  'INSERT INTO dbo.tblSales SELECT * FROM OPENROWSET(''MSDASQL'', ''dsn=mydsn;uid=myid;pwd=mypwd;'', ''SELECT ID, TranDate, ProductID, CostValue, SalesValue, QtySold, FROM tblSales WHERE CONVERT(VARCHAR,TranDate,112) = ''' + CONVERT(VARCHAR,@dDateTIME,112) + ''''')'

        EXEC (@Query)  

Regards,

Brindha.

_{Saturday, May 18, 2013 1:24 PM}

Try without using any variable,

INSERT INTO dbo.tblSales SELECT * FROM OPENROWSET('MSDASQL', 'dsn=mydsn;uid=myid;pwd=mypwd;', 'SELECT ID, TranDate, ProductID, CostValue, SalesValue, QtySold, FROM tblSales WHERE TranDate IN (SELECT SalesDate FROM dbo.SalesDate)')

Regards, RSingh