William
Mon Nov 05 10:27:40 PST 2007
This is a multi-part message in MIME format.
------=_NextPart_000_005D_01C81F96.7E2E0600
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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.=20
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.=20
b.. TSQL BulkCopy operations
c.. DTS/SSIS scripts=20
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.=20
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
--=20
____________________________________
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" <jwood@softcircuits.com> wrote in message =
news:%239kZoJ0HIHA.4584@TK2MSFTNGP03.phx.gbl...
> I'm new to SQL stuff. All I have is MS SQL Express that came with =
Visual=20
> Studio 2005, and SQL Server Management Studio Express, which I =
downloaded=20
> and installed.
>=20
> I've created a new table, and I have a comma-delimited text file of =
data I=20
> want in that table.
>=20
> Can someone tell me the least painful way to import that data into the =
> table?
>=20
> Thanks.
>=20
> --=20
> Jonathan Wood
> SoftCircuits Programming
>
http://www.softcircuits.com
>
------=_NextPart_000_005D_01C81F96.7E2E0600
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16546" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Ah, none of the above.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>ADO and ADO.NET (and all of its =
predecessors after=20
DB-Library) are QUERY interfaces, not designed for bulk operations. It's =
a waste=20
of time and resources to try to use the System.Data classes to import =
more than=20
a few rows of data. If the data is being "manufactured" it still =
makes=20
sense to write it to a delimited file and stream it in via one of the =
bulk copy=20
techniques discussed below. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>When it's time to import data into SQL =
Server, the=20
fastest (by an order of magnitude or more) is to use the TDS bulk copy =
approach.=20
This is exposed in a number of ways including:</FONT></DIV>
<UL>
<LI><FONT face=3DArial size=3D2>The BCP commandline utility. =
</FONT></LI>
<LI><FONT face=3DArial size=3D2>TSQL BulkCopy operations</FONT></LI>
<LI><FONT face=3DArial size=3D2>DTS/SSIS scripts </FONT></LI>
<LI><FONT face=3DArial size=3D2>ADO.NET SqlBulkCopy =
method.</FONT></LI></UL>
<DIV><FONT face=3DArial size=3D2>All of these techniques can import data =
from=20
anything that can be read by a .NET data provider, an OLE DB data =
provider, and=20
ODBC driver or your own custom-written data provider that exposes a =
DataReader.=20
This means other database tables, flat files, text files, delimited=20
files--almost anything. These routines can import millions of rows in no =
time.=20
Some Oracle customers buy SQL Server just to get SSIS. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>My latest book details how to use =
SqlBulkCopy. BOL=20
shows how to do the rest. I'll be happy to show it to you if you come to =
one of=20
my sessions this week at DevConnections in Vegas or at my workshop in =
Vancouver=20
BC on the 26th (see <A =
href=3D"
http://www.devweek.com">www.devweek.com</A> for=20
availability).</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>hth</FONT></DIV>
<DIV><BR><FONT face=3DArial size=3D2>--=20
<BR>____________________________________<BR>William (Bill) =
Vaughn<BR>Author,=20
Mentor, Consultant<BR>Microsoft MVP<BR>INETA Speaker<BR></FONT><A=20
href=3D"
http://www.betav.com/blog/billva"><FONT face=3DArial=20
size=3D2>www.betav.com/blog/billva</FONT></A><BR><A=20
href=3D"
http://www.betav.com"><FONT face=3DArial=20
size=3D2>www.betav.com</FONT></A><BR><FONT face=3DArial size=3D2>Please =
reply only to=20
the newsgroup so that others can benefit.<BR>This posting is provided =
"AS IS"=20
with no warranties, and confers no=20
rights.<BR>__________________________________<BR>Visit </FONT><A=20
href=3D"
http://www.hitchhikerguides.net"><FONT face=3DArial=20
size=3D2>www.hitchhikerguides.net</FONT></A><FONT face=3DArial size=3D2> =
to get more=20
information on my latest book:<BR>Hitchhiker's Guide to Visual Studio =
and SQL=20
Server (7th Edition)<BR>and Hitchhiker's Guide to SQL Server 2005 =
Compact=20
Edition=20
(EBook)<BR>--------------------------------------------------------------=
---------------------------------------------------------</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>"Jonathan Wood" <</FONT><A=20
href=3D"mailto:jwood@softcircuits.com"><FONT face=3DArial=20
size=3D2>jwood@softcircuits.com</FONT></A><FONT face=3DArial =
size=3D2>> wrote in=20
message </FONT><A =
href=3D"news:%239kZoJ0HIHA.4584@TK2MSFTNGP03.phx.gbl"><FONT=20
face=3DArial =
size=3D2>news:%239kZoJ0HIHA.4584@TK2MSFTNGP03.phx.gbl</FONT></A><FONT=20
face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> =
I'm new to SQL=20
stuff. All I have is MS SQL Express that came with Visual <BR>> =
Studio 2005,=20
and SQL Server Management Studio Express, which I downloaded <BR>> =
and=20
installed.<BR>> <BR>> I've created a new table, and I have a=20
comma-delimited text file of data I <BR>> want in that table.<BR>> =
<BR>> Can someone tell me the least painful way to import that data =
into the=20
<BR>> table?<BR>> <BR>> Thanks.<BR>> <BR>> -- <BR>> =
Jonathan=20
Wood<BR>> SoftCircuits Programming<BR>> </FONT><A=20
href=3D"
http://www.softcircuits.com"><FONT face=3DArial=20
size=3D2>
http://www.softcircuits.com</FONT></A><BR><FONT face=3DArial=20
size=3D2>></FONT></BODY></HTML>
------=_NextPart_000_005D_01C81F96.7E2E0600--