Solved

How do I insert data from one dataset into multiple tables

Posted on 2009-05-06
13
1,068 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
  • 7
  • 5
13 Comments
 
LVL 22

Expert Comment

by:pivar
Comment Utility
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 26

Expert Comment

by:Chris Luttrell
Comment Utility
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
Comment Utility
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
 
LVL 22

Expert Comment

by:pivar
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:pivar
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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 500 total points
Comment Utility
0
 

Author Comment

by:luke_hippe
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now