SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Merge TWO, Problem is not solved
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 06/08/2001 :  14:28:38  Show Profile  Visit dtong004's Homepage  Reply with Quote
The question did not answered and I want to get help.

Here is the orignal question:

I want to be able to merge two tables into one using a stored procedure if possible.

I have two tables with a common set of columns. (Common meaning same column name and datatype/size.) I won't know the names of those common columns or how many columns between the two tables are common.

These two tables can also have additional columns as well.

How can I merge these two tables' structures creating a new table and then populate the resulting table with the applicable data from the existing tables?
Example:
----Table One------

[db1]..[Table One] ColA varchar(25) -- Same as ColA in db2ColB int -- Same as ColB in db2Colx char(10) -- Different Col

-------------------


----Table Two ----- [db2]..[Table Two] ColA varchar(25) -- Same as ColA in db1ColB int -- Same as ColB in db1Col9 int -- Different Col


-------------------
The resulting table I want is:

[db3]..[MergedTbl] ColA varchar(25) ColB int colx char(10) Col9 int


The original answer:

There are two approaches you could take to solve this problem. The first is a UNION query where you simply specify NULL for the columns that do not match between the two tables. The other is to build a temporary table and populate it with the each table. Effectively the same result as the UNION query.


However, my problem did not solved. Here is my problem: ****************************************

The answer does not address the question. The problem is we need the records in table two match colA and colB with should be updated.

example:
table one:

AA, 2, X
AB, 3, Y
AA, 3, Z
AA, 3, R

Table two like this:
AA, 3, 27
AB, 3, 50
AA, 1, 30

The result should be like this:

AA, 2, X, -
AB, 3, Y, 50
AA, 3, Z, 27
AA, 3, R, -
AA, 1, -, 30

Aperantly, Union cannot do the work. The key is colA+colB is not unique, I don't want the data in table two repeated.

Thanks for you help.

Daniel



robvolk
Most Valuable Yak

USA
15657 Posts

Posted - 06/08/2001 :  20:10:10  Show Profile  Visit robvolk's Homepage  Reply with Quote
1. Why won't you know the names of the common columns, or how many are common between the two (or more) tables? They're your tables, aren't they?

2. The UNION answer does address your question, IMHO, if you use the NULL value for columns missing from one table but present in the other, like it says. Maybe it didn't work for other reasons (maybe you made a mistake).

3. Why are you doing this anyway? How often do you have to do this? If it's just once, why not just slog through it and be done with it?

Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 06/11/2001 :  10:15:13  Show Profile  Visit dtong004's Homepage  Reply with Quote
Thanks for yoru repsonse.

I am writing this as a final report purpose.

The common columns is the employee and they have multiple working "projects" in Table 1, That is why I say it is NOT unique based on the EMP alone. Table 2, however, employess also have multiple working "results". Those reults however, have no relationship with "projects".

It is perfect for two table format and it is two separate reports. However, users are asking, why not paste those two table together based on employee, in that case we just present one report.

get it?

I was thinking use two cursors at one procedure to solve this. It appears, MS-SQL only supports one cursor at a time. The global variable @@fetchstatus only refer to one cursor.

Anyone have any ideas make it like : cursor1.fetchstatus vs cursor2.fetchstatus? , or this is the limilitation of MS-SQL?

Thanks for you help

Go to Top of Page

robvolk
Most Valuable Yak

USA
15657 Posts

Posted - 06/11/2001 :  10:25:48  Show Profile  Visit robvolk's Homepage  Reply with Quote
Try this:

SELECT DISTINCT IsNull(T1.ColA,T2.ColA) Column1, IsNull(T1.ColB,T2.ColB) Column2,
T1.Colx ColumnA, T2.Colx ColumnB
FROM db1..TableOne T1 CROSS JOIN db2..TableTwo T2


You'll probably have to change table and column names. Check Books Online for more details on CROSS JOIN and DISTINCT. If this does not work, I don't know what will, and frankly I'm not sure it's all that useful anyway.

Edited by - robvolk on 06/11/2001 11:16:58
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/11/2001 :  11:01:17  Show Profile  Visit nr's Homepage  Reply with Quote
cross join shold work.
If you don't know the column names then you can use dynamic sql to build the query from syscolumns.

I don't know anything about cursors but the fetch status is the status from the last fetch - can have more than one cursor (but should only need less than one).


Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 06/11/2001 :  14:44:27  Show Profile  Visit dtong004's Homepage  Reply with Quote
DUH!, Cross join is a Cartesian product. It should NOT work. I run the code: its result is like this:

Column1 Column2 ColumnA ColumnB
------- ----------- ------- -----------
AA 2 X 27
AA 2 X 30
AA 2 X 50
AA 3 R 27
AA 3 R 30
AA 3 R 50
AA 3 Z 27
AA 3 Z 30
AA 3 Z 50
AB 3 Y 27
AB 3 Y 30
AB 3 Y 50

Everything doubled!. Guys, we got be have some way to solve this in MS-SQL

Daniel

Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 06/11/2001 :  14:57:52  Show Profile  Reply with Quote
If you would check BOL before criticizing somebody trying to help you you would realize that a cross join with a where clause DOES NOT produce a cartesian.

Justin

Go to Top of Page

robvolk
Most Valuable Yak

USA
15657 Posts

Posted - 06/11/2001 :  15:01:40  Show Profile  Visit robvolk's Homepage  Reply with Quote
Maybe this will help:

SELECT DISTINCT IsNull(T1.ColA,T2.ColA) Column1, IsNull(T1.ColB,T2.ColB) Column2,
T1.Colx ColumnA, T2.Colx ColumnB
FROM db1..TableOne T1 CROSS JOIN db2..TableTwo T2
WHERE IsNull(T1.ColA,T2.ColA)=IsNull(T2.ColA,T1.ColA)
AND IsNull(T1.ColB,T2.ColB)=IsNull(T2.ColB,T1.ColB)


And if that doesn't do it, then good luck finding a solution. I'm done.

To be honest, your explanation of what you want really doesn't make sense. Your users might want this report combined, but if the tables are modeling different but related data, they won't necessarily combine the way you want. That's why you put them in two different tables, right? Think about that before you start dumping on someone's try at solving this for you.

If you don't appreciate the efforts we've put into finding a solution, go to another site and see if they can help.

Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 06/11/2001 :  15:51:56  Show Profile  Visit dtong004's Homepage  Reply with Quote
ALL RIGHT! I apology for that if I offend anyone here.

What user needed is that they want "cut" the report of first table and "paste" to the second table, or vice versa. The problem is that there is no unique link between two tables.

My first thoughts for this question is " It should be doable" . I bet my users are not the unique business group asking for this kind of report(s).

I feel sorry to say that Mr. robvolk, your second solution does not work either. The result is like this:

Column1 Column2 ColumnA ColumnB
------- ----------- ------- -----------
AA 3 R 27
AA 3 Z 27
AB 3 Y 50

Maybe we should put this into the reader's challange and draw more attention from user group

Thanks for you help.

Dan

we all learn sql.

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 06/11/2001 :  18:33:24  Show Profile  Visit Merkin's Homepage  Reply with Quote
Yep mr. dtong004. Rob is right, being rude won't help your cause.

I think you should read up on Graz's Law (http://www.sqlteam.com/item.asp?ItemID=1353) which states

"If you can't get data in or out easily, you're not storing it right."

Damian

we all learn manners
Go to Top of Page

Tim
Starting Member

Australia
392 Posts

Posted - 06/11/2001 :  22:41:10  Show Profile  Reply with Quote
For clarity I rename the columns as follows:


Table1: Key1, Key2, Value
Table2: Key1, Key2, Value
Result: Key1, Key2, Value1, Value2


Here is the process you go through to get the desired result:


for each record in Table1

result.key1 = table1.key1
result.key2 = table1.key2
result.value1 = table1.value

find the first table2 record which matches table1.key1 and table1.key2

if not found then
result.value2 = '-'
else
result.value2 = table2.value
ignore this table2 record from now on
end if

next


for each remaining record in table2


result.key1 = table2.key1
result.key2 = table2.key2
result.value1 = '-'
result.value2 = table2.value

next


So as you can see it is a PROCEDURAL task not a SET BASED task so you will not be able to do it with a straight forward SQL statement.

(a SQL solution would require joining, and there is no way to avoid the unwanted repeating records as the result of joins)

You would be able to do it with cursors so that you can implement the loops and individual record processing.

To ignore the already processed table2 records you could dump all of table2 into a temp table. Then use the temp table in the loops (instead of table2) and delete each record as it was processed.

The problem is that the data model sucks (to be brutally honest), and as usual, with a bad data model you *CAN* make a solution but the performance will also suck.

I suggest that you revisit the requirement and design a data model accordingly.

hope that helps



Edited by - tim on 06/11/2001 22:57:53
Go to Top of Page

robvolk
Most Valuable Yak

USA
15657 Posts

Posted - 06/12/2001 :  09:21:48  Show Profile  Visit robvolk's Homepage  Reply with Quote
Thank you Tim!

Go to Top of Page

Teroman
Posting Yak Master

United Kingdom
115 Posts

Posted - 07/12/2001 :  08:47:46  Show Profile  Send Teroman an AOL message  Reply with Quote
i think ive done it, paste the following into query analyser.

the only thing is the results for 'AA',3 are different to those in the original post, but to be honest i think mine are right (although i may have the wrong end of the stick, so say if im wrong)

here the code:


set nocount on

create table #t1(c1 char(2), n1 int, val1 char(1))
create table #t2(c1 char(2), n1 int, val2 int)

insert into #t1
select 'AA',2,'X'
insert into #t1
select 'AB',3,'Y'
insert into #t1
select 'AA',3,'Z'
insert into #t1
select 'AA',3,'R'


insert into #t2
select 'AA',3,27
insert into #t2
select 'AB',3,50
insert into #t2
select 'AA',1,30

select * from #t1
select * from #t2

select isnull(#t1.c1,#t2.c1),
isnull(#t1.n1,#t2.n1),
#t1.val1,
#t2.val2
from #t1
full outer join #t2
on #t1.c1 = #t2.c1
and #t1.n1 = #t2.n1

drop table #t1
drop table #t2



Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 07/12/2001 :  10:02:41  Show Profile  Visit dtong004's Homepage  Reply with Quote
Your solution is hardcoded.
I will post my solution with cursors solution.

Daniel

quote:

i think ive done it, paste the following into query analyser.

the only thing is the results for 'AA',3 are different to those in the original post, but to be honest i think mine are right (although i may have the wrong end of the stick, so say if im wrong)

here the code:


set nocount on

create table #t1(c1 char(2), n1 int, val1 char(1))
create table #t2(c1 char(2), n1 int, val2 int)

insert into #t1
select 'AA',2,'X'
insert into #t1
select 'AB',3,'Y'
insert into #t1
select 'AA',3,'Z'
insert into #t1
select 'AA',3,'R'


insert into #t2
select 'AA',3,27
insert into #t2
select 'AB',3,50
insert into #t2
select 'AA',1,30

select * from #t1
select * from #t2

select isnull(#t1.c1,#t2.c1),
isnull(#t1.n1,#t2.n1),
#t1.val1,
#t2.val2
from #t1
full outer join #t2
on #t1.c1 = #t2.c1
and #t1.n1 = #t2.n1

drop table #t1
drop table #t2







Go to Top of Page

Teroman
Posting Yak Master

United Kingdom
115 Posts

Posted - 07/12/2001 :  10:50:50  Show Profile  Send Teroman an AOL message  Reply with Quote
i thought the challenge was just to do it in a set based way, quicker and simpler than a cursor as there is just one select(the rest is getting the temp tables together)

if you need it dynamic make it dynamic! make the sql sting as you go along, its not really a problem is it?

i was just showing it can be done in a very simple way, avoiding cursors, which is what you asked for(although that may have been in another thread)

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000