| Author |
 Topic  |
|
|
dtong004
Constraint Violating Yak Guru
USA
281 Posts |
 Posted - 06/08/2001 : 14:28:38
|
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
15557 Posts |
Posted - 06/08/2001 : 20:10:10
|
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?
|
 |
|
|
dtong004
Constraint Violating Yak Guru
USA
281 Posts |
Posted - 06/11/2001 : 10:15:13
|
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
|
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 06/11/2001 : 10:25:48
|
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 |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 06/11/2001 : 11:01:17
|
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).
|
|
|
|
dtong004
Constraint Violating Yak Guru
USA
281 Posts |
Posted - 06/11/2001 : 14:44:27
|
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
|
|
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
Posted - 06/11/2001 : 14:57:52
|
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
|
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 06/11/2001 : 15:01:40
|
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.
|
|
|
|
dtong004
Constraint Violating Yak Guru
USA
281 Posts |
Posted - 06/11/2001 : 15:51:56
|
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.
|
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 06/11/2001 : 18:33:24
|
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 |
|
|
|
Tim
Starting Member
Australia
392 Posts |
Posted - 06/11/2001 : 22:41:10
|
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 |
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 06/12/2001 : 09:21:48
|
Thank you Tim!
|
|
|
|
Teroman
Posting Yak Master
United Kingdom
115 Posts |
Posted - 07/12/2001 : 08:47:46
|
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
|
|
|
|
dtong004
Constraint Violating Yak Guru
USA
281 Posts |
Posted - 07/12/2001 : 10:02:41
|
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
|
|
|
|
Teroman
Posting Yak Master
United Kingdom
115 Posts |
Posted - 07/12/2001 : 10:50:50
|
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)
|
|
|
| |
Topic |
|
|
|
Merge TWO, Problem is not solved
