DTS Import to different tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Import to different tables?

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-03-09 : 23:35:05

Hi,

Here is my scenario... I'm working in a project where the client will give us datas in the form of either csv or txt file(we are still not sure abt the file format yet, Their DB is Oracle). It will be a one single file and with a minimum of million records. I have to extract those datas from the file, and import only the related columns in my tables according to my db design. I have atleast 12 tables now.

So my questions are
Can I do this with DTS?
If yes, how can import to different tables?
What are the other options available?
What could be the best source file format for this? (Txt,XML etc)
This process will happen every month. The size of data might differ.

Any thoughts?

Thanks and Regards,
Karunakaran

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-10 : 18:03:29

Import all of the data into a staging table that matches the format of the csv file. Then use T-SQL to normalize the data into your 12 tables.

Tara

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-03-10 : 23:16:23

Thanks for the idea. I'll try with it and let you people know how it goes.

Karunakaran

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-03-14 : 05:18:33

Some more details..

We have atleast 2 files(diff in structure) which needs to be imported to the database. I have to import these 2 files to the 12 tables in the database.

As Tara said, I have imported the 2 tables to 2 temp tables. Now all I have to do is select the respective columns and import them to the 12 tables wherever neccessary. But now the client says, if certain conditions are not satisfied then I have to skip that record and process the next one...Essentially they are talking about processing and importing records one by one.

Whats the best way I can do this?

Any links to articles how to do this would be great full.
Can I do this in DTS itself or should I go for something else?

Karunakaran

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-14 : 07:21:13

row by row processing is almost always a bad idea.
could you do something like this:

insert targettable(cola,colb,colc,,,)
select col1,col2,col3,,, from stagetable
where not(<certain conditions>)

rockmoose

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-03-14 : 08:23:25

I think posting the db structure might help to understand my problem. I'll post it soon. For starters, Here is my list of columns in the 2 staging tables:

TempTable ORD
Bill To Cust
Ship To Cust
VAR Cust
Reseller Cust
End User Cust
End User Cust No
Cust Major Geo
Order No
Order Date
Year
Quarter
Month
End User Cust No1
Bookings Unit Qty
Sale SKU
Sale SKU Desc
Product Family
Product Offering
OS Family
Product Version No
Revenue Type
Bookings Dollar Amt
Bkngs Txn Curr Amt
Txn Currency Code
Bookings Disc Pct

TempTable IBR
Org ID
Current Order Bill To Cust
Current Order Ship To Cust
End User Cust
End User Cust No
End User Cust Line 1 Addr
End User Cust Line 2 Addr
End User Cust Line 3 Addr
End User Cust City
End User Cust Ctry Name
End User Cust Postal Code
Original Order Date
Original Order No
Original PO No
Original Ref No
Current Order Date
Current Order No
Current PO No
Order Type
Current Ref No
End User Contact
End User Contact Email Addr
End User Contact Phone No
Lic Qty
SKU
Prod Version
SKU Desc
Prod Family Code
Prod Family Desc
Prod Offering Desc
OS
Prod Pricing Tier
Prod Install Status
Prod Host ID
Prod Serial No
Svc Order No
Svc Prod
Svc Txn Res Status Code
Svc Prod Item Start Date
Svc Prod Item End Date
Svc Txn Eff Date
Svc Txn Exp Date
Svc Txn Dur Qty
Svc Covered Flag

UserTable CustomerContact
CustomerID - End User Cust No
ContactFName - End User Contact
ContactLName - This doesnt have a matching value in either of the temp tables and for now will be always null.
ContactCity - End User Cust City
ContactCountry - End User Cust Ctry Name
ContactPinCode - End User Cust Postal Code

End User Customer No has to match with the 2 temp tables and End User Contact should be distinct

I tried an insert statement with select using distinct clause for End User Contact, but got some errors...
Almost this how all the 12 tables need to be pushed datas from the temp tables...

Karunakaran

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-15 : 08:34:18

Show us the insert statement.
This is probably just a matter of writing the correct SQL for loading each of the 12 tables.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources