How to merge 2 datasets in a script

Posted on 2009-04-27
Medium Priority
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
  • 3
  • 2

Author Comment

ID: 24245608
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

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

Accepted Solution

nmcdermaid earned 2000 total points
ID: 24247641
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

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

Expert Comment

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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

616 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