Re: Problems w/ SQL Insert with a static parm

littldo_at_gmail.com wrote:

> Hi,
>
> 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

Is the double comma in the column list a syntax error? ie. the double-comma between c and batch_key? Received on Sun Apr 01 2007 - 01:08:17 CEST