Data conversion

Hi,
Usually how people do the data conversion? For example, I have a well
defined database, the new client use another similar relations database and
have few years data in it, he want to migrate to my system, so I need to
convert his data into my database, customer, appointment, transactions,
etc.)
Is it a way to handle this? Please advice.js wrote:
> Hi,
> Usually how people do the data conversion? For example, I have a well
> defined database, the new client use another similar relations
> database and have few years data in it, he want to migrate to my
> system, so I need to convert his data into my database, customer,
> appointment, transactions, etc.)
>
> Is it a way to handle this? Please advice.
If this is a one-off scenario, you need to scope out the migration with
a complete mapping of the source and destination databases and any
scrubbing that needs to occur to the data, design the migration code,
test it repeatedly on a test server and then run it in production when
it's ready. You can use DTS to help with the migration if the data
scrubbing is involved. If not, you may be able to do this using T-SQL.
David Gugick
Imceda Software
www.imceda.com|||Thanks David,
"David Gugick"
> js wrote:
> If this is a one-off scenario, you need to scope out the migration with a
> complete mapping of the source and destination databases and any scrubbing
> that needs to occur to the data, design the migration code, test it
> repeatedly on a test server and then run it in production when it's ready.
> You can use DTS to help with the migration if the data scrubbing is
> involved. If not, you may be able to do this using T-SQL.
>
Is it any articles available to learn?
This is not a one-off scenario (depend on the clients system) , the business
logics in general are deal the same, for example, custom info, order info,
but they can represent different in the field name and size
I did lots of data conversion use queries before. Just wander there are good
ways (or tools) out there can simplify the process?
Use DTS in my case is slow, I could use it wrong, because I always use it to
process the records by row to massage the data. Using T-SQL (deal with
large set of data), it is faster. Is it right?

Data Cleansing

We have just started a new data cleansing project. One of the aims of the
project is to identify similar records so that we can eliminate them. The
problem lies in how to identify those similar records.
Consider a table that has the columns: (ID, name …). A record whose name is
“XYZ” is similar to “XY”. Moreover “XYZ Company” should be similar to “XYZ
Co”.
We have to ideas:
1) For each record in the table, get the records that match it based on a
function, candidate function. Then for each match get a percentage that
indicate how close the 2 records are, scoring function.
2) For each record create a function that gives a score to a record, Sum of
its name characters ASCII codes + any other function, items with nearby
scores are considered similar. There could be more than one function; 2
functions means search in 2D, 3 functions means search in 3D.
Can you lead me to ideas/articles/books that show how to identify the
columns that will be included in the scoring functions, or that illustrate a
better way of searching?
Note:
Matreials needn't be targeted at SQL Server, we can do some part in SQL
Server and the other in C#.
On 28 Apr, 09:52, Shehab Kamal <ShehabKa...@.discussions.microsoft.com>
wrote:
> We have just started a new data cleansing project. One of the aims of the
> project is to identify similar records so that we can eliminate them. The
> problem lies in how to identify those similar records.
> Consider a table that has the columns: (ID, name ...). A record whose name is
> "XYZ" is similar to "XY". Moreover "XYZ Company" should be similar to "XYZ
> Co".
> We have to ideas:
> 1) For each record in the table, get the records that match it based on a
> function, candidate function. Then for each match get a percentage that
> indicate how close the 2 records are, scoring function.
> 2) For each record create a function that gives a score to a record, Sum of
> its name characters ASCII codes + any other function, items with nearby
> scores are considered similar. There could be more than one function; 2
> functions means search in 2D, 3 functions means search in 3D.
> Can you lead me to ideas/articles/books that show how to identify the
> columns that will be included in the scoring functions, or that illustrate a
> better way of searching?
> Note:
> Matreials needn't be targeted at SQL Server, we can do some part in SQL
> Server and the other in C#.
Data cleansing is supported by Integration Services using the Fuzzy
Lookup feature:
http://msdn.microsoft.com/msdnmag/issues/05/09/sqlserver2005/default.aspx
You'll find that other integration tools offer the same kind of
functionality.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||The ETI idea is brilliant. Thanks for the link

Data Cleansing

We have just started a new data cleansing project. One of the aims of the
project is to identify similar records so that we can eliminate them. The
problem lies in how to identify those similar records.
Consider a table that has the columns: (ID, name â?¦). A record whose name is
â'XYZâ' is similar to â'XYâ'. Moreover â'XYZ Companyâ' should be similar to â'XYZ
Coâ'.
We have to ideas:
1) For each record in the table, get the records that match it based on a
function, candidate function. Then for each match get a percentage that
indicate how close the 2 records are, scoring function.
2) For each record create a function that gives a score to a record, Sum of
its name characters ASCII codes + any other function, items with nearby
scores are considered similar. There could be more than one function; 2
functions means search in 2D, 3 functions means search in 3D.
Can you lead me to ideas/articles/books that show how to identify the
columns that will be included in the scoring functions, or that illustrate a
better way of searching?
Note:
Matreials needn't be targeted at SQL Server, we can do some part in SQL
Server and the other in C#.On 28 Apr, 09:52, Shehab Kamal <ShehabKa...@.discussions.microsoft.com>
wrote:
> We have just started a new data cleansing project. One of the aims of the
> project is to identify similar records so that we can eliminate them. The
> problem lies in how to identify those similar records.
> Consider a table that has the columns: (ID, name ...). A record whose name is
> "XYZ" is similar to "XY". Moreover "XYZ Company" should be similar to "XYZ
> Co".
> We have to ideas:
> 1) For each record in the table, get the records that match it based on a
> function, candidate function. Then for each match get a percentage that
> indicate how close the 2 records are, scoring function.
> 2) For each record create a function that gives a score to a record, Sum of
> its name characters ASCII codes + any other function, items with nearby
> scores are considered similar. There could be more than one function; 2
> functions means search in 2D, 3 functions means search in 3D.
> Can you lead me to ideas/articles/books that show how to identify the
> columns that will be included in the scoring functions, or that illustrate a
> better way of searching?
> Note:
> Matreials needn't be targeted at SQL Server, we can do some part in SQL
> Server and the other in C#.
Data cleansing is supported by Integration Services using the Fuzzy
Lookup feature:
http://msdn.microsoft.com/msdnmag/issues/05/09/sqlserver2005/default.aspx
You'll find that other integration tools offer the same kind of
functionality.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The ETI idea is brilliant. Thanks for the link

Data Cleansing

We have just started a new data cleansing project. One of the aims of the
project is to identify similar records so that we can eliminate them. The
problem lies in how to identify those similar records.
Consider a table that has the columns: (ID, name …). A record whose name i
s
“XYZ” is similar to “XY”. Moreover “XYZ Company” should be simil
ar to “XYZ
Co”.
We have to ideas:
1) For each record in the table, get the records that match it based on a
function, candidate function. Then for each match get a percentage that
indicate how close the 2 records are, scoring function.
2) For each record create a function that gives a score to a record, Sum of
its name characters ASCII codes + any other function, items with nearby
scores are considered similar. There could be more than one function; 2
functions means search in 2D, 3 functions means search in 3D.
Can you lead me to ideas/articles/books that show how to identify the
columns that will be included in the scoring functions, or that illustrate a
better way of searching?
Note:
Matreials needn't be targeted at SQL Server, we can do some part in SQL
Server and the other in C#.On 28 Apr, 09:52, Shehab Kamal <ShehabKa...@.discussions.microsoft.com>
wrote:
> We have just started a new data cleansing project. One of the aims of the
> project is to identify similar records so that we can eliminate them. The
> problem lies in how to identify those similar records.
> Consider a table that has the columns: (ID, name ...). A record whose name
is
> "XYZ" is similar to "XY". Moreover "XYZ Company" should be similar to "XYZ
> Co".
> We have to ideas:
> 1) For each record in the table, get the records that match it based on a
> function, candidate function. Then for each match get a percentage that
> indicate how close the 2 records are, scoring function.
> 2) For each record create a function that gives a score to a record, Sum o
f
> its name characters ASCII codes + any other function, items with nearby
> scores are considered similar. There could be more than one function; 2
> functions means search in 2D, 3 functions means search in 3D.
> Can you lead me to ideas/articles/books that show how to identify the
> columns that will be included in the scoring functions, or that illustrate
a
> better way of searching?
> Note:
> Matreials needn't be targeted at SQL Server, we can do some part in SQL
> Server and the other in C#.
Data cleansing is supported by Integration Services using the Fuzzy
Lookup feature:
http://msdn.microsoft.com/msdnmag/i...05/default.aspx
You'll find that other integration tools offer the same kind of
functionality.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The ETI idea is brilliant. Thanks for the link

daily transfer from oracle schema to similar SQL-Server

I have some small (<5000 records) Oracle tables that I need to transfer daily to SQL-Server.

The old legacy standalone Oracle system here in Hong Kong must still be used to enter property data, so I cannot change it. The idea is to get this data in every day into an SQL-Server. I am sure they can be connected over the company's LAN if not so already. The aim is to then put together a basic front-end for this data. Both systems although fairly similar (property web site data), their schemas are fixed and the SQL-Server schema is in use by another branch of the company for a U.K. property site. The Hong Kong team want me to get their legacy data in to this new system every day and then do a similar web site for their HK data.

Is there a way where I can, say automate DTS to run every day on the SQL-Server machine, get the Oracle tables over the LAN to SQL-Server, then write scheduled stored procedures that massage the data from one schema to the other (I will have to lose a bit of data, since they are not exactly the same tables and fields, though with property data, it is all pretty much the same).

I once did this using ASP (after getting the data into a CSV file from an ACCESS database). The script looped through each line of data and put it into the relevant table.

Anyone got any ideas? Much appreciated. Itry using a linked server to transfer the data from the oracle server. It is much more faster . Write a stored procedure for the same and then schedule it as a job|||Originally posted by Enigma
try using a linked server to transfer the data from the oracle server. It is much more faster . Write a stored procedure for the same and then schedule it as a job

Thanks. I will meet their IT admin guy next week. So, I will have an Oracle DB and a SQL-Server DB. I am familiar with Enterprise manager, I can add remote SQL-Server groups. How would I go about setting up a 'Link Server'. In my main job, we use Lotus Enterprise Integrator (LEI), a handy tool that transfers data from Lotus Notes to anything else, but not sure of a tool for my needs.

I'm more from a programming background. Can you explain the Link Server setup. The scheduled SP's I've done before.

Many Thanks|||You can look in books online and search for "linked server". Also, in the enterprise manager under the security directory are the linked servers visible.|||Originally posted by jora
You can look in books online and search for "linked server". Also, in the enterprise manager under the security directory are the linked servers visible.

Thanks guys, I've just trawled though a few of these pages. Seems DTS is a safe option. I've just started on Enterprise manager, trying to create a package in the meantime to transfer data every day from some test SQL table from one DB to another DB on our sql-server. Looks like I should go over to the client next week, and setup a DTS package on their SQL-Server, try and connect to their Oracle tables and go from there.

I'll check ot the LINK server, I'll need to connect to their legacy Server

Cheers