Re: Problems w/ SQL Insert with a static parm

On Mar 31, 6:08 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> litt..._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
> > litt..._at_yahoo.com
>
> Is the double comma in the column list a syntax error? ie. the
> double-comma between c and batch_key?- Hide quoted text -
>
> - Show quoted text -

sorry typo. Received on Sun Apr 01 2007 - 16:35:58 CEST