Bring flat file to relational database 1

[Niv3k]

I have a flat file that has several non-relational fields, such city_due_1, city_due_2, city_due_3, city_due_4.

There is also garbage 1-4, sewer 1-4, and water 1-4.

I would like to normalize this a little by bringing these fields into their own seperate tables, for example:
bill_num int
quarter tinyint (1 - 4)
city_due currency

Any ideas on how to do this without using a cursor?

thanks, Kevin

 

[JohnDTampaBay]

Are you importing the data into a staging table in SQL before INSERTing the data into your "real" data tables?

If so, use a UNION or perform separate INSERT...SELECT statements:

INSERT INTO CityDue (BillNum, Quarter, City_Due)
SELECT BillNum, Quarter, City_Due_1
FROM StagingTable WHERE City_Due_1 IS NOT NULL
UNION
SELECT BillNum, Quarter, City_Due_2
FROM StagingTable WHERE City_Due_2 IS NOT NULL
UNION
SELECT BillNum, Quarter, City_Due_3
FROM StagingTable WHERE City_Due_3 IS NOT NULL
UNION
SELECT BillNum, Quarter, City_Due_4
FROM StagingTable WHERE City_Due_4 IS NOT NULL


I hope this helps. Good luck!



--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[Niv3k]

so THATS what the union operator does... Boy this would've come in handy years ago.

Thanka!

Kevin