Read records from sql statement and insert those records into a sql table in

Posted on 2014-08-19
Last Modified: 2014-08-21
Hi all.

I want to read records from a sql statement and then insert those records into a sql server 2008 table. I was thinking of using SqlDataReader to do so but don't know how to get it to work, below is some code I've started to work on.

Please let me know how I can finish it off, or if I'm doing it incorrectly then what is the correct way to do so.

Thank you in advance.

Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim dr As SqlDataReader
        Dim  itemnumber1 As String
        Dim qtyordered, qtyshipped, qtybackordered As Decimal

con.ConnectionString = "Data Source=myserver;Initial Catalog=mydb;Integrated Security=True"


        cmd.Connection = con

cmd.CommandText = "SELECT ItemNumber, QtyOrdered, QtyShipped, QtyBackordered FROM myTableA t1 INNER JOIN myTableB t2 ON t1.InvoiceNumber = t2.InvoiceNumber WHERE (t2.SkipPrintingOfThisComponent <> 'Y') AND (t1.SalesOrderNumber = @SalesOrder3)"
        cmd.Parameters.AddWithValue("@SalesOrder3", txtSalesOrder.Text)

While dr.Read()
            itemnumber1 = dr("ItemNumber")
            qtyordered = dr("QtyOrdered")
            qtyshipped = dr("QtyShipped")
            qtybackordered = dr("QtyBackordered")

            cmd.CommandText = "INSERT INTO tblOrderPicking_Detail(  SalesOrder,ItemNumber,QtyOrderedEaches,QtyShippedEaches,QtyBackorderedEaches) VALUES (@SalesOrder4,@ItemNumber1,@QtyOrderedEaches,@QtyShippedEaches,@QtyBackorderedEaches )"

            cmd.Parameters.AddWithValue("@SalesOrder4", txtSalesOrder.Text)
            cmd.Parameters.AddWithValue("@ItemNumber1", itemnumber1)
            cmd.Parameters.AddWithValue("@QtyOrderedEaches", qtyordered)
            cmd.Parameters.AddWithValue("@QtyShippedEaches", qtyshipped)
            cmd.Parameters.AddWithValue("@QtyBackorderedEaches", qtybackordered)

        End While

Open in new window

Question by:printmedia
    LVL 28

    Accepted Solution


    I am assuming that you will be inserting one record at a time.

    So, the basic syntax will be to select the records you need based on the WHERE predicate you are passing.

    Then if the record exists, insert that into your other table:

    Something like:

                        INSERT INTO tblOrderPicking_Detail(  SalesOrder,ItemNumber,QtyOrderedEaches,QtyShippedEaches,QtyBackorderedEaches) 
            SELECT ItemNumber, QtyOrdered, QtyShipped, QtyBackordered FROM myTableA t1 INNER JOIN myTableB t2 ON t1.InvoiceNumber = t2.InvoiceNumber WHERE (t2.SkipPrintingOfThisComponent <> 'Y') AND (t1.SalesOrderNumber = @SalesOrder3)"

    Open in new window

    You can also use EXISTS function:

                s = "IF NOT EXISTS (SELECT ItemNumber, QtyOrdered, QtyShipped, QtyBackordered FROM myTableA t1 INNER JOIN myTableB t2 ON t1.InvoiceNumber = t2.InvoiceNumber WHERE (t2.SkipPrintingOfThisComponent <> 'Y') AND (t1.SalesOrderNumber = @SalesOrder3")           
     s += " BEGIN " '***Record does not exist, create one***
                s += " INSERT INTO tblOrderPicking_Detail(  SalesOrder,ItemNumber,QtyOrderedEaches,QtyShippedEaches,QtyBackorderedEaches)
                s += " End "
                s += " ELSE "
    Record already exists.

    Open in new window

    LVL 27

    Assisted Solution

    Wow, that's such an approach I would never dream of using :-)
    Essentially you're trying to perform an operation on a 'Set' - which is exactly what SQL is designed to do.
    1) Write a sql stored procedure that performs the logic you want, and call that from VB
    Or, if you don't have access to the sql database to code the stored procedure ...
    2) Create the sql to perform the action as a string in your VB code and execute that.

    It is never a good idea to iterate a sql reader result set and execute sql  statements based on it.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    745 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

    13 Experts available now in Live!

    Get 1:1 Help Now