Ok here is the scenario. I have a text file that contains
all the data. I have a SQL Server db that has tables for
that data. Currently I backup the old table, then
truncate it, then insert the new data. I do this for
about 5 tables. The text file always has the newest data.
I do this nightly so I was wondering if there is a better
way to get the data inserted into the table, is this the
most efficient. How are most of you doing it?

It seems like there is a better way to accomplish that.
What are your thoughts?

Hi Mike
Congratulations on your concern.
Can you post DDL and description for the tables?
The reason I ask is to analyze if there are integrity constraints,indexes or checks involved.


----- Mike wrote: ----

Ok here is the scenario. I have a text file that contains
all the data. I have a SQL Server db that has tables for
that data. Currently I backup the old table, then
truncate it, then insert the new data. I do this for
about 5 tables. The text file always has the newest data.
I do this nightly so I was wondering if there is a better
way to get the data inserted into the table, is this the
most efficient. How are most of you doing it

It seems like there is a better way to accomplish that.
What are your thoughts

There are many ways to everything and the answer is "it depends"

removing all data and BULK INSERTing it back in from text file will be
incredibly quick but obviously the more rows you import the longer it will
take.

I am presuming then that all the text file gives you is the data for those 5
tables. These text files increase in size every day.

Ideally you would have the rows that have been changed during that
day/period appearing in the text file only. You would add an
OperationIdentifier column to the text file so you could tell what had
happened to the row (INSERT UPDATE DELETE).

You would then have a smaller text file and the load would be quicker.
Without an identifier for the operation you would either do as you are doing
or

Import the text files into Scratch Tables

INSERT = Key Col in Source not in Dest
DELETE = Key Col in Dest not in Source
UPDATE = Key col in Source and Dest

The problem here being that short of comparing all columns in Source and
Dest you will have to issue a blanket UPDATE statement.

There will be little time difference for either method.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Mike" <anonymous (AT) discussions (DOT) microsoft.com> wrote

You would add an OperationIdentifier column to the text
file so you could tell what had happened to the row
(INSERT UPDATE DELETE).

That would be an ideal way, but they would have to change
their current in-house db to accomendate this option.

you import the longer it will take.

If I use this option, every row would have to be error
free, correct or it would not get imported, correct?

This seems like a reasonable accomendation to accomplish
the desired task but like you said, it would have to check
all the colums which could be VERY time intensive.

blanket UPDATE statement.

Oh I see, just grab the key and update the row.

The problem I am running into is they also want a manual
process just incase sql server dies. Which I'm unsure how
to create. My initial thought is to create an identical
table structure in Access, but then again, if SQL dies
then DTS dies along with it. How safe is it to truncate
the table then insert, how does that affect indexes, etc?
I'm scared that it may truncate the table then an error
would occur then I would be SOL. How do you get around
that?

I don't have the db info with me but I can tell you that
on each table there is a multicolumn index (ex. id, date,
name, ssn) There are no constraints or checks involved
only the indexes I have to be concerned with.

How reliable is DTS for everyday use, I just fear it dying
in the middle of the import process.

DTS is very very reliable and its reliability should be no more called into
Question than any other ETL tool.
Indexes affect INSERT performance sure, but if you do not over cook the
indices i.e. have loads of them or they span most of your columns then in
2000 this is not really a problem.

BULK INSERT does not handle error rows (row*row) nicely

As you have mentioned and as I alluded to the source is the source and you
get it as you get it. Your current methodology is for me not a bad one.
You can work on them to produce a more streamlined version as we have
discussed.

Text files are really not a good source to start playing with as the driver
is limited but you can work around this by importing into a scratch SQL
Server table and using TSQL from there.





--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Mike" <anonymous (AT) discussions (DOT) microsoft.com> wrote