inserting data using temp tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

inserting data using temp tables

Author

Topic

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2009-04-24 : 18:06:07

I have a table which I would like to merge the values in. I have created a temp table and extracted the merged data. I would then like to insert the data from the temp table into the original table. This is my query :

SELECT itemnmbr as itemnmbr1,
lotnumbr as lotnmbr1,
min(daterecd) as receiptdate1,
sum(qtyrecvd) as qtyrecvd1,
IDENTITY(smallint, 1, 1) as dex_row_id1,
locncode as locncode1,
sum(qtysold) as qtysold1
into #lot
FROM iv00300
group by itemnmbr,lotnumbr, locncode

delete iv00300

insert into iv00300 (itemnmbr, lotnumbr, receiptdate,qtyrecvd,dex_row_id,locncode,qtysold) values (itemnmbr1, lotnumbr1, receiptdate1,qtyrecvd1,dex_row_id1,locncode1,qtysold1)
from #lot

drop table #lot

When I run it, I get the message :

"Server: Msg 128, Level 15, State 1, Line 25
The name 'itemnmbr1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

How do I fix this ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-24 : 18:09:35

insert into iv00300 (itemnmbr, lotnumbr, receiptdate,qtyrecvd,dex_row_id,locncode,qtysold)
select itemnmbr1, lotnumbr1, receiptdate1,qtyrecvd1,dex_row_id1,locncode1,qtysold1
from #lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources