MS SQL Server Books On Line - search on:
Bulk Insert or Bulk Copy Command or BCP
Maybe create a sp and execute as follows?
The BULK INSERT statement can be executed within a user-defined transaction.
Rolling back a user-defined transaction that uses a BULK INSERT statement
and BATCHSIZE clause to load data into a table or view using multiple
batches rolls back all batches sent to SQL Server.
Examples
This example imports order detail information from the specified data file
using a | character as the field terminator and |\n as the row terminator.
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
------------------------------------------------------
BULK INSERT (T-SQL)
Copies a data file into a database table or view in a user-specified format.
Syntax
BULK INSERT [['database_name'.]['owner'].]{'table_name' FROM data_file}
[WITH
(
[ BATCHSIZE [= batch_size]]
[[,] CHECK_CONSTRAINTS]
[[,] CODEPAGE [= 'ACP' | 'OEM' | 'RAW' | 'code_page']]
[[,] DATAFILETYPE [=
{'char' | 'native'| 'widechar' | 'widenative'}]]
[[,] FIELDTERMINATOR [= 'field_terminator']]
[[,] FIRSTROW [= first_row]]
[[,] FORMATFILE [= 'format_file_path']]
[[,] KEEPIDENTITY]
[[,] KEEPNULLS]
[[,] KILOBYTES_PER_BATCH [= kilobytes_per_batch]]
[[,] LASTROW [= last_row]]
[[,] MAXERRORS [= max_errors]]
[[,] ORDER ({column [ASC | DESC]} [,...n])]
[[,] ROWS_PER_BATCH [= rows_per_batch]]
[[,] ROWTERMINATOR [= 'row_terminator']]
[[,] TABLOCK]
)
]
Arguments
'database_name'
Is the name of the database in which the specified table or view resides. If
not specified, this is the current database.
'owner'
Is the name of the table or view owner. owner is optional if the user
performing the bulk copy operation owns the specified table or view. If
owner is not specified and the user performing the bulk copy operation does
not own the specified table or view, Microsoft� SQL Server� returns an error
message, and the bulk copy operation is canceled.
'table_name'
Is the name of the table or view to bulk copy data into. Only views in which
all columns refer to the same base table can be used. For more information
on the restrictions for copying data into views, see INSERT.
data_file
Is the full path of the data file that contains the data to be copied into
the specified table or view. BULK INSERT can copy data from a disk
(including network, floppy disk, hard disk, and so on).
data_file must specify a valid path from the server on which SQL Server is
running. If data_file is a remote file, specify the UNC name.
BATCHSIZE [= batch_size]
Specifies the number of rows in a batch. Each batch is copied to the server
as one transaction. SQL Server commits or rolls back, in the case of
failure, the transaction for every batch. By default, all data in the
specified data file is one batch.
CHECK_CONSTRAINTS
Specifies that any constraints on table_name are checked during the bulk
copy operation. By default, constraints are ignored.
CODEPAGE [= 'ACP' | 'OEM' | 'RAW' | 'code_page']
Specifies the code page of the data in the data file. CODEPAGE is relevant
only if the data contains char, varchar, or text columns with character
values greater than 127 or less than 32.
HTH,
Steve
----- Original Message -----
From: "Jason Egan" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 30, 2000 10:18 PM
Subject: RE: reading text files
> That is a good idea, but there will be 3 separate files from 150mb - 300mb
> each that will need to be processed each day. Each record will have to be
> updated/inserted/deleted from the db. So I'm trying for total automation
> from the ftp retrieval to the complete db comparison.
>
> :-(
>
> It is becoming a vendetta!
> je
>
> -----Original Message-----
> From: Dan Haley [mailto:[EMAIL PROTECTED]]
> Sent: Friday, June 30, 2000 5:23 PM
> To: '[EMAIL PROTECTED]'
> Subject: RE: reading text files
>
>
> Does it have to be fast? You could use VBA in Excel to read the file in
> which would allow you to separate it to multiple sheets (without cutting
up
> the text file itself) for later import into Access or SQL. Or even VBA in
> Access I suppose to read the file and do inserts. The VBA into Excel
> wouldn't be too slow (we're counting in minutes though, not seconds or
> milliseconds!), and then you could possibly automate the import into Excel
> at the same time. Pretty much anything you can do manually in the Office
> apps can be automated with VBA . . . sometimes they just need to be
started
> before heading for home . . . :)
>
> Dan
>
> -----Original Message-----
> From: Jason Egan [mailto:[EMAIL PROTECTED]]
> Sent: Friday, June 30, 2000 3:01 PM
> To: [EMAIL PROTECTED]
> Subject: RE: reading text files
>
>
> yeah! --- that is exactly what we've had to do in the past, but we need to
> automate and streamline...
>
> I've tried the readline tag, but it still seems to bomb after a certain
> amount of lines, not the same each time... unpredictable.
>
> GRRRRRR.....
> je
>
> > -----Original Message-----
> > From: Mark Warrick [mailto:[EMAIL PROTECTED]]
> > Sent: Friday, June 30, 2000 2:20 PM
> > To: [EMAIL PROTECTED]
> > Subject: RE: reading text files
> >
> >
> > I ran into the same problem in the past with a 50 MB text file
> > that I need to import into SQL.
> >
> > I tried making it a spreadsheet and importing to Access first,
> > but it cuts of at something like 65,000 lines. Same problem with
> > importing directly into Access, it just bombs. Also, I tried
> > importing directly into SQL, but SQL just froze up after 15
> > minutes of processing.
> >
> > I ended up solving the problem but cutting up the text file into
> > smaller pieces and doing a whole lot of imports. It was a
> > tedious task, but it worked.
> >
> > If anyone knows the right way to do it and can direct us both to
> > perhaps SQL help docs that describe it, I know we would both
> > appreciate it.
> >
> > ---mark
> >
> > --------------------------------------------------------------
> > Mark Warrick
> > Phone: (714) 547-5386
> > Efax.com Fax: (801) 730-7289
> > Personal Email: [EMAIL PROTECTED]
> > Personal URL: http://www.warrick.net
> > Business Email: [EMAIL PROTECTED]
> > Business URL: http://www.fusioneers.com
> > ICQ: 346566
> > --------------------------------------------------------------
> >
> >
> > > -----Original Message-----
> > > From: Jason Egan [mailto:[EMAIL PROTECTED]]
> > > Sent: Friday, June 30, 2000 1:10 PM
> > > To: Cf-Talk
> > > Subject: reading text files
> > >
> > >
> > > I have a rather large text file (145mb) that I need to read into a dB.
> > >
> > > The part I'm having trouble with is the READING of the file part. I
am
> > > using a line by line tag from the dev site to read in say 1000
> > lines at a
> > > time, but some times it bombs at line 500... then I'll run it
> > again and it
> > > works, then I run it again and it bombs at 532 or something
> > like that... I
> > > examine the lines and they are properly formatted, I've even
> > taken out the
> > > inserts and I still receive the errors when only reading the
> > > files in... any
> > > ideas why?
> > >
> > > Is it the file size?
> > >
> > > thanks,
> > > je
> > >
> > > ------------------------------------------------------------------
> > > ------------
> > > Archives: http://www.eGroups.com/list/cf-talk
> > > To Unsubscribe visit
> > > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf
> > > _talk or send a message to [EMAIL PROTECTED] with
> > > 'unsubscribe' in the body.
> >
> > ------------------------------------------------------------------
> > ------------
> > Archives: http://www.eGroups.com/list/cf-talk
> > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar
> >
>
> --------------------------------------------------------------------------
--
> --
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
> --------------------------------------------------------------------------
--
> --
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
>
> --------------------------------------------------------------------------
----
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
