Click here to Skip to main content
15,031,687 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have two tables and insert data in both tables at same time but one table contains autogenerated numbers and autogenerated numbers insert into 2nd table.

Thanks
Posted
Updated 9-Dec-10 4:33am
v4
Comments
Abdul Quader Mamun 9-Dec-10 9:36am
   
What your need code or Idea?
Sandeep Mewara 9-Dec-10 9:37am
   
so whats the issue?
wizardzz 9-Dec-10 10:26am
   
You might want to add SQL to the tags?
Toli Cuturicu 9-Dec-10 10:32am
   
This is a statement, not a question.

Assuming you have a stored procedure that you are calling,
and that you are using TSQl:

DECLARE @AutoGeneratedId int

INSERT INTO table1
(a,b,c)
values
(1,2,3)

SET @AutoGeneratedId = SCOPE_IDENTITY();


INSERT INTO table2
(fkFromTable1,q,w)
(@AutoGeneratedId, 4,5)
   
Comments
OriginalGriff 9-Dec-10 10:29am
   
Thanks for your comments, but the performance hit isn't as bad as you might think:
"There are many urban legends surrounding the GUID usage that condemn them based on their size (16 bytes) compared to an int (4 bytes) and promise horrible performance doom if they are used. This is slightly exaggerated. A key of size 16 can be a very peformant key still, on a properly designed data model. While is true that being 4 times as big as a int results in more a lower density non-leaf pages in indexes, this is not a real concern for the vast majority of tables. The b-tree structure is a naturally well balanced tree and the dept of tree traversal is seldom an issue, so seeking a value based on GUID key as opposed to a INT key is similar in performance."

I agree that Guids are visually horrible, but mostly I don't view my ids directly anyway.

If I need to absolutely know the entry order, I will provide a DateTime stamp for that purpose!

Since replication uses an internal Guid for the record if you don't supply a RowGuid column, it seems silly to have two differing unique identifactions for any single row!
If you are dealing with data which has a common id, then automatically generated numbers are not necessarily the way to go. You would have to insert the item in one table, then select it back to find out the number assigned, then add it to the second table.
Look at using a Guid instead - since you assign it rather than the database, it works better in such circumstances. Additionally, it makes replication easier if you need that later.
   
Comments
Magnus Gudmundsson 9-Dec-10 10:18am
   
I disagree :)
Guids are pain to the eye.
Usually you want to put an index on your Identity column, and because Guids are big, (4 times the size of a columntype of int) The indexing becomes slower = every search you do on that table becomes slower.
+ when you use an autonumber it is fairly easy to see in which order the rows arrived to the database (and that is something you often want to know.)

Replication is not a problem. you can turn the autogeneration of the identity column off:
http://ryanfarley.com/blog/archive/2004/12/19/1313.aspx
Magnus Gudmundsson 9-Dec-10 10:41am
   
Thanks for the answer Griff. I did not know about the internal Guid used for replication, thats interesting. But still, it seems a bit off to add a DateTime stamp column to a table when all you need is a puny int as Identity ;) (granted if you make the guid a clustered index, you would not need the datetime stamp.) I remain a Guid skeptic, but would like to gruff on about this matter :o)
If I have the time and energy I will try to write a CP article named The Case against Guid, and will leave no stone unturned.
Cheers
Insert into 1 table.
Then
SELECT MAX(ID) FROM TABLENAME

Store this ID in variable & then simply insert it to another table.

Its the best and more accurate code ever seen.
Cheers...
   
Comments
OriginalGriff 9-Dec-10 10:52am
   
Bad idea - What happens in a multiuser or distributed system?
sandipapatel 9-Dec-10 11:41am
   
Bad idea :)) :(
How perfect it works n being maintained in ESB,JSF,EJB.... n more n more Azure,Nuke and even Microsoft lync tools.
I'm not really into C#, but try to look up some information on appending text.
(.AppendText)
Practically, taking the value of the first table, and appending it into the second one.
http://dotnetperls.com/textbox-appendtext[^]

Scroll down, there's a two table example too.
   
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900