CODEWITHSUNDEEP
c#asp.netsql
Color Shadow
Color Shadow

Reputation: 305

Insert data into two table in SQL

It it possible to insert data into two table simultaneously? In my current page, there is only one command that insert data only in one table,CarTab.

 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
    SqlCommand cmd = new SqlCommand("Insert into CarTab(Manufacture,Model,Plate,Color,Year,Service,Roadtax) Values (@manufactures,@models,@plates,@colors,@years,@services,@roadtaxs)", conn);

    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@manufactures", Label1.Text);
    cmd.Parameters.AddWithValue("@models", Label2.Text);
    cmd.Parameters.AddWithValue("@plates", Label3.Text);
    cmd.Parameters.AddWithValue("@colors", Label4.Text);
    cmd.Parameters.AddWithValue("@years", Label5.Text);
    cmd.Parameters.AddWithValue("@services", Label6.Text);
    cmd.Parameters.AddWithValue("@roadtaxs", Label7.Text);


    conn.Open();
    cmd.ExecuteNonQuery();

If I want to put another table, TempTab what is the command? Thanks.

Upvotes: 1

Views: 3590

Answers (4)

John Woo
John Woo

Reputation: 263693

There are many possible solution to your answer. First, I would suggest that you create a TRIGGER on every insert on table CarTab.

Second, why not change the CommandText of your command object and call ExecuteNonQuery() again?

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString))
{
    string QueryA = "Insert into CarTab(Manufacture,Model,Plate,Color,Year,Service,Roadtax) Values (@manufactures,@models,@plates,@colors,@years,@services,@roadtaxs";
    string QueryB = "Insert into TempTab(Manufacture,Model,Plate,Color,Year,Service,Roadtax) Values (@manufactures,@models,@plates,@colors,@years,@services,@roadtaxs";

    using (SqlCommand cmd = new SqlCommand("", conn))
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = QueryA;
        cmd.Parameters.AddWithValue("@manufactures", Label1.Text);
        cmd.Parameters.AddWithValue("@models", Label2.Text);
        cmd.Parameters.AddWithValue("@plates", Label3.Text);
        cmd.Parameters.AddWithValue("@colors", Label4.Text);
        cmd.Parameters.AddWithValue("@years", Label5.Text);
        cmd.Parameters.AddWithValue("@services", Label6.Text);
        cmd.Parameters.AddWithValue("@roadtaxs", Label7.Text);

        try
        {
            conn.Open()
            cmd.ExecuteNonQuery();

            cmd.CommandText = QueryB;
            cmd.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            // put error here
        }
        finally
        {
            conn.Close();
        }
    }
}

and Lastly create a Stored Procedure

CREATE PROCEDURE InsertRecord
@manufactures varchar(50),
@models varchar(50),
@plates varchar(50),
@colors varchar(50),
@years varchar(50),
@services varchar(50),
@roadtaxs varchar(50)
AS
BEGIN
        Insert into CarTab(Manufacture,Model,Plate,Color,Year,Service,Roadtax) 
        Values (@manufactures,@models,@plates,@colors,@years,@services,@roadtaxs)

        Insert into CarTab(Manufacture,Model,Plate,Color,Year,Service,Roadtax) 
        Values (@manufactures,@models,@plates,@colors,@years,@services,@roadtaxs)
END
GO

and in your C# code,

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString))
{

    using (SqlCommand cmd = new SqlCommand("InsertRecord", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = QueryA;
        cmd.Parameters.AddWithValue("@manufactures", Label1.Text);
        cmd.Parameters.AddWithValue("@models", Label2.Text);
        cmd.Parameters.AddWithValue("@plates", Label3.Text);
        cmd.Parameters.AddWithValue("@colors", Label4.Text);
        cmd.Parameters.AddWithValue("@years", Label5.Text);
        cmd.Parameters.AddWithValue("@services", Label6.Text);
        cmd.Parameters.AddWithValue("@roadtaxs", Label7.Text);

        try
        {
            conn.Open()
            cmd.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            // put error here
        }
        finally
        {
            conn.Close();
        }
    }
}

Upvotes: 0

Shiridish
Shiridish

Reputation: 4962

Multi-table inserts are an extension to INSERT..SELECT. Syntax is of the following form:

INSERT ALL|FIRST
[WHEN condition THEN] INTO target [VALUES]
[WHEN condition THEN] INTO target [VALUES]
...
[ELSE] INTO target [VALUES]
SELECT ...
FROM   source_query;

Source: http://www.oracle-developer.net/display.php?id=209

Upvotes: 0

Darin Dimitrov
Darin Dimitrov

Reputation: 1038720

You repeat the same code as the one shown in your question but modify the query and parameters for the second table. Also I would recommend you wrapping IDisposable resources such as database connections and commands in a using statement.

Upvotes: 0

MichaelT
MichaelT

Reputation: 7934

You can write 2 insert queries inside text you passing to SQL command or you can write a store procedure and call it instead.

Upvotes: 2

Related Questions