asked on

How do I insert data from one dataset into multiple tables

I have a database that holds our inventory. I use a JOIN to load data from the Items table and the Salesinfo table into a dataset. These are linked by a SalesIndexPK in the SalesInfo table and a SalesIndexFK in the Items table.

I am writing an application that allows a user add a new item to the database. I use SaleIndexPK as a unique identifier for SalesInfo table, and use ItemIndexPk as a unique identifier in the Items table. I place all the data collected for both tables into a clone of the dataset produce by the JOIN statement used above.

How can I insert the data from the dataset into both tables, making sure that the SalesIndexPK is the SalesIndexFK in the Items table?

My line of thinking is that I would need to use two INSERT statements, inserting into the SalesInfo table first, then retrieve the SalesIndexPK to insert into the Items table along with the Items table's data.

Because my database is fairly new, all the Items.SalesIndexFK = Items.ItemIndexPK. So if I add a row to Items, I could add a row to SalesInfo and the keys would match up. But I do not want to rely on this, as I am sure the tables will not always be in sync.

Is there another way to do this? Am I missing something? Can an INSERT command insert into multiple tables? Do I need to split my dataset into two sets that match the structure of the tables?

Thanks, guys!

Luke

pivar

Hi,

If SaleIndexPK is an IDENTITY, you can use SCOPE_IDENTITY in the second INSERT, if you do this in the same session/scope.

like

INSERT INTO Salesinfo(...) VALUES (...)
INSERT INTO Items(SalesIndexFK , ...) VALUES (SCOPE_IDENTITY(), ...)

/peter

Chris Luttrell

I would pass the data into a stored procedure which would first look to see if the Salesinfo record exists or needs to be created. Create that record first capturing the ID.
Insert into the Items table.

luke_hippe

ASKER

pivar, can you point me to a good reference to learn more about SCOPE_IDENTITY? I am pretty new to using SQL, and need all the resources I can get. How does SCOPE_IDENTITY work and how it it used?

CGLutterell, I fail to understand how I can check the existence of a record that I have yet to update. I get the second part about creating the SalesInfo record first, then capturing the SalesIndexPK to use as a SalesIndexFK for the Items record insertion, but that first part is lost to me. Could you further explain what you are talking about and include an example of how it's done?

Thanks folks!

Luke

pivar

Hi,

Read about it here http://msdn.microsoft.com/en-us/library/ms190315.aspx. MSDN is a pretty good reference resource.

In short, SCOPE_IDENTITY returns the last IDENTITY value inserted into an IDENTITY column in any table in the same scope.

/peter

luke_hippe

ASKER

I read up on scope_identity and @@identity and found the concepts very helpful. I am not sure exactly how to utilize scope_identity in my application, but I will figure it out. I am going to close this question as 'answered,' but I would like ask you first if you could supply some real-world examples of scope_identity and @@identity in .net applications. This may be of help to others who are searching for a similar solution.

luke_hippe

ASKER

I have had a change to experiment with @@IDENTITY.
I use 'SELECT @@IDENTITY FROM SalesInfo' and load the results into a datareader.

When I do this,

While drIdent.Read
' place into variable or paramater value for next query
End While

I get a one result for each row in my table (all the same value), not a big deal, until this database starts getting bigger.. is there another way to get this value? I don't want to have to cycle the whole datareader. when I use scope_identity as scope_identity i get the same result.

pivar

You have misunderstood the usage of @@IDENTITY and SCOPE_IDENTITY()
@@IDENTITY will give you the value of the identity column from the latest input sql serverwide.

So your example says, for every row in SalesInfo show the identity value of the latest input, that is the same value.
If you want to show the identity value of each row use 'SELECT SalesIndexPK FROM SalesInfo'. If SalesIndexPK is the name of the identity column.

In this case I recommend you to use SCOPE_IDENTITY() since it will give you the latest identity value in your session, while @@IDENTITY will give
you the latest identity value from any session. That is, if another user makes an insert between your insert and you fetching of @@IDENTITY, you will
get that value, not yours.

luke_hippe

ASKER

I see what you mean, and that could be big trouble if I started using @@identity. How would I use SCOPE_IDENTITY in an sql command from .net? What exactly entails a 'session'? Do I need to include the select statement in the same command as the insert?

ASKER CERTIFIED SOLUTION

pivar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

pivar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

luke_hippe

ASKER

Thanks for the examples. I was of the mindset that you must do each sql statement one at a time. It never occurred to me that I could put the all of the queries in the same command. I worked out how to use scope_Identity using multiple queries. I can imagine that your solution is much more elegant and efficient, but I included what I came up with. I placed all of these statements in it's own dll, and call them through the application, hence using a function. I intend to use your method to get less costly results.

Thanks for your help.

Public Function InsertNewItems(ByVal dsInsert As DataSet, ByVal tableName As String) As Boolean
        Dim iLastRow, x As Integer
        '''' ''''''''''''Declare Parameters (including CoIndexPK and SalesIndex FK)
        
        
        Dim cmdSalesInfo As New SqlCommand("INSERT INTO SalesInfo (NewPar, ParCorrection, CurrentSold, ProjectedMonth, InStock, ``````````OnOrder) values (@NewPar, @ParCorrection, @CurrentSold, @ProjectedMonth, @InStock, @OnOrder); SELECT `SCOPE_IDENTITY() ", bCon)
 
        Dim cmdSelectIdent As New SqlCommand("SELECT SCOPE_IDENTITY from SalesInfo", dbCon)
 
        Dim cmdSelectCoIndexFK As New SqlCommand("SELECT CoIndex FROM Company WHERE Company = @Company", dbCon)
 
        Dim cmdItems As New SqlCommand("INSERT INTO Items (ItemNumber, SKU, Description, Location, OOSStatus, Discontinued, " & _
                                       "LbsShipping, OzShipping, Cost, MAP, Notes, CaseQTY, SaleIndexFK, CoIndexFK) Values " & _
                                       "(@ItemNumber, @SKU, @Description, @Location, @OOSStatus, @Discontinued, @LbsShipping," & _
                                       " @OzShipping, @Cost, @MAP, @Notes, @CaseQty, @SaleIndexFK, @CoIndexFK) ", dbCon)
 
        For x = 0 To dsInsert.Tables(tableName).Rows.Count - 1
            '''' load values from dsInsert into parameters
            '''' add parameters to cmdSalesInfo
           
            Try
                dbCon.Open()
            Catch ex As Exception
                MsgBox(ex.Message.ToString & vbCrLf & "Item Information connection failed.")
                Return False
            End Try
                 '''' execute Query and clear parameters
            iLastRow = cmdSalesInfo.ExecuteScalar
            cmdSalesInfo.Parameters.Clear()
            dbCon.Close()
 
            ''''''''' load values from dsInsert into parameters
          
            '''''''''' add parameters to cmdItems
 
            '''' execute Insert into Items table
            Try
                dbCon.Open()
            Catch ex As Exception
                MsgBox(ex.Message.ToString & "   item update fail")
                Return False
            End Try
            cmdItems.ExecuteNonQuery()
            cmdItems.Parameters.Clear()
            dbCon.Close()
 
        Next x
        Return True

Open in new window

luke_hippe

ASKER

ah.. I didn't use the cmdSelectIdent command, I don't know why I left that in there. Anyway... Thanks.

luke_hippe

ASKER

I am really glad that the time was taken to explain both in theory and examples how this is done. I asked a lot of Pivar, and Pivar delivered. I wanted to make sure I didn't just say "thanks, that worked" without making sure that examples were provided. I come across that alot in forums and help sites. It's frustrating to have to re-ask a question or to work off of vague answers that may help an experienced user but does little to help new guys understand not just how, but why it's done a certain way. Again, thanks!