How to import multiple text files (residing in single folder) into SQL Server table? I know how to import single file but not sure how multiple files could be loaded? Pls. guide.
Thanks,
HShah
I am assuming that thefiles all have the same structure.
There are a number of options availableto you. See here:
Processing data from multiple files all at once
(http://blogs.conchango.com/jamiethomson/archive/2006/10/14/SSIS_3A00_-Processing-data-from-multiple-files-all-at-once.aspx)
-Jamie
That is, if I say:
cmd1.CommandText = "UPDATE foo SET myVal = myVal - 1"
cmd2.CommandText = "SELECT myVal FROM foo"cmd1.Connection = myConn
cmd2.Connection = myConnmyConn.Open()
cmd1.ExecuteNonQuery() ' decrement myVal
intResult = CInt(cmd2.ExecuteScalar()) ' select value of myValmyConn.Close()
Do the UPDATE and the SELECT happen in the same transaction, or is it possible that someone else would have changed the value of myVal between these two calls?Nope. Each statement has an implicit transaction but to put them both under a single transaction you have to use an explicit transaction.
So yes, data could have changed between the statements.
Don|||Thanks for the clarification. So if I understand you right, this:
cmd.CommandText = "UPDATE ... ; SELECT @.myResult = ... "
cmd.ExecuteNonQuery()
would put them in the same transaction, which is what I want.|||Now I'm on shakier ground. I'm pretty sure that SQL Server still considers them to be separate statements, simply batched together. But not absolutely positive.
Any reason you don't want to just use an explicit transaction?
Don|||No -- I'd like to use an explicit transaction, but I'm not sure how. Can you give me an example of how to do it w/o moving the queries to a stored procedure?
Thanks!|||Well, you're opening yourself up to all kinds of security holes by using dynamic SQL like this. Stored procedures are much better way to go.
But you can either use transactions via dynamic SQL and batch statements, or using the SQLTransaction class in ADO.NET. The example in the .NET docs is pretty clear. For the latter, just new up a SQLTransaction object and use the connection object to begin the transaction. Then call either the Commit or Rollback methods of the transaction object.
But remember that your app won't be very secure unless you take all the steps to close the holes.
Don|||Thanks, Don. What kind of hacks am I risking? I'm using parameters to avoid sql-insertion attacks; are there other risks that come with using SqlCommands?|||Ah, you're using ADO.NET parameters? That wasn't clear from the snippets you've posted, although you had one @.Result showing. You're using parameters to provide the changeable values in the SQL, right?
If that's the case, you're probaly reasonably well protected from SQL injection, since ADO.NET passes the SQL and parameters separately to SQL Server, where they are inserted into the SQL.
Don
