I want to insert data in two tables at same time

Question

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 9-Dec-10 3:29am

gosaiarjun

Updated 9-Dec-10 4:33am

Toli Cuturicu

v4

Add a Solution

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.

4 solutions

Solution 5

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...

Posted 9-Dec-10 4:10am

sandipapatel

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.

Solution 1

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.

Posted 9-Dec-10 3:46am

LighthouseCall

Updated 9-Dec-10 3:48am

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Magnus Gudmundsson · Accepted Answer · 2010-12-09T03:48:00

Solution 3

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)

Posted 9-Dec-10 3:48am

Magnus Gudmundsson

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!

OriginalGriff · Accepted Answer · 2010-12-09T03:47:00

Solution 2

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.

Posted 9-Dec-10 3:47am

OriginalGriff

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