Insert Into SQL command with two related tables

Question

i have 3 tables in my site.

Users
Threads
Comments

I connected the comments primary key to Threads comments field. I want to use insert into command while feeding comments to specific threads.

How do i write the command?!?

is it like this:

 string myCommand="INSERT INTO [Threads].[Comments] VALUES(....";

Will the messages be inserted into a specific thread? What if i want to insert data to both simultaneously.. e.g. a headline to a thread and a date to the comment...can i combine two commands into one?

Two commands. First the thread, then the comment once you have the key for the thread. — Chris Farmer
– Chris Farmer, Commented May 12, 2011 at 16:29

Coding Flow · Accepted Answer · 2011-05-12 16:39:58Z

1

You have set up your foreign key back to front, if you have a foreign key in the threads table that links to the primary key of the comments table then one comment can relate to many threads but a thread can only have one comment.

edited May 12, 2011 at 16:39

answered May 12, 2011 at 16:33

Coding Flow

21.9k6 gold badges68 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dustin Laine · Accepted Answer · 2011-05-12 16:31:49Z

1

You have to create two separate INSERT statements. You can wrap them in a transaction to ensure that neither are committed unless they are both successful.

answered May 12, 2011 at 16:31

Dustin Laine

38.7k10 gold badges90 silver badges125 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:19:58Z

1

You still need two INSERT statements, but it sounds like you want to get the IDENTITY from the first insert and use it in the second, in which case, you might want to look into OUTPUT or OUTPUT INTO: http://msdn.microsoft.com/en-us/library/ms177564.aspx

(my answer to the same question previously asked: SQL Server: Is it possible to insert into two tables at the same time?)

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered May 12, 2011 at 16:34

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Comments

Tom Pickles · Accepted Answer · 2011-05-12 16:43:56Z

0

I'd create a stored procedure and put both insert's into one transaction within the SP. You can use @@SCOPE_IDENTITY to get the ID from the insert into threads and use that in your insert into the comments table:

INSERT INTO [Threads] (...

INSERT INTO [Comments]
SELECT 
     @@SCOPE_IDENTITY,
     OtherValues ...

You could use a transaction as previously stated to make it more robust. Call your SP from your C# code using a SQL command.

answered May 12, 2011 at 16:43

Tom Pickles

90011 silver badges28 bronze badges

Comments

mellamokb · Accepted Answer · 2011-05-12 17:09:29Z

0

Use two commands to insert into Threads and Comments. First insert into Threads and grab the id:

string myCommand = "INSERT INTO [Threads] (...";
// execute
string myCommand = "SELECT SCOPE_IDENTITY()";
// execute - put in thread ID

Then insert into comments using the thread ID

string myCommand = "INSERT INTO [Comments] (" + ThreadID + "...";

There is no real value or point in somehow accomplishing this in a single INSERT Command.

Edit Changed @@IDENTITY to SCOPE_IDENTITY() per comment suggestions. Thanks!

edited May 12, 2011 at 17:09

answered May 12, 2011 at 16:30

mellamokb

56.9k12 gold badges111 silver badges138 bronze badges

6 Comments

SQLMason Over a year ago

Please use SCOPE_IDENTITY() instead of @@IDENTITY

SQLMason Over a year ago

Better to put it into a PROC and use SCOPE_IDENTITY

Alexander Kahoun Over a year ago

Use SCOPE_IDENTITY() as it limits the scope to the ID you just created, no the last auto-generated ID regardless of scope which is what @@IDENTITY does

Coding Flow Over a year ago

Unless there is a trigger on the table @@IDENTITY and SCOPE_IDENTITY() will return the same value. @@IDENTITY does not return identities created by other sessions. See msdn.microsoft.com/en-us/library/ms187342.aspx to quote "@@IDENTITY contains the last identity value that is generated by the statement"

SQLMason Over a year ago

It will not return the same value if there are 'large' transactions involved. It is safer to use SCOPE_IDENTITY() as a practice and not worry about when to use @@IDENTITY and not.

|

Collectives™ on Stack Overflow

Insert Into SQL command with two related tables

5 Answers 5

Comments

Comments

Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related