Merge and combine two tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Merge and combine two tables

Merge and combine two tables

Rate Topic

Display Mode

Topic Options

Author

Message

helal.mobasher 13209

Posted Thursday, August 30, 2012 12:52 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, March 24, 2013 2:05 PM
Points: 23, Visits: 99

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

anthony.green

Posted Thursday, August 30, 2012 1:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

helal.mobasher 13209

Posted Thursday, August 30, 2012 12:19 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, March 24, 2013 2:05 PM
Points: 23, Visits: 99

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

Charles Hearn

Posted Thursday, August 30, 2012 12:34 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:45 AM
Points: 557, Visits: 4,822


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

helal.mobasher 13209

Posted Thursday, August 30, 2012 2:02 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, March 24, 2013 2:05 PM
Points: 23, Visits: 99

Awesome....worked liek a charm.

Post #1352482

« Prev Topic | Next Topic »

Permissions