How to merge 2 datasets in a script

Posted on 2009-04-27
Last Modified: 2013-11-30
I have a dtsx package that uses a script to display data in 2 HTML tables. The customer has requested to merge the datasets. Can someone advise me how to do this?

Thank you
Public Sub Main()   

 Dim Mail As New MailMessage

        Dim SnapshotsCS As String = "Data Source=EGLSMSQL21;Initial Catalog=Snapshots;Persist Security Info=True;User ID=knowledge;Password=tesVraH"

        Dim DS As New DataSet

        Dim SVGReader As StreamReader = New StreamReader("\\EGLSFPS02\Knowledge_Harvest\Snapshots\NASHINT.htm")

        Dim File As String = SVGReader.ReadToEnd

        Dim EG3Query As String = "select * from EG3 where datediff(day,date,getdate()) = 0"

        Dim EG3Adapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(EG3Query, SnapshotsCS)

        EG3Adapter.Fill(DS, "EG3")

        File = Replace(File, "VCapacity", DS.Tables(0).Rows(0).Item("Capacity").ToString)

        File = Replace(File, "VLoads_Received", DS.Tables(0).Rows(0).Item("Loads_Received").ToString)

        File = Replace(File, "VPallets_Received", DS.Tables(0).Rows(0).Item("Pallets_Received").ToString)

        File = Replace(File, "VHV_Pallets_Received", DS.Tables(0).Rows(0).Item("HV_Pallets_Received").ToString)

        File = Replace(File, "VTotal_Scheduled", DS.Tables(0).Rows(0).Item("Total_Scheduled").ToString)

        File = Replace(File, "VPallets_Expected_Today", DS.Tables(0).Rows(0).Item("Pallets_Expected_Today").ToString)

        File = Replace(File, "VOpen_Appointments", DS.Tables(0).Rows(0).Item("Open_Appointments").ToString)

        File = Replace(File, "VHours_Out", DS.Tables(0).Rows(0).Item("Hours_Out").ToString)

        File = Replace(File, "VPallets_Shipped", DS.Tables(0).Rows(0).Item("Pallets_Shipped").ToString)

        File = Replace(File, "VHV_Pallets_Shipped", DS.Tables(0).Rows(0).Item("HV_Pallets_Shipped").ToString)

        File = Replace(File, "VCartons_Shipped", DS.Tables(0).Rows(0).Item("Cartons_Shipped").ToString)

        File = Replace(File, "VOrders", DS.Tables(0).Rows(0).Item("Orders").ToString)

        Dim EG4Query As String = "select * from EG4 where datediff(day,date,getdate()) = 0"

        Dim EG4Adapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(EG4Query, SnapshotsCS)

        EG4Adapter.Fill(DS, "EG4")

        File = Replace(File, "EG4Capacity", DS.Tables("EG4").Rows(0).Item("Capacity").ToString)

        File = Replace(File, "EG4Loads_Received", DS.Tables("EG4").Rows(0).Item("Loads_Received").ToString)

        File = Replace(File, "EG4Pallets_Received", DS.Tables("EG4").Rows(0).Item("Pallets_Received").ToString)

        File = Replace(File, "EG4Total_Scheduled", DS.Tables("EG4").Rows(0).Item("Total_Scheduled").ToString)

        File = Replace(File, "EG4Pallets_Expected_Today", DS.Tables("EG4").Rows(0).Item("Pallets_Expected_Today").ToString)

        File = Replace(File, "EG4Open_Appointments", DS.Tables("EG4").Rows(0).Item("Open_Appointments").ToString)

        File = Replace(File, "EG4Hours_Out", DS.Tables("EG4").Rows(0).Item("Hours_Out").ToString)

        File = Replace(File, "EG4Pallets_Shipped", DS.Tables("EG4").Rows(0).Item("Pallets_Shipped").ToString)

        File = Replace(File, "EG4Cartons_Shipped", DS.Tables("EG4").Rows(0).Item("Cartons_Shipped").ToString)

        File = Replace(File, "EG4Orders", DS.Tables("EG4").Rows(0).Item("Orders").ToString)

Open in new window

Question by:JessyRobinson1234

    Author Comment

    I added 2 more columns to the EG4 table (HV Pallets Shipped, HV Pallets Received) so all the columns can be horizontally merged. The primary key for both tables is the date.

    Author Comment

    It's one dataset that uses 2 SQL Data Adapters. Anyone?
    LVL 30

    Accepted Solution

    Without looking too hard at it....
    you could always use one SQL statement:
    "select * from EG3 where datediff(day,date,getdate()) = 0 UNION ALL select * from EG4 where datediff(day,date,getdate()) = 0"
    but I strongly suggest you lose the select *. you should explicitly name the columns. Particularly when using UNION ALL
    There's probably a dataset operation that will do it in code but it won't be as efficient as doing it in SQL.

    Author Closing Comment

    I agree. I will create a view in SQL to combine both and call the view from the script.
    LVL 30

    Expert Comment

    ......that was my next suggestion :) - create a view.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now