• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

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
0
pommoz
Asked:
pommoz
2 Solutions
 
Renante EnteraCommented:
Hi pommoz!

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,Address,DOB)
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 :-)
0
 
MannaraprayilCommented:
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')

0
 
Renante EnteraCommented:
Ooopss...  I have a correction.

INSERT INTO NewTable(uniqueId,Name,Address,DOB)
SELECT t1.uniqueId, t1.Name, t1.Address, CASE when t2.Type = 'DOB' then t2.Setting else NULL END
FROM Table1 t1
INNER JOIN Table2 t2
  ON t2.uniqueId = t1.uniqueId
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
MannaraprayilCommented:
Do you need to insert the record whose type is 'POA'...?

0
 
rafranciscoCommented:
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
0
 
Scott PletcherSenior DBACommented:

INSERT INTO newTable
SELECT table1.uniqueId, table1.Name, table1.Address, table2.Setting
FROM table1
LEFT OUTER JOIN table2 ON table2.uniqueId = table1.uniqueId AND table2.type = 'DOB'
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now