June 19, 2007 at 4:26 pm
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?
June 19, 2007 at 4:31 pm
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.
June 20, 2007 at 7:58 am
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
June 20, 2007 at 11:01 am
Thanks a ton Shri
June 20, 2007 at 11:29 am
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?
June 21, 2007 at 1:03 am
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