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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 20, 2014 8:33 PM
Points: 24, Visits: 112
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: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 20, 2014 8:33 PM
Points: 24, Visits: 112
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
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:04 AM
Points: 557, Visits: 4,841

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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 20, 2014 8:33 PM
Points: 24, Visits: 112
Awesome....worked liek a charm.
Post #1352482
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse