SQL 2000: INSERT within a cursor alternative? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

SQL 2000: INSERT within a cursor alternative?

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2010-05-14 : 16:21:02

I need to review all records within an "OrderDetails" table and if the order detail record contains a particular item (we'll say item#999 in the example below), then I need to INSERT a new record into the order details table.

Basically, it's a promotion where if a user orders a particular item, they get a free bonus item. For technical reasons, this has to be done within SQL, not within the application.

So here's a sample table:

OrderDetailsID | OrderID | ProductID
--------------------------------------------------------
1 | 1111 | 999
2 | 2222 | 687
3 | 3333 | 110
4 | 4444 | 999

So in the above example, there are 2 records with productID 999, so I would need to insert two new records into this table for those orders containing the productID of the bonus item (productid 1010):
OrderDetailsID | OrderID | ProductID
--------------------------------------------------------
5 | 1111 | 1010
6 | 4444 | 1010

Any idea how I could do this? Preferably without a cursor?

Thanks.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-05-14 : 17:00:04

So in the above example, there are 2 records with productID 999, so I would need to insert two new records into this table for those orders containing the productID of the bonus item (productid 1010):
OrderDetailsID | OrderID | ProductID
--------------------------------------------------------
5 | 1111 | 1010
6 | 4444 | 1010

Where is the ProductID value (1010) comming from?

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-05-14 : 17:20:33

I am not sure i understand your requirement correctly ... but just try this.

insert into @temp (oid, pid)
(select oid, @bonusItem from @temp where pid = @specialItem)

Here is my test ... and result

declare @temp table(odid int PRIMARY KEY IDENTITY, oid int, pid int)
declare @specialItem int, @bonusItem int
set @specialItem = 999
set @bonusItem = 1010

insert into @temp values(1111 , 999)
insert into @temp values(2222 , 678)
insert into @temp values(3333 , 110)
insert into @temp values(4444 , 999)

select * from @temp
insert into @temp (oid, pid)
(select oid, @bonusItem from @temp where pid = @specialItem)
select * from @temp

--- original --------
odid oid pid
----------- ----------- -----------
1 1111 999
2 2222 678
3 3333 110
4 4444 999

--- after insert ----------
odid oid pid
----------- ----------- -----------
1 1111 999
2 2222 678
3 3333 110
4 4444 999
5 1111 1010
6 4444 1010

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-15 : 03:22:05

[code]
INSERT INTO OrderDetails
(OrderID,ProductID )
SELECT OrderID,1010
FROM OrderDetails
WHERE ProductID=999
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2010-05-17 : 10:46:41

Thanks for all of your help on this. I was able to use the queries you provided. Both versions worked great! :)

Btw Namman, just to clarify from your original question, the ProductID was being pulled from my Products table however this part wasn't an issue as I was already able to successfully retrieve the necessary productID, so I didn't specify that part within my original question.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources