Problems w/ SQL Insert with a static parm

[Quoted] [Quoted] I need to insert a fixed parameter along with data from a query as part of an insert statement.
The statement looks like this.

insert into tab (a,b,c,,batch_key)
select a, b, c
, ? -- batch_key parm
from x
where x.date= current_date

The batch_key is the parm and would be the same for each inserted record in this batch. next batch it would be a different value, and it isn't in the source. It's basically an audit value that we can use to trace the batch that loaded the record.

I'm using MS SQL 2000 and DTS to do the load. In DTS I can supply a parameter to the SQL statement such that the GUI allows me to associate by position

So I can have
parm1 = 'source_5' -- datasource
parm2 = 'doug' -- operator Id
parm3 ='231' -- batch_key

and in the SQL window I can use
-- This works

Insert into batch_log(datasource, operater_id, batch_key) values(?,?,?) --- parm 1 would be associated with 1st ? and use the value I assigned at run time.

• This doesn't insert into tab (a,b,c,,batch_key) select a, b, c , ? -- batch_key parm from x where x.date= current_date-1

I get a syntax error.
I understand enough to know that parameters are being supplied via odbc at runtime not prep-time and that my syntax error is coming from the select ...,? which doesn't have enough info to complete the prep.

I tried other approaches
1 .local t-sql variable - still no way to set the value from a parm 2. create temp table and insert the value, then do a x-join in the 2nd select. - works but looked awful.
3. user defined scalar & table functions can anybody recommend any solutions?

Thanks
doug little
littldo_at_yahoo.com Received on Sun Apr 01 2007 - 00:48:10 CEST