Bulk Insert

I'm trying to import data from a text file into my database. each line of this text file must be inserted into 2 seperate tables depending on characters in the line of text. any ideas about how to do this without using a great deal of cpu would be appreciated.
thanks.... :confused:IF I understand correctly you are trying to use one Bulk Insert command using single file to load 2 tables at same time depending on some data. As per my understanding , it may be possible if you have triggers on table which would delete rows from primary and add to secondary (depending on character you loking for)

Or you can insert all the rows in Primary and use another BCP to load to another table using a select statement and later remove those rows from First table|||I would load everything to a stagin table, then use sql to split it up to the final destination tables...|||another problem i have is that the file contains 128 bit strings that i have to separate into columns of my tables. what would be the best way to get these substrings separated while trying to insert the data into a primary table. if i could do that, then i can just bcp what i need from the primary table over to my secondary one.

thanks 4 the help...
e3witt|||Is the data delimeted or fixed width?|||the data is fixed lenght, not delimited. the only thing i can think of is to look at the substrings that capture each item for my database.|||Sorry Double post|||Did you look at DTS. It is Possible to create a dts for this . A column from text file can be mapped to many columns in a table (transform process) . Or

Load data in some Staging table .
Use a Select statement to BCP out 2 files using Substring function to breakup the column. Load the text files one at atime to 2 different tables

If you want you can send table structure and file and may be we could help|||the txt file attached is an example, only the one i'll be given has b/t 1000 and 10000 lines.|||AND header and trailer info....

Create a table with 1 column varchar(8000)

bcp the whole file in to that table

Get a copy of your record layout and create a select against that table like

SELECT SUBSTRING(Col1,1,1) AS Col1
, SUBSTRING(Col1,2,10) AS Col2
ect

You can even create it as a view...

Add a WHERE Clause to eliminate the header and trailer

You can even do data audits...

And check the validity of some data...using ISNUMERIC and ISDATE...

Sound good?

That's what I do...|||thanks... i'll try that today. thank you for ur help.