Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I insert data from one dataset into multiple tables

Posted on 2009-05-06
13
Medium Priority
?
1,076 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:luke_hippe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 24320449
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
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24320511
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.
0
 

Author Comment

by:luke_hippe
ID: 24326617
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
0
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 
LVL 22

Expert Comment

by:pivar
ID: 24327711
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
0
 

Author Comment

by:luke_hippe
ID: 24388424
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.  
0
 

Author Comment

by:luke_hippe
ID: 24391580
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.

0
 
LVL 22

Expert Comment

by:pivar
ID: 24392522
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.
0
 

Author Comment

by:luke_hippe
ID: 24395899
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?
0
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 24401601
Try this, change ... to required fields and values. Note the ';' which ends a sql statement. You can include a select as well and use a reader to retrieve the resultset. Check this link for details.

Regarding sessions, check this link: http://www.informit.com/articles/article.aspx?p=29583&seqNum=2
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
 
    mySqlCommand.CommandText =
      "INSERT INTO Salesinfo(...) VALUES (...);" +
      "INSERT INTO Items(SalesIndexFK , ...) VALUES (SCOPE_IDENTITY(), ...);";

Open in new window

0
 
LVL 22

Assisted Solution

by:pivar
pivar earned 2000 total points
ID: 24402094
0
 

Author Comment

by:luke_hippe
ID: 24408182
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

0
 

Author Comment

by:luke_hippe
ID: 24408202
ah.. I didn't use the cmdSelectIdent command, I don't know why I left that in there.  Anyway... Thanks.
0
 

Author Closing Comment

by:luke_hippe
ID: 31578757
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!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question