June 27, 2005 at 5:32 am
Hey people!
Does anybody know how to merge 2 columns together from two different tables or even more from two different databases?
Say I have the databases Sarah_1 and Sarah_2
and I want to take a column from Sarah_1 and add it to an already existing table in Sarah_2 without overwriting the Sarah_2 data???
HOW can this be done!! I need you experts to help!!!
TAKE CARE
Sarah
June 27, 2005 at 5:41 am
Not 100% sure I understand. Do you want to create a brand new column in Sarah_2 to which will be copied the Sarah_1 data?
Is there a one-to-one relationship between the two tables?
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 27, 2005 at 5:57 am
Do you mean something like this:
CREATE TABLE foo (a int NOT NULL, b int NOT NULL)
CREATE TABLE bar (a int NOT NULL, c int NOT NULL)
GO
INSERT INTO foo VALUES (1, 10)
INSERT INTO foo VALUES (2, 20)
INSERT INTO bar VALUES (1, 100)
INSERT INTO bar VALUES (3, 300)
-- add information in column foo.b to new column in bar
ALTER TABLE bar ADD b int NOT NULL CONSTRAINT def DEFAULT 0
GO
UPDATE bar SET b = COALESCE((SELECT b FROM foo WHERE a = bar.a), 0)
ALTER TABLE bar DROP CONSTRAINT def
June 27, 2005 at 6:53 am
yes. that's what I want.
what is a one to one relationship? meaning there are as any columns in Sarah_1 as in Sarah_2?
Not necessarily.
ok, lets say i have this table:
Table: Sarah_1
ID Hair Color Eye Color
1451 blue green
1245 brown brown
2135 brown pink
Table: Sarah_2
ID Height Weight
1564 5-3 127
1563 5-7 154
2135 6-8 250
Is it possible to Merge these tables so that it looks something like this...the order of the rows is not important now...:
Table: Sarah_2
1564 5-3 127
1563 5-7 154
2135 6-8 250 brown pink
1245 brown brown
1451 blue green
June 27, 2005 at 7:05 am
A one-to-one relationship means that there is one row in table Sarah_1 that matches one row in Sarah_2. If there is a one-to-many relationship, e.g. one row in Sarah_1 with id X but several rows in Sarah_2 with id X, then you would not know which row to take the value for the new column from. But from your example it looks like a one-to-one relationship.
Using something similar to my example above you should be able to do this. The procedure is basically:
1. Add the new column(s) to Sarah_1, with either defaults or allowing NULLs in the new column(s).
2. Update the rows in Sarah_1 that matches those in Sarah_2, copying the value from Sarah_2 to use for the new column(s).
3. Insert the rows from Sarah_2 that do not already have a matching id in Sarah_1. Here however you need to make sure that the existing columns allow null or have a default, since you have no value for them.
June 27, 2005 at 7:11 am
Or can't you just do a cross db select??
June 27, 2005 at 7:14 am
Is there an automated way of doing this? Or I have to code it?
what does the merge replica wizard do?
Theoretically speaking....what if there was a one to many relationship?
June 27, 2005 at 7:52 am
The easiest way to do this is first to add the new columns to Sarah_2 - just use Enterprise Manager - making sure that the datatypes are the same as they are in Sarah_1. These columns will not contain any data ... yet.
Then write an UPDATE query to copy the data over:
Update s2
set HairColour = s1.Haircolour, EyeColour = s1.EyeColour
from Sarah_2 s2 inner join Sarah_1 s1 on s2.id = s1.id
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 27, 2005 at 7:53 am
How do you keep it in sync now??
June 28, 2005 at 12:47 am
Ok, you have to walk me through this please!!!
Where do I put the update procedure???
These are my tables:
Table # 1:tbl_bug_severity
bug severity
low
medium
critical
Table #2: tbl_bug_status
bug_status
null
found
infound
null
Table #3: tbl_bug_type
bug_type
interface
programming
cost
management
And I need the tables to become:
Table #1:
bug_severity bug_status bug_type
with all the data inside the columns.
June 28, 2005 at 9:42 am
This would denormalize the db, you simply need to select the data correctly when presenting it using inner joins. There's no need the ship all this data in the same table.
June 28, 2005 at 9:50 am
Remi is right. What is your objective? It is easy to write a SELECT query to display the data how you want. Or you can create a permanent VIEW of the data that displays the data in this way for anyone to access.
If you merge the table-data as you are suggesting and then your bug severity changes from 'low' to 'slight', for example, you would need to amend all occurences of 'low' to implement the change. Tedious and prone to error. Leaving it as it is means that only one occurence need be amended and is certainly good practice.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply