Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parsing tab selimited text into SQL table


Parsing tab selimited text into SQL table

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
Lisa Cherney (3/14/2012)
If you need to do this from a .net application, load your data from the file into a DataTable and then use the SQLBulkCopy class (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx) to move the data to your database.

Caveat to this approach: make sure the memory on your server is ample, the generic implementation of this technique requires that the entire data file be loaded into memory before it is bulk copied to the server.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44996 Visits: 39880
Ummmm... that class has a batch size parameter to it. I'm not a C# programmer but wouldn't that prevent the whole 9 yards from being loaded at once?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
The batch size comes into play when loading the data into the database which is downstream from the issue I am highlighting.

The SqlBulkCopy.WriteToServer method (4 overloads) is how the class is told to begin loading the data to the database. The most common of the three overloads is the one that accepts an ADO.NET DataTable, the WriteToServer(DataTable) method, and that DataTable by definition is a memory-resident object containing the data to load, the entire file in the most generic case. That is the most common way to leverage the SqlBulkCopy class and how the overwhelming majority of the basic internet tutorials show it.

The most interesting of the three overloads to me is the one that accepts an IDataReader. The example on MSDN alludes to the original intent of this overload which was to enable table-to-table data copying by passing in a SQL ExecuteReader (executes a SELECT from one database to load into another), a very powerful and handy amethod indeed. With only a few lines of code we can copy all data from all tables in one database to a database with the same schema on another instance (database copy wizard anyone?).

That said, I would find it much more interesting if one could easily pass an IDataReader hooked up to a flat-file reader. Then you would have something analogous to a subset of the bcp.exe functionality written in .NET, but I have not tried it nor looked to see if anyone has gone there mainly because we have SSIS, bcp.exe, BULK INSERT and many other tools already capable of doing the task and I have never been boxed into an environment where I would need such a thing.

edit: excuse me, there are 4 WriteToServer overloads, not 3

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44996 Visits: 39880
Thanks, Orlando.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search