Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Merge and combine two tables Expand / Collapse
Author
Message
Posted Thursday, August 30, 2012 12:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 11:48 AM
Points: 50, Visits: 184
I need to merge/combine the following two tables (tabl1 & table2). Results are in #Mergedtables . Value data on both tables are already aggregated

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#testtable1') IS NOT NULL DROP TABLE #testtable1

--===== Create the test table with
CREATE TABLE #testtable1
(
paytoname varchar (255),
pname varchar (255),
contract1 varchar (255),
Memcount1 int,-- aleady summed up
Paid1 int,-- aleady summed up
plans1 varchar (150),
)

INSERT INTO #testtable1
(paytoname, pname, contract1,Memcount1,Paid1,Plans1)
SELECT 'American DME','Smith, Frank' , '100%Medi' , '1500', '2400', 'Medplan1'
UNION ALL
SELECT 'ERP DME','Boyd, Carl' , '105%Medi' , '2500', '400', 'Medplan2'
UNION ALL
SELECT 'MOP DME','Falling, Peter' , '100%Medi' , '200', '1400', 'Medplan1'

IF OBJECT_ID('TempDB..#testtable2') IS NOT NULL DROP TABLE #testtable2

--Table 2
CREATE TABLE #testtable2
(
paytoname varchar (255),
pname varchar (255),
contract2 varchar (255),
Memcount2 int,-- aleady summed up
Pcount2 int,-- aleady summed up
Paid2 int, -- aleady summed up
plans2 varchar (150),
)

INSERT INTO #testtable2
(paytoname, pname, contract2,Memcount2,Pcount2,Paid2,Plans2)
SELECT 'American DME','Smith, Frank' , '101%Med' , '700', '150' , '1200', 'Medplan4'
UNION ALL
SELECT 'ERP DME','Boyd, Carl' , '105%Med' , '900', '600', '175', 'Medplan4'
UNION ALL
SELECT 'MOP DME','Falling, Peter' , '100%Med' , '1200', '3500', '240' , 'Medplan4'
UNION ALL
SELECT 'ASH DME','Ferrari, Shelly' , '90%Med' , '250', '1100', '197','Medplan4'

IF OBJECT_ID('TempDB..#Mergedtables ') IS NOT NULL DROP TABLE #Mergedtables
--Merged Tables Results
CREATE TABLE #Mergedtables
(
paytoname varchar (255),
pname varchar (255),
contract1 varchar (255),
contract2 varchar (255),
Memcount1 int,
Memcount2 int,
Pcount int,
Paid1 int,
Paid1 int,
plans1 varchar (150),
plans2 varchar (150)
)

INSERT INTO #Mergedtables
(paytoname, pname, contract1,contract2,Memcount1,Memcount2,Pcount1,Pcount2,Paid1,Paid2,Plans1,plans2)
SELECT 'American DME','Smith, Frank' , '100%Medi' , '101%Med' , '1500', '700', '2400' , '150' , '1200', 'Medplan1','Medplan4'
UNION ALL
SELECT 'ERP DME','Boyd, Carl' , '105%Medi','105%Med' , '2500', '900', '400', '600', '175', 'MedPlan2','Medplan4'
UNION ALL
SELECT 'MOP DME','Falling, Peter' , '100%Medi' , '100%Med' , '200', '1200', '1400', '3500', '240' , 'Medplan1' , 'Medplan4'
UNION ALL
SELECT 'ASH DME','Ferrari, Shelly' , '90%Med' , '250', '1100', '197','Medplan4'

Thank you for your help

Helal
Post #1352029
Posted Thursday, August 30, 2012 1:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, July 27, 2015 5:53 AM
Points: 5,224, Visits: 5,139
Thanks for the DDL but it is not syntactically correct.

There are misssing columns from table1 (PCount), you are not specifiying enough columns in the union for the results table.

But from looking at the requirements it would seem you need an outer join and join the tables together.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1352039
Posted Thursday, August 30, 2012 12:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 11:48 AM
Points: 50, Visits: 184
Thank you for catching that. Here is the corrected and tested DDL. I did try left join but can't get them lined up the way presented in results (MergedTab).

IF OBJECT_ID('TempDB..#table1') IS NOT NULL DROP TABLE #table1

--===== Create the test table with
CREATE TABLE #table1

(
paytoname varchar (255),
pname varchar (255),
contract1 varchar (255),
Memcount1 int,-- aleady summed up
Paid1 int,-- aleady summed up
plans1 varchar (150),
)

INSERT INTO #table1
(paytoname, pname, contract1,Memcount1,Paid1,Plans1)
SELECT 'American DME','Smith, Frank' , '100%Medi' , '1500', '2400', 'Medplan1'
UNION ALL
SELECT 'ERP DME','Boyd, Carl' , '105%Medi' , '2500', '400', 'Medplan2'
UNION ALL
SELECT 'MOP DME','Falling, Peter' , '100%Medi' , '200', '1400', 'Medplan1'

--Table 2
IF OBJECT_ID('TempDB..#table2') IS NOT NULL DROP TABLE #table2

CREATE TABLE #table2
(
paytoname varchar (255),
pname varchar (255),
contract2 varchar (255),
Memcount2 int,-- aleady summed up
Pcount int,-- aleady summed up
Paid2 int, -- aleady summed up
plans2 varchar (150),
)

INSERT INTO #table2
(paytoname, pname, contract2,Memcount2,Pcount,Paid2,Plans2)
SELECT 'American DME','Smith, Frank' , '101%Med' , '700', '150' , '1200', 'Medplan4'
UNION ALL
SELECT 'ERP DME','Boyd, Carl' , '105%Med' , '900', '600', '175', 'Medplan4'
UNION ALL
SELECT 'MOP DME','Falling, Peter' , '100%Med' , '1200', '3500', '240' , 'Medplan4'
UNION ALL
SELECT 'ASH DME','Ferrari, Shelly' , '90%Med' , '250', '1100', '197','Medplan4'

--Merged Tables Results
IF OBJECT_ID('TempDB..#MergedTab ') IS NOT NULL DROP TABLE #MergedTab
CREATE TABLE #MergedTab
(
paytoname varchar (255),
pname varchar (255),
contract1 varchar (255),
contract2 varchar (255),
Memcount1 int,
Memcount2 int,
Pcount int,
Paid1 int,
Paid2 int,
plans1 varchar (150),
plans2 varchar (150)
)

INSERT INTO #MergedTab
(paytoname, pname, contract1,contract2,Memcount1,Memcount2,Pcount,Paid1,Paid2,Plans1,plans2)
SELECT 'American DME', 'Smith, Frank', '100%Medi', '101%Med', '1500', '700', '150', '2400', '1200', 'Medplan1', 'Medplan4'
UNION ALL
SELECT 'ERP DME', 'Boyd, Carl', '105%Medi', '105%Med', '2500', '900', '600', '400', '175', 'Medplan2', 'Medplan4'
UNION ALL
SELECT 'MOP DME', 'Falling, Peter', '100%Medi', '100%Med', '200', '1200','3500', '1400', '240', 'Medplan1', 'Medplan4'
UNION ALL
SELECT 'ASH DME', 'Ferrari, Shelly', '', '90%Med', '', '250', '1100', '', '197', '', 'Medplan4'


Thank You for reviewin gand giving me directions.

Helal
Post #1352412
Posted Thursday, August 30, 2012 12:34 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, July 24, 2015 5:05 AM
Points: 681, Visits: 5,078

SELECT ISNULL(T1.paytoname, T2.paytoname) paytoname,
ISNULL(T1.pname, T2.pname) pname,
ISNULL(contract1,'') contract1,
contract2,
ISNULL(Memcount1,0) Memcount1,
Memcount2,
Pcount,
ISNULL(Paid1,0) Paid1,
Paid2,
ISNULL(plans1,'') plans1,
plans2
FROM #table2 T2
LEFT JOIN #table1 T1 ON T2.paytoname = T1.paytoname


Post #1352417
Posted Thursday, August 30, 2012 2:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 11:48 AM
Points: 50, Visits: 184
Awesome....worked liek a charm.
Post #1352482
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse