Openquery Insert & Exec
Posted on 2005-04-01
Have a current script which does the following on sql Server 2003 download from Oracle:
1) Create the table LTic1 with the ticketNumber being a 10 digit string with the first four digits representing the year
2) Inserts data using open query which joins two tables and only downloads the tickets with the year of ''2005'' or ''2004''
This works. What I would like to do is to make the script automatically determine the years to be downloaded. So created and populated two script variables: @CurrentYr and @PastYr. They contain the proper information.
It seems one must use Exec in order to use a script variable, so @sql VARCHAR(8000) was created to hold the entire query.
1) In the script, the table is created then the insert into new table using openquery is loaded into the @sql variable. Next, exec @sql. Is this allowed?
2) Have tried a variety of ways to set @sql with the very big query but am receiving the error:
Server: Msg 203, Level 16, State 2, Line 14
The name 'INSERT INTO LTic1(ST_TICKET_NUMBER ) SELECT * FROM OPENQUERY(ORAVGER,'SELECT ST_TICKET_NUMBER FROM SUMMARY_TICKET1 WHERE (substr(ST_TICKET_NUMBER,1,4) = ''2005'' or substr(ST_TICKET_NUMBER,1,4) = ''2004'') AND ST_ETE_WORK_CENTER = ''CCC'' ORDER BY t1.ST_TICKET_NUMBER') ' is not a valid identifier.
This query above reflects a simple version of the true query but even the simple version isn't working...