pommoz
asked on
Populating a DB from 2 tables (Dynamic)
Hi
I am quite new to the complexities of MS SQL and have a problem, I would like to resolve. I have 2 tables with a unique identifier in both and want to populate a new table with information from both, but the second table I would like to populate just some fields that have a DOB eg
Table 1:
uniqueId
Name
Address
Table2:
uniqueId
Type
Setting
example of content for Table 2:
uniqueId Type Setting
123 DOB 03/04/74
234 TFN 12345678
567 POA Mr Smith
So the new table needs to be populated with a ll of info in table 1 and has a new field called DOB so only the clients with a DOB should populate this field, if the client in Table 1 has a TFN reference, this record should be added to the new table but no value needs to be entered eg
123 Chris Smith 1 high street 03/04/74
234 Jon brown 2 high terrace <Null>
Cheers
pommoz
I am quite new to the complexities of MS SQL and have a problem, I would like to resolve. I have 2 tables with a unique identifier in both and want to populate a new table with information from both, but the second table I would like to populate just some fields that have a DOB eg
Table 1:
uniqueId
Name
Address
Table2:
uniqueId
Type
Setting
example of content for Table 2:
uniqueId Type Setting
123 DOB 03/04/74
234 TFN 12345678
567 POA Mr Smith
So the new table needs to be populated with a ll of info in table 1 and has a new field called DOB so only the clients with a DOB should populate this field, if the client in Table 1 has a TFN reference, this record should be added to the new table but no value needs to be entered eg
123 Chris Smith 1 high street 03/04/74
234 Jon brown 2 high terrace <Null>
Cheers
pommoz
Please try this
INSERT INTO newTable
SELECT F.*, CASE S.Type WHEN 'DOB' THEN S.Setting ELSE NULL END
FROM Table1 F INNER JOIN Table2 S ON F.UniqueId = S.uniqueID
WHERE Type IN ('DOB', 'TFN')
INSERT INTO newTable
SELECT F.*, CASE S.Type WHEN 'DOB' THEN S.Setting ELSE NULL END
FROM Table1 F INNER JOIN Table2 S ON F.UniqueId = S.uniqueID
WHERE Type IN ('DOB', 'TFN')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you need to insert the record whose type is 'POA'...?
If your second table contains more than 1 record for each uniqueID in table1, try this:
SELECT A.UniqueID, A.Name, A.Address, B.Setting
INTO YourNewTable
FROM Table1 A LEFT OUTER JOIN (SELECT UniqueID, Setting FROM Table2 WHERE Type = 'DOB') B
ON A.UniqueID = B.UniqueID
SELECT A.UniqueID, A.Name, A.Address, B.Setting
INTO YourNewTable
FROM Table1 A LEFT OUTER JOIN (SELECT UniqueID, Setting FROM Table2 WHERE Type = 'DOB') B
ON A.UniqueID = B.UniqueID
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am assuming that you have a structure for your new table which is like this :
NewTable :
uniqueId
Name
Address
DOB
To insert record on that new table, you will simply do something like this :
INSERT INTO NewTable(uniqueId,Name,Add
SELECT t1.uniqueId, t1.Name, t1.Address, CASE when t2.Type = 'DOB' then t2.Type else NULL END
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.uniqueId = t1.uniqueId
Hope this helps you. Just try it.
Goodluck!
eNTRANCE2002 :-)