Conversion

  • I am converting data from an old system to new system. I'm converting Client Master (which is denormalized). Here are some fields.

    client id - pk

    ......

    address 1

    address 2

    city

    state

    zip

    ......

    This will be converted to the new system which has 2 files. Client Master and Address. Here they are:

    client master:

    id - pk, identity

    cliend id - fk

    address:

    id - pk, identity

    client pointer - fk (into client master)

    ....

    address 1

    address 2

    city

    state

    zip

    ....

    During conversion, fo each client record read from the old system, two record need to be created. A client record and an address record. And while creating the address record, the client key needs to be known as it is plugged in as the client pointer.

    Is it possible to do so without writing a VB program? If yes, how? And more importantly, is it a good idea to do so than to write a VB program?

  • If you're generating new key values, output the new key and the old key using the OUTPUT clause and use it to seed the address record.

  • If I am not wrong with logic, you can have below t-sql script executed by sqlcmd rather than having a vb program to write the logic and execute. sqlcmd(OLEDB connetion) is much faster and provides n number of options for script executions moreover its recommanded tool from microsoft for sql server 2005. If you can work out same thing using t-sql no need to think of other unless its easy and guarantees performance.

    use tempdb

    create table old_client_table

    ( client_id int, address1 varchar(20))

    create table client_table(

      id int identity(1,1) primary key,

      client_id int)

    create table address_table (

     id int identity(1,1) primary key ,

     client_id int foreign key   references client_table(id),

     address1 varchar(20))

     

    insert into old_client_table select 1, 'Texsas'  union all   

    select 7, 'New England'  union all   

    select 5, 'Florida'  union all

    select 9, 'Hawai';

     

    --conversion

    insert client_table    

    select client_id from old_client_table

     

    insert into address_table

    select ct.id,ot.address1 from old_client_table ot join client_table ct

    on ot.client_id = ct.client_id

     

    select * from old_client_table

    select * from client_table

    select * from address_table

     

    drop table old_client_table

    drop table address_table

    drop table client_table

     

    Regards
    Shrikant Kulkarni

  • Thanks a ton Shri

  • I am trying to create a table and copy data from a different database to tempdb.

    use tempdb

    CREATE TABLE old_client_table

    AS (SELECT * FROM WarmSpringPRD.person)

    What am I doing wrong?

  • SQL Server does not have Create table xxx as select ... instead use select into or create table and then Insert into select statements. I always perfer to use select into as its faster and minimal logged operation.     

    use tempdb

    select object_name(object_id)as Oname 

    into my_table

    from master.sys.objects

     

    Regards
    Shrikant Kulkarni

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply