Multiple insert based on query

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

Transact-SQL (2000)

Multiple insert based on query

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2005-02-15 : 03:54:24

I have a table Schedule
Fields are:
Definition_ID int
Schedule int,
Item_ID int,
OrderNo int
Other fields

I use negative values in Definition_ID to store template data
e.g. Template for Definition 1 (Schedule is of no value because it's a template)
Select * from Schedule WHERE Definition_ID=-1
-1,0,1,1 ...
-1,0,2,2 ...
-1,0,3,3 ...

Now I need to poplulate the same table for a spesific Schedule based on the Definition Template. Let's say Schedule 1 using template Definition 1.
Thus I need multiple insert to achieve the follwing result
1,1,1,1 ...
1,1,2,2 ...
1,1,3,3 ...

I need to insert the (Select * from Schedule WHERE Definition_ID=-(given parameter Definition_ID)).
The new Definition_ID must change to 1 (given parameter Definition_ID) and the Schedule must change to 1 (given parameter Schedule)

I'm a bit lost can one do this in one stored proc?

I can do the above using

Insert Schedule
Select
1,
1,
Item_ID,
OrderNo,
....
from Schedule
WHERE Definition_ID=-1

Now the tricky part is, if there is already entries for that spesific schedule item then OrderNo must be automatically incremented from where the previous OrderNo stopped for that spesific schedule.
Is it still possible to do this in one stored proc?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources