June 5, 2012 at 9:24 am
I have a as/40o file that I need to import into my SQL server, but this file has 123 rows and its use is to track billing efforts, since but per orderid a customer can only have 100 items and the way the records are stored in this file are:
OrderID OrderDate Item1 Item2 ..(to)..Item100, EffortID, EffortDate TotalItemsInvoiced
123 5/1/2012 4587 45879 101 6/5/2012 2
I need to create a new table and copy from this file one record per item but I have no clue yet how I'm going to do this my table should look like this.
OffortID, EffortDate, OrderID, ItmeID
101 6/5/2012 123 45879
101 6/5/2012 123 4587
PLEASE HELP:crying:
June 5, 2012 at 9:29 am
You will probably need a staging table to hold the original data. Use SSIS to import the data into a table in sql. Then you can parse each row into the individual rows. You could probably use UNPIVOT for the last step.
http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2012 at 9:50 am
You might be able to skip SSIS altogether and just pull the data into a staging table by using OpenRowset. Simple import jobs can often be done that way.
Beyond that idea, I'd go with the plan already presented: Import to Staging (by whatever means), then process into a normalized format.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 5, 2012 at 10:13 am
Thanks guys..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply