Importing Data

[Jonathan Wood]

I'm new to SQL stuff. All I have is MS SQL Express that came with Visual
Studio 2005, and SQL Server Management Studio Express, which I downloaded
and installed.

I've created a new table, and I have a comma-delimited text file of data I
want in that table.

Can someone tell me the least painful way to import that data into the
table?

Thanks.

 

[TAJ]

The best way is to Create a table in the database that represents the data
structure. Then use the filestream in System.IO namespace and read the file.
you will have to use some creativity to read until it reaches a comma then
mark that as a field. Do that to create a datarow in a datatable or
arraylist then write that row to the database.

That is one way and in that I would do all your error checking and other
logic required to ensure you are importing what you want. But this uses the
basics and is pretty easy. If you look at other built in methods those can
make your life easier but would take longer to explain.

Tom

 

[Cor Ligthert[MVP]]

Jonathan,

A comma seperated file contains datafields, the best way is to set those
datafields one by one in your table.

However if you see the rows as objects, then you can by instance zip them
and set them then as a kind of image/blog whatever you name it in the SQL
server. However as least you have then to make from your CSV file a byte
array.

Be aware that it is not one simple instruction to do the latter, doing it
field by field is much easier and as well better to use.

Cor

 

[Jonathan Wood]

Thanks, but I didn't even understand what you mean. I was hoping there was
an import command somewhere, like there is in Access.

I'll write code if I have to, if I can figure out how.

 

[Cor Ligthert [MVP]]

OK,

Thanks, but I didn't even understand what you mean. I was hoping there was
an import command somewhere, like there is in Access.

I'll write code if I have to, if I can figure out how.

Make from your CSV a dataset with datatable(s) in it using OleDB,

Then create the update from that to your database.
(You can use the commandbuilder for that).
Be aware that there is not a method, which can make from a dataset a
DataBase.

Be aware that the dataset has to have the same schema as your database.
In case that it is not equal you can use the FillSchema method.

-----------------------------------------------------------
For Bill.

If you want to do it only one time, then use by hand one of the by Bill so
often mentioned methods., he knows probably more of them then me.

Bill will give you the address of his book, in my opinion the most practical
books about AdoNet that I know.
(I don't get fees from Bill)

Cor

 

[William \(Bill\) Vaughn]

Ah, none of the above.
ADO and ADO.NET (and all of its predecessors after DB-Library) are QUERY interfaces, not designed for bulk operations. It's a waste of time and resources to try to use the System.Data classes to import more than a few rows of data. If the data is being "manufactured" it still makes sense to write it to a delimited file and stream it in via one of the bulk copy techniques discussed below.

When it's time to import data into SQL Server, the fastest (by an order of magnitude or more) is to use the TDS bulk copy approach. This is exposed in a number of ways including:
a.. The BCP commandline utility.
b.. TSQL BulkCopy operations
c.. DTS/SSIS scripts
d.. ADO.NET SqlBulkCopy method.
All of these techniques can import data from anything that can be read by a .NET data provider, an OLE DB data provider, and ODBC driver or your own custom-written data provider that exposes a DataReader. This means other database tables, flat files, text files, delimited files--almost anything. These routines can import millions of rows in no time. Some Oracle customers buy SQL Server just to get SSIS.

My latest book details how to use SqlBulkCopy. BOL shows how to do the rest. I'll be happy to show it to you if you come to one of my sessions this week at DevConnections in Vegas or at my workshop in Vancouver BC on the 26th (see www.devweek.com for availability).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Jonathan Wood]

Bill,
ADO and ADO.NET (and all of its predecessors after DB-Library) are QUERY
interfaces, not designed for bulk operations. It's a waste of time and
resources to try to use the System.Data classes to import more than a few
rows of data.
Can you explain why it's a waste of time? Looking at what I've found so far,
that approach would probably take me, personally, about hundredth of the
time requires to figure out the other stuff. And that takes into
consideration the fact that I've been so far unable to find a simple example
showing how to add records to a database using the System.Data classes.
When it's time to import data into SQL Server, the fastest (by an order of
magnitude or more) is to use the TDS bulk copy approach. This is exposed in
a number of ways including:
a.. The BCP commandline utility.
b.. TSQL BulkCopy operations
c.. DTS/SSIS scripts
d.. ADO.NET SqlBulkCopy method.
All of these techniques can import data from anything that can be read by a
..NET data provider, an OLE DB data provider, and ODBC driver or your own
custom-written data provider that exposes a DataReader. This means other
database tables, flat files, text files, delimited files--almost anything.
These routines can import millions of rows in no time. Some Oracle customers
buy SQL Server just to get SSIS.
Okay...

I guess TDS stands for something but "tds bulk copy" brings up one a single
result on Google, and that wasn't much help.

Let's try this: Can you tell me which of these is available with what I have
(VS2005 and SQL Server Management Studio Express)? Also, any spec of details
on using any of these would be a tremendous help. Maybe even a link of some
sort.

And I have your book "Hitchhiker's Guide to Visual Studio and SQL Server."
Am I understanding correctly that this doesn't step through what I want to
do anywhere? Or how about an example that shows how to add records to a
database from C# (with no controls, Access, or anything else other than C#
and SQL)?
My latest book details how to use SqlBulkCopy. BOL shows how to do the rest.
I'll be happy to show it to you if you come to one of my sessions this week
at DevConnections in Vegas or at my workshop in Vancouver BC on the 26th
(see www.devweek.com for availability).
<<<<<

Sounds good, but I'll be far from Vegas at that time.

Thanks.

 

[William Vaughn]

And, I just wrote a whitepaper on this for Developer.Com.
http://www.developer.com/net/asp/article.php/10917_3702826_1



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Andrew Faust]

You could write code as many of the others suggested. Or you could do it
the easy way. Download the 180 day trial of full SQL Server 2005 and
install just the client access & developer tools. It will include include
SQL Server Management Studio. From that you can do a simple import/export.

http://www.microsoft.com/sql/downloads/trial-software.mspx#EXC

 

[Jonathan Wood]

Just to clarify, are you saying that the full version of SQL Server
Management Studio has a completely different way of importing data than the
Express version does?

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

You could write code as many of the others suggested. Or you could do it
the easy way. Download the 180 day trial of full SQL Server 2005 and
install just the client access & developer tools. It will include include
SQL Server Management Studio. From that you can do a simple import/export.

http://www.microsoft.com/sql/downloads/trial-software.mspx#EXC

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com

I'm new to SQL stuff. All I have is MS SQL Express that came with Visual
Studio 2005, and SQL Server Management Studio Express, which I downloaded
and installed.

I've created a new table, and I have a comma-delimited text file of data
I want in that table.

Can someone tell me the least painful way to import that data into the
table?

Thanks.