December 2, 2011 at 6:39 am
I'm dynamically generating an OPEN Query Statement (DB2) and generating the Account_Numbers from a SQL Server Table that I loaded via SSIS.
I'm placing the Account Numbers inside the OPEN QUERY because when I attempt to JOIN the DB2 Table to a very small SQL Server Table I have to kill it because it does not return a result (after 20 minutes)
The following SELECT INTO works great but I would prefer an INSERT INTO which fails.
SELECT * INTO WestwoodReport FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM PMSPYP00 WHERE YP_EFFECTIVE_DATE >=1111001 AND YP_Account_No IN (7003303,7003050,7003055,7003013,7002928,8062604,7003306,7002945,7003019,7003006,7003014,7003018,7003307,7002936,8062091,7002897,7003061,8062736,7003064,8062946,7003012,7003057,7003029,7030003,7003314,7003315,7003020,7003017,7003311,7003056,7003329,7002999,7003062,7003024,7003319,7002934,7003063,7002992,7003078,7003323,7003309,7002898,7030010,7002998,2993-01,7003304,7003074,7003317,7003068,7003053,7003067,7003070,7003069,7003073,7003341,7003071,7003320,7003072,7003082,7003088,8062686,7003022,7003065,7002941,7003332,7003084,7030015,7003338,7003302,7003033,7003075,7003015,7003333,7003089,7003085,7003059,7003023,7003349,7003034,7003330,7030014,7003318,7003037,7030017,7003313,7003340,7002946,7003038,7003086,7003087,7003098,7003301,8063443,7003026,1014382,7030013,7030012,7003021,7030019,7030011,7003092,7003030,7003058,7003040,7003031,7003052,7003091,7003308,7003334,7003076,7003079,7003339,7003093,7003353,7003337,1014483,7003066,7003322,7003035,7003095,7030016,7003042,7003094,7003041,7003028,7003097,7003032,7003312,7003036,7003351,7003355,7003016,7003025,7003356,7003356)') AS DB2
If I change "SELECT * INTO WestwoodReport FROM OPENQUERY(LSCTLRDAT1"
To
INSERT * INTO WestwoodReport FROM OPENQUERY(LSCTLRDAT1"
I get the following Error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 2, 2011 at 6:47 am
INSERT INTO WestwoodReport SELECT * FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM PMSPYP00 WHERE YP_EFFECTIVE_DATE >=1111001 AND YP_Account_No IN (7003303,7003050,7003055,7003013,7002928,8062604,7003306,7002945,7003019,7003006,7003014,7003018,7003307,7002936,8062091,7002897,7003061,8062736,7003064,8062946,7003012,7003057,7003029,7030003,7003314,7003315,7003020,7003017,7003311,7003056,7003329,7002999,7003062,7003024,7003319,7002934,7003063,7002992,7003078,7003323,7003309,7002898,7030010,7002998,2993-01,7003304,7003074,7003317,7003068,7003053,7003067,7003070,7003069,7003073,7003341,7003071,7003320,7003072,7003082,7003088,8062686,7003022,7003065,7002941,7003332,7003084,7030015,7003338,7003302,7003033,7003075,7003015,7003333,7003089,7003085,7003059,7003023,7003349,7003034,7003330,7030014,7003318,7003037,7030017,7003313,7003340,7002946,7003038,7003086,7003087,7003098,7003301,8063443,7003026,1014382,7030013,7030012,7003021,7030019,7030011,7003092,7003030,7003058,7003040,7003031,7003052,7003091,7003308,7003334,7003076,7003079,7003339,7003093,7003353,7003337,1014483,7003066,7003322,7003035,7003095,7030016,7003042,7003094,7003041,7003028,7003097,7003032,7003312,7003036,7003351,7003355,7003016,7003025,7003356,7003356)') AS DB2
-- Gianluca Sartori
December 2, 2011 at 7:03 am
@Gianluca Thanks!:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 5, 2011 at 2:59 am
You're welcome!
-- Gianluca Sartori
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply