sql server dts: Import Flat File Without Duplicates -- sql server dts

Import Flat File Without Duplicates

Jake_adl
5/28/2006 4:47:01 PM

sql server dts:

I have a flat file with customer information that looks like:

Name, Suburb, Postcode
John, Adelaide, 5000
Mary, Adelaide, 5000
Mark, Melbourne, 3000
Elizabeth, Sydney, 2000
George, Adelaide 5000
Henry, Melbourne, 3000

I need to add the Suburb and Postcode to a related table but it has a Unique
Index on it for Suburb and Postcode.

How can I extract only one instance of each Suburb|Postcode, ie:
Adelaide, 5000
Melbourne, 3000
Sydney, 2000

and insert those values into the Regions table before importing the customer
details into the Customer table?

Re: Import Flat File Without Duplicates

JXStern
5/29/2006 12:00:57 PM

On Sun, 28 May 2006 16:47:01 -0700, Jake_adl
[quoted text, click to view]

You *could* write some fancy DTS transform code to parse each row and
build the suburb table on the fly,

but it's probaby better to (1) load the whole file into a staging
table, (2) run a query like "insert into regions_table select distinct
suburb, postcode from staging_table", (3) copy the data from the
staging table into the customer table with something like "insert into
customer select name, postcode_id from staging s inner join
regions_table r on s.suburb=r.suburb and s.postcode=r.postcode".

Have a nice day!

Josh