Question about SQL insert statement!

Question

I have two tables:

Threads ******* ThreadID UserID TopicsID Date ThreadTitle ThreadParagraph ThreadClosed Topics ****** TopicID Theme Topics Date 

I need to insert two operators and connect between them! This is the first condition:

 string insertCommand = "INSERT INTO Users (UserID,TopicID,Date,ThreadTitle,ThreadParagraph) " + "VALUES ('CONVERT(uniqueidentifier, '" + giveMeGuidID() + "),TopicID,dateTime,questionTitle,subTopic)"; 

and I need to have another expression for the topic table:

 string insertCommand = "INSERT INTO Topics (UserID,TopicID,Date,ThreadTitle,ThreadParagraph) " + "VALUES ('Theme, Topics, Date')"; 

The problem is that I have a connection between TopicID (Thread table) and ThreadID (Thread table). Both are incremental ints, so how do I insert the same both of them so get the same value?

Answer 1

If you are using MS SQL Server, you can get the auto-increment value using @@ Identity.

 string insertCommand = "INSERT INTO Users (UserID,TopicID,Date,ThreadTitle,ThreadParagraph) " + "VALUES ('CONVERT(uniqueidentifier, '" + giveMeGuidID() + "),TopicID,dateTime,questionTitle,subTopic); SELECT @@Identity"; 

Then run this command as ExecuteScalar and get your value

Answer 2

You can save a Transaction using TransactionScope and using SCOPE_IDENTITY() to get the inserted identifier from the first query.

  // Create the TransactionScope using (TransactionScope oTranScope = new TransactionScope()) { Int32 TopicID; // Open a connection using (SqlConnection oCn1 = new SqlConnection(this.sCn1)) { SqlCommand oCmd1 = new SqlCommand("INSERT INTO Users (UserID,TopicID,Date,ThreadTitle,ThreadParagraph) " + "VALUES ('CONVERT(uniqueidentifier, '" + giveMeGuidID() + "),TopicID,dateTime,questionTitle,subTopic); SELECT SCOPE_IDENTITY()";, oCn1); oCmd1.Parameters.Add ... Better to use parameter to save SQL Injection Attack oCn1.Open(); // At this point, the connection is in the transaction scope, // which is a lightweight transaction. TopicID = Convert.ToInt32 oCmd1.ExecuteScaler()); // as you want to get Id oCn1.Close(); } // Open a connection using (SqlConnection oCn2 = new SqlConnection(this.sCn2)) { SqlCommand oCmd2 = new SqlCommand("SQLQuery", oCn2); //use return TopicID from last inserted query oCn2.Open(); // The connection is enlisted in the transaction scope, // which is now promoted to a distributed transaction // controlled by MSDTC oCmd2.ExecuteNonQuery(); oCn2.Close(); } // Tell the transaction scope to commit when ready oTranScope.Consistent = true; // The following bracket completes and disposes the transaction } 

Answer 3

If you are looking for something reliable, you need to use transactions.

See "Transaction Management in SQL Server Stored Procedures" for an introduction.

Also, see Transaction Management (Database Engine) and SQL Server Transaction Isolation Models .

You also need to use the @@ Identity value of the last insert.

Answer 4

Code samples do not correlate well with the rest of the information provided. Without code, your post seems consistent enough, so I tend to think these fragments are simply wrong.

In any case, your idea seems clear. In SQL Server 2005+, you can solve your problem with an INSERT statement like this:

 string insertCommand = "INSERT INTO Topics (Theme, Topics, Date) " + "OUTPUT 'CONVERT(uniqueidentifier, '" + giveMeGuidID() + "'), INSERTED.TopicID, @dateTime, @questionTitle, @subTopic " + "INTO Threads (UserID, TopicID, Date, ThreadTitle, ThreadParagraph) " + "VALUES (@Theme, @Topics, @Date)"; 

Although this is a single statement, it performs two inserts into different tables. The "main" insert is done in the Topics table. Secondary, in Threads , is defined by the OUTPUT...INTO clause. Basically, the OUTPUT clause allows you to reference the inserted data and either return it as a set of rows to the client, or (in combination with INTO ) direct them to an existing table, as you can see here.