CODEWITHSUNDEEP
c#sql-server
Jrow
Jrow

Reputation: 1054

Insert, select and insert in same command

Currently i'm using the following C# code to insert rows into a database

int result = 0;
            using (SqlConnection connect = new SqlConnection(connectionString))
            {                    
                string insertQuery = @"Insert Into Conversions (CID, Source, Destination, Packages, CreatedBy, SourceID) 
            SELECT @CID, @Source, @Destination, @Packages, @CreatedBy, @SourceID";
                SqlCommand command = new SqlCommand(insertQuery, connect);
                command.Parameters.AddWithValue("CID", cidBox.Text);
                command.Parameters.AddWithValue("Source", sourceSoftwareBox.Text);
                command.Parameters.AddWithValue("Destination", destinationSoftwareBox.Text);
                command.Parameters.AddWithValue("Packages", packagesBox.Text);                    
                command.Parameters.AddWithValue("CreatedBy", Environment.UserName);
                command.Parameters.AddWithValue("SourceID", sourceID.sourceID);
                connect.Open();
                result = Convert.ToInt32(command.ExecuteScalar());
            }

I have another table called HistoricalData with the following columns: Id, Status, Comments, Date

I want to be able to Insert into the Conversions table, select the Id that was just auto created from the insert and use that Id in a second insert into the HistoricalData table. Can I do this with some sort of SQL magic?

Upvotes: 0

Views: 274

Answers (2)

GarethD
GarethD

Reputation: 69749

You can use the OUTPUT clause to insert directly into the 2nd table. Your SQL would be something like:

INSERT Conversions (CID, Source, Destination, Packages, CreatedBy, SourceID)
OUTPUT inserted.ID, @Status, @Comments, GETDATE()
    INTO HistoricalData (ID, Status, Comments, Date)
VALUES (@CID, @Source, @Destination, @Packages, @CreatedBy, @SourceID);

This won't work however if there is a foreign key relationship between the two tables. In some cases I would advise simply using the output clause to insert into a temp table before inserting to the child table, but since you are only inserting a single row and only accessing the identity field, you could just use SCOPE_IDENTITY():

INSERT Conversions (CID, Source, Destination, Packages, CreatedBy, SourceID)
VALUES (@CID, @Source, @Destination, @Packages, @CreatedBy, @SourceID); 

INSERT HistoricalData (ID, Status, Comments, Date)
VALUES (SCOPE_IDENTITY(), @Status, @Comments, GETDATE());

N.B. In both instances I was unsure of what you would want to add as Status, Comment, and Date, so have used variables and GETDATE(), you may need to adjust this.

Another note, I have switched from a SELECT with no FROM to the VALUES constructor for the insert, mostly out of second nature when rewriting the queries. There is no difference in the two methods, although I do find using VALUES displays intent better, it shows clearly nothing is being selected.

Upvotes: 1

TSungur
TSungur

Reputation: 406

If your database in question supports stored procedures i would definitely go that way. Create a stored procedure with required parameters. Create your SqlCommand like this:

SqlCommand command = new SqlCommand("MyStoredProcedureName", connect);
command.CommandType=CommandType.StoredProcedure;

Pass your parameters like in your current code. Your database stored procedure would look something like this:

CREATE PROCEDURE MyStoredProcedureName 
@CID bigint,
..... all params
    AS
    BEGIN
   DECLARE @myNewID bigint
SET NOCOUNT ON;

        Insert Into Conversions (CID, Source, Destination, Packages, CreatedBy, SourceID) VALUES (@CID, @Source, @Destination, @Packages, @CreatedBy, @SourceID)
    SET @myNewID=SCOPE_IDENTITY()
    -- Now you can insert what you want where you want
    INSERT INTO  HistoricalData (Column list...) (Values list ... @myNewID)
    END

If you are not using Entity Framework or something like this it is best to keep database code separate from rest of your code. So when you change table names/column names or any change in database structure you don't have to change your code. Just change the database part and your stored procedures.

Upvotes: 1

Related Questions