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?

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.

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.

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

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.

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!