Populating a DB from 2 tables (Dynamic)

[pommoz]

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

[MAK]

use tempdb
go
create table Table1 (uniqueId int, Name varchar(100), Address varchar(500))
go
create table Table2 (uniqueId int, Type varchar(5),Setting varchar(12))
go
insert into table1 select 123, 'Chris
Smith', '1 high street '
insert into table1 select 234, 'Jon brown', '2 high ter'
insert into table1 select 567, 'John wang', 'Main st'
insert into table2 select 123, 'DOB', '03/04/74'
insert into table2 select 234, 'TFN', '12345678'
insert into table2 select 567, 'POA', 'Mr Smith'
go
create table newtable (uniqueId int, Name varchar(100), Address varchar(500),
DOB varchar(12))
go
--select a.uniqueid,a.name,a.address, DOB = case when isdate(setting)=1 and
b.type ='DOB' then setting else NULL end from
table1 a join table2 b on a.uniqueid=b.uniqueid

--Insert
insert into newtable
select a.uniqueid,a.name,a.address, DOB = case when isdate(setting)=1 and
b.type ='DOB' then setting else NULL end from
table1 a join table2 b on a.uniqueid=b.uniqueid