Conversion

Question

Conversion

yaip

SSCrazy

Points: 2656
More actions
June 19, 2007 at 4:26 pm

#174691

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?

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

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

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.

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 2

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

yaip SSCrazy Points: 2656 More actions · Answer 3

yaip

SSCrazy

Points: 2656

June 20, 2007 at 11:01 am

#714179

Thanks a ton Shri

yaip SSCrazy Points: 2656 More actions · Answer 4

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?

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 5

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