dcsimg
Results 1 to 2 of 2

Thread: Populating a DB from 2 tables (Dynamic)

  1. #1
    Join Date
    Apr 2005
    Location
    Perth Australia
    Posts
    1

    Question 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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •