Custom Data Transfer from old un-normalize DB to n

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Custom Data Transfer from old un-normalize DB to n

Author

Topic

user323
Starting Member

8 Posts

Posted - 2011-10-05 : 23:21:39

Greetings and Dear All,

my boss wants me to do a Data Transfer from an old SQL Server DB to a new SQL Server DB.
the issue is, the schema in the new DB is highly normalized where the data will be stored in such a way that many lookup table are used unlike the old un-normalized DB where alot of data is bunch into one table.

my boss wants me to write a Windows program to essentially marshall, transform and transfer the data from the old unnormalized DB to the new normalized DB with lookup tables for datatypes. In addition, he wants me to using an XML file for mapping the various columns from the old to the new db.

i never done this before, if u guys have experience and ideas on this, please provide some pointers, tutorials, sample codes and such.

Pls help.... :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-05 : 23:30:13

Since it is all within SQL Server, you don't need a separate Windows program to do it. It can be accomplish with just T-SQL script.

As the old schema is different from new schema, you will probably need to select from more than 1 tables from the old schema (old DB) and insert into the table in new schema (new DB)

basically, it is just INSERT INTO . . SELECT .. FROM . ..


INSERT INTO newdb.dbo.new_table( <column list> )
SELECT <column list>
FROM   olddb.dbo.old_table1 t1
       INNER JOIN olddb.dbo.old_table2 t2 ON t1.somecol = t2.somecol
       . . . 
WHERE  . . .

KH
[spoiler]Time is always against us[/spoiler]

user323
Starting Member

8 Posts

Posted - 2011-10-06 : 00:26:37

thats seems to work...

however, what about when i have to splice up the column data from the old db and into different tables in the new db where it is normalize to way that it uses lookup table to store the original column data by its new lookup table ID or new Primary Key or Foreign Key ID?

I am confusing myself as well. What I mean is that the new db now uses lookup tables.

i am not good with complex SQL like the INNER JOINS in the subquery structure like you showed me. pls provide some tutorial on these complex SQL stuff and ideas on my situation as i described.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 01:59:26

just search (google or bing) for sql tutorial. There are lots of it available on the net

KH
[spoiler]Time is always against us[/spoiler]

user323
Starting Member

8 Posts

Posted - 2011-10-06 : 02:02:59

what about my ques. on where i need to transform the data from old unnormalized data into new db tables where the lookup ids from lookup tables replaces the the data from the old db thats put into the new db table?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 02:08:25

quote:
Originally posted by user323

what about my ques. on where i need to transform the data from old unnormalized data into new db tables where the lookup ids from lookup tables replaces the the data from the old db thats put into the new db table?

Can't answer you on this without further information like schema of the old and new db, the transformation rule etc

KH
[spoiler]Time is always against us[/spoiler]

user323
Starting Member

8 Posts

Posted - 2011-10-06 : 02:19:49

for example

//////////////////
old db

table user
columns userID, city, state, country
/////////////////

/////////////////
new db

table user
columns userID, cityID, stateID, countryID

table cityType
columns cityID(PK), cityName

table stateType
columns stateID(PK), stateName

table countryType
columns countryID(PK), countryName
//////////////////////

this is an example where the new db are normalized w/ data by having lookup table in my situation?

so could u help me on such situation? tnx in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 02:27:23

what is the data type of those ID column like cityID, stateID ?

Is it an identity column ?

KH
[spoiler]Time is always against us[/spoiler]

user323
Starting Member

8 Posts

Posted - 2011-10-06 : 02:32:12

the ID columns are Primary Keys with auto incremented numbers for each new row w/ corresponding names. Like each city with have their unique cityID number and that serves as Primary Key as well.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 02:44:54

1. first you need to transfer the city, state etc over.
in new DB


insert into cityType (cityName)
select distinct city
from   old_db.dbo.user

insert into stateType (stateName)
select distinct state
from   old_db.dbo.user

insert into countryType (countryName)
select distinct country
from   old_db.dbo.user

2. then you transfer the customer record and referencing the city, state table etc for the ID


insert into user (userID, cityID, stateID, countryID)
select u.userID, c.cityID, s.stateID, n.countryID
from   old_db.dbo.user u
       inner join cityType c     on u.city    = c.cityName
       inner join stateType s    on u.state   = s.stateName
       inner join countryType n  on u.country = n.countryName

KH
[spoiler]Time is always against us[/spoiler]

user323
Starting Member

8 Posts

Posted - 2011-10-06 : 02:50:18

did u mistype some of the codes?

user323
Starting Member

8 Posts

Posted - 2011-10-06 : 02:53:14

u have this...

insert into cityType (cityName)
select distinct country
from old_db.dbo.user

shouldn't it be this...

insert into cityType (cityName)
select distinct city
from old_db.dbo.user

//////////////////////////////

u have this (where u use the "c" for cityType and countryType ...

insert into user (userID, cityID, stateID, countryID)
select u.userID, c.CityID, s.stateID, c.countryID
from old_db.dbo.user u
inner join cityType c on u.city = c.cityName
inner join stateType s on u.state = c.stateName
inner join countryType c on u.country = c.countryName

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 03:18:46

quote:
Originally posted by user323

u have this...

insert into cityType (cityName)
select distinct country
from old_db.dbo.user

shouldn't it be this...

insert into cityType (cityName)
select distinct city
from old_db.dbo.user

//////////////////////////////

u have this (where u use the "c" for cityType and countryType ...

insert into user (userID, cityID, stateID, countryID)
select u.userID, c.CityID, s.stateID, c.countryID
from old_db.dbo.user u
inner join cityType c on u.city = c.cityName
inner join stateType s on u.state = c.stateName
inner join countryType c on u.country = c.countryName