0

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?

Chris Farmer
  • 24,974
  • 34
  • 121
  • 164
Dmitry Makovetskiyd
  • 6,942
  • 32
  • 100
  • 160

5 Answers5

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.

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
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.

Ben Robinson
  • 21,601
  • 5
  • 62
  • 79
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?)

Community
  • 1
  • 1
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
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!

mellamokb
  • 56,094
  • 12
  • 110
  • 136
  • 2
    Please use SCOPE_IDENTITY() instead of @@IDENTITY – SQLMason May 12 '11 at 16:33
  • Better to put it into a PROC and use SCOPE_IDENTITY – SQLMason May 12 '11 at 16:34
  • 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 – Alexander Kahoun May 12 '11 at 16:36
  • 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 http://msdn.microsoft.com/en-us/library/ms187342.aspx to quote "@@IDENTITY contains the last identity value that is generated by the statement" – Ben Robinson May 12 '11 at 16:37
  • 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. – SQLMason May 12 '11 at 16:39
  • Thank you for your answer. so what i need to do first, is obtain a thread id by SCOPE_IDENTITY().. (in practice i need to save the threads title), then i use that id in my Insert statement of each comment.. ..when i use the Select statement, how do i retrieve all the comments that relate to the Thread ID (Thread title)? – Dmitry Makovetskiyd May 13 '11 at 05:32
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.

Tom Pickles
  • 890
  • 10
  • 26