CODEWITHSUNDEEP
c#asp.netsql
Dmitry Makovetskiyd
Dmitry Makovetskiyd

Reputation: 7053

Insert Into SQL command with two related tables

i have 3 tables in my site.

  1. Users
  2. Threads
  3. 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?

Upvotes: 0

Views: 1992

Answers (5)

mellamokb
mellamokb

Reputation: 56779

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!

Upvotes: 0

Tom Pickles
Tom Pickles

Reputation: 900

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.

Upvotes: 0

Coding Flow
Coding Flow

Reputation: 21881

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.

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89741

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?)

Upvotes: 1

Dustin Laine
Dustin Laine

Reputation: 38543

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.

Upvotes: 1

Related Questions