Insert question - SQL Server Forums

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)

Insert question

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-09 : 14:41:19

Greetings!

I have 3 tables. Work Order, Job and WorkOrderJob that is a table that is a middle table for Work Order and Job.

Work Order Table has all Sales Work Orders entered, it is a replicated data from an old mainframe app to our SQL server, it has no IDENTITY KEY so I use WorkOrderNumber as the key field.

Multiple work orders can be assigned to one Job therefore I haev a middle table WorkOrderJob which has
WorkOrderJobID
WorkOrderNumber
JobID

Job
JobID
JobNumber

How do I in one INSERT statement insert into Job and WorkOrderJob. I am trying to do this in one Store procedure to which I pass concatenated WorkOrderNumber(s)

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 14:46:25

How do you determine which is the related Work Order for each of job created?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-09 : 14:53:25

that is my dilemna...I can't find that out until the job is created. And that will be difficult for the users to create a job and then associate it to a Work Order (users are not yet computer savvy) so we want to create these for them.
In the front end we have a list view. The insert is called for each item in listView. if somehow right after creating the job it could grab the jobID and the WoNumber and create the WorkOrderJob is what I was hoping.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-09 : 14:58:17

What if I used @@IDENTITY in that sproc and then went ahead and INSERTed into the WorkOrderJob ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 14:58:35

quote:
Originally posted by yosiasz

that is my dilemna...I can't find that out until the job is created. And that will be difficult for the users to create a job and then associate it to a Work Order (users are not yet computer savvy) so we want to create these for them.
In the front end we have a list view. The insert is called for each item in listView. if somehow right after creating the job it could grab the jobID and the WoNumber and create the WorkOrderJob is what I was hoping.

so you will pass the work order as input,it will create a job with passed jobnumber and assign it against passed on workorder? if yes you can do like this

INSERT INTO Job (JobNumber)
SELECT @JobNumber

SELECT @JobID=SCOPE_IDENTITY()

INSERT INTO WorkOrderJob (WorkOrderNumber,JobID)
SELECT @WorkOrderNo,@JobID

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-09 : 15:06:15

nice!!! Thanks so much. Now what is the difference between SCOPE_INDETITY() and @@IDENTITY? which on is best to use?

Thanks again! visakh16

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-09 : 15:10:44

SQL Server Books Online is always a great resource to check when you have questions about SQL functions. But here's the article from SQLTeam: http://www.sqlteam.com/article/alternatives-to-identity-in-sql-server-2000

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 15:10:54

quote:
Originally posted by yosiasz

nice!!! Thanks so much. Now what is the difference between SCOPE_INDETITY() and @@IDENTITY? which on is best to use?

Thanks again! visakh16

SCOPE_IDENTITY returns the last id value generated in current scope while @@IDENTITY returns the id value generated in you curent connection. They may not be always same. Suppose if you have a trigger on your table which does insertion on some other table then @@IDENTITY returns only the last id value which is not one generated for your table. SCOPE_IDENTITY correctly returns the id for your table as its in the current scope. You also use IDENT_CURRENT('YourTable') to get tables last generated id value.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-09 : 15:13:23

oh yes of course! it would be dangerous to use @@Identity then I see! because other inserts will be going on and you do not want to insert identity of other tables!

Wow thatks so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 00:20:55

quote:
Originally posted by yosiasz

oh yes of course! it would be dangerous to use @@Identity then I see! because other inserts will be going on and you do not want to insert identity of other tables!

Wow thatks so much!

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources