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.
| Author |
Topic |
|
thebody
Starting Member
4 Posts |
 Posted - 2009-02-06 : 17:13:42
|
| Hi folks, I'm wondering if there's a guru out there who can assist with my noobish TRANSACT SQL attempts. I'm using SQL 2005 and trying to do a (I thought) rather simple load of all files from one directory into a table. First problem was getting all the info into the table. I've had to set all the fields as varchar, but some of them need processing later anyway, so I have a Staging table call New_Call_Log. I'm loading the data throught the following code.BULK INSERT New_Call_Log FROM 'C:\Data\CDR_Detail_20090103.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )GOWhich works well, but I want to be able to achieve two things:1. I'd like to load all files in the directory(and then move or rename them once loaded if possible). THey all follow the same structure - CDR_Detail_YYYYMMDD.2. I only want unique instances of each row so if the same file is attempted to load twice, it will only add data where the first column is unique to the table. I can achieve this by setting the first column as a primary key, however this causes an error that stops the loading of the rest of the file. In theory that should be fine because if the first record of a table is already loaded then so should all the other rows be. But I've learned from experience that assuming things like that isn't always a good idea. |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-02-06 : 17:20:30
|
| You need to do this in SSIS. There is something in there you can use to point it to a directory and it will look up all of the files in there. SSIS is your ticket I believe2 cents |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-06 : 18:16:18
|
| You need to use Foreachloop Container in SSIS which will see all files in directory and load to tables. Use Filesystem task to archive the files to different folders as you are done. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-07 : 10:02:12
|
| http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx |
|
|
|
thebody
Starting Member
4 Posts |
Posted - 2009-02-08 : 03:30:11
|
| Thanks - that is almost exactly what I'm after. However when I get to this step:The important part of this dialog is highlighted and that is "Expressions". Click on the ellipses and we will be taken through to the next screen where we can start to create the expression. In the screen that follows, from the Property column drop the list down and choose ConnectionString Connection string is not an option. Do I need to enable that somewhere else or something to have it as an option.Cheers, |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-08 : 21:18:31
|
| nope you can choose all the property associated with task inside expression builder which includes connection string as well. refer below link toohttp://www.mssqltips.com/tip.asp?tip=1084 |
|
|
|
|
|
|
Data from all files in a directory