Can i create rows in different tables all in one query?

Question

I'm trying to create multiple rows at the same time, each row implements previous rows Primary key. Is there a way i can make all this happen in one query instead of creating multiple query's for doing it, like shown in code sample?

public void CreateMessage(int profileId, String text, int chatId)
    {
        string stmt = "INSERT INTO Activity (profileID, timeStamp) OUTPUT INSERTED.activityID values (" + profileId + ", '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "')";
        SqlDataReader reader = new SqlCommand(stmt, con).ExecuteReader();
        reader.Read();

        stmt = "INSERT INTO Text(activityID, message) OUTPUT INSERTED.textID values(" + Int32.Parse(reader["activityID"].ToString()) + ", (select cast('" + text + "' as varbinary(max))))";
        reader.Close();
        reader = new SqlCommand(stmt, con).ExecuteReader();
        reader.Read();

        stmt = "INSERT INTO Message (textID, chatID) values (" + Int32.Parse(reader["textID"].ToString()) + ", " + chatId + ")";
        reader.Close();
        new SqlCommand(stmt, con).ExecuteNonQuery();
    }

You can use a stored procedure than uses a transaction command. See following posting : https://stackoverflow.com/questions/175066/sql-server-is-it-possible-to-insert-into-two-tables-at-the-same-time — jdweng, Oct 26 '17 at 13:30
You need 3 sql insert queries that have to be written somewhere. if you were using ColdFusion, and you separated your 3 queries by semi colons in a single ColdFusion query, it would probably work. The equivalent here would be to have a single stmt variable with all 3 queries separated by semi colons. If it were me, I'd try it. By the way, query parameters are your friends. — Dan Bracuk, Oct 26 '17 at 13:36
BTW, concatinating arbitrary text into a SQL statement is just asking for a SQL injection attack to happen. [Bobby Tables](https://xkcd.com/327/) is coming for you. — Becuzz, Oct 26 '17 at 13:39

score 0 · Answer 1 · answered Oct 26 '17 at 13:35

SqlDataReader is used to read data in a secuential way. You retrieve data from a select or a stored procedure call and read a record at a time.

If you want to execute a block of commands you could use a transaction in your code, check SQlConnection.BeginTransaction, or delegate that task to the database using a stored procedure (which internally should use a transaction).

score 0 · Answer 2 · answered Oct 26 '17 at 13:37

Normally you could just put a semicolon between statements and send multiple commands in as one string. However, you're using the results of one statement in the next. This means that you MUST run each statement one at a time and read the results before executing the next.

You can do that in c# or in an SQL stored procedure. In either case you can use transactions to ensure all get written at once.

score 0 · Answer 3 · answered Oct 26 '17 at 14:11

It is possible the only thing you had to do is enter a ; between the different insert lines, see example below:

public void CreateMessage(int profileId, String text, int chatId)
    {
        string stmt = "INSERT INTO Activity (profileID, timeStamp) OUTPUT INSERTED.activityID values (" + profileId + ", '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "')";
        SqlDataReader reader = new SqlCommand(stmt, con).ExecuteReader();
        reader.Read();

        stmt = "INSERT INTO Text(activityID, message) OUTPUT INSERTED.textID values(" + Int32.Parse(reader["activityID"].ToString()) + ", (select cast('" + text + "' as varbinary(max))))";
        reader.Close();
        reader = new SqlCommand(stmt, con).ExecuteReader();
        reader.Read();

        stmt = "INSERT INTO Message (textID, chatID) values (" + Int32.Parse(reader["textID"].ToString()) + ", " + chatId + ")";
        reader.Close();
        new SqlCommand(stmt, con).ExecuteNonQuery();
    }

will become

    public void CreateMessage(int profileId, String text, int chatId)
        {
            string stmt = "INSERT INTO Activity (profileID, timeStamp) OUTPUT INSERTED.activityID values (" + profileId + ", '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "');
INSERT INTO Message (textID, chatID) values (" + Int32.Parse(reader["textID"].ToString()) + ", " + chatId + ");
INSERT INTO Text(activityID, message) OUTPUT INSERTED.textID values(" + Int32.Parse(reader["activityID"].ToString()) + ", (select cast('" + text + "' as varbinary(max))))";
            SqlDataReader reader = new SqlCommand(stmt, con).ExecuteReader();
            reader.Read();
        }

I hope this will solve your problem, If i'am wrong say it! Good Luck

score 0 · Accepted Answer · answered Nov 02 '17 at 14:06

It is better to wrap this code in a stored procedure, but it is possible without it. Pay attention to the transaction, it is necessary to preserve the integrity of the data.

        public void CreateMessage(int profileId, String text, int chatId)
        {
            string stmt = string.Format(
@"DECLARE @profileId int;
DECLARE @text nvarchar(4000);
DECLARE @chatId int;
DECLARE @id int;

SELECT
    @profileId = {0},
    @chatId = {1},
    @text = N'",
                profileId.ToString(), chatId.ToString());

            stmt += text;
            stmt +=
@"';

BEGIN TRAN;

INSERT INTO Activity (profileID, timeStamp) VALUES(@profileId, getdate());
IF @@ERROR = 0
    SET @id = @@IDENTITY;
ELSE
    GOTO on_roll_back;

INSERT INTO Text (activityID, message) values(@id, (select cast(@text as varbinary(max))));
IF @@ERROR = 0
    SET @id = @@IDENTITY;
ELSE
    GOTO on_roll_back;

INSERT INTO Message (textID, chatID) values (@id, @chatId);
IF @@ERROR = 0 BEGIN
    COMMIT TRAN;
    GOTO on_finish; 
END;

on_roll_back:
ROLLBACK TRAN;

on_finish:";

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = stmt;
                cmd.ExecuteNonQuery();
            }
        }

I answered your question, but to avoid a possible SQL injection attack (text = " '; drop table Users; -- ") you should use stored procedures and always pass the values through the parameters — Yargo, Nov 02 '17 at 14:36

Can i create rows in different tables all in one query?

4 Answers4