Adding Idenity Field to Temp Table

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)

Adding Idenity Field to Temp Table

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-15 : 17:28:15

I have a SP that creates a backorder but it must have a entry number added starting at one.

Declare @CCCorderID Int
Select @CCCorderID = 3656
Declare @newOrderID Int
Declare @CustID Varchar(10)
Select @newOrderID = cccorderId,@CustID = CustID
FROM dbo.StockOrderHead
WHERE (OrigOrderID = @CCCorderID)

SELECT @NeworderID AS CCCOrderID , Item_Number, QuanBackordered, BasePrice, VolPrice, CustPrice, ORPrice
Into #StORDetTemp
FROM dbo.StockOrderDet
WHERE (CCCOrderID = @cccOrderID) and (QuanBackordered > 0)

Alter TAble #StORDetTemp add EntryNum int IDENTITY (1, 1) NOT NULL

INSERT INTO dbo.StockOrderDet
(CCCOrderID, Item_Number, ItemQuan, BasePrice, VolPrice, CustPrice, ORPrice,EntryNum)
SELECT CCCOrderID, Item_Number, QuanBackordered, BasePrice, VolPrice, CustPrice, ORPrice,EntryNum
FROM #StORDetTemp

Drop Table #StORDetTemp

If I remove the Insert and Put Select * I get all the fields

3684 86 1000 NULL 11.7400 9.9800 NULL 1

but with the insert I get an error

Invalid column name 'EntryNum'.

Any Ideas??

Jim
Users <> Logic

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-06-15 : 17:32:54

[code]SELECT @NeworderID AS CCCOrderID , Item_Number, QuanBackordered, BasePrice, VolPrice, CustPrice, ORPrice, IDENTITY(INT,1,1) EntryNum
Into #StORDetTemp
FROM dbo.StockOrderDet
WHERE (CCCOrderID = @cccOrderID) and (QuanBackordered > 0)
[/code]

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-15 : 17:46:00

Thanks That did the Trick nicely

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources