create table question

Hi all,
I bring data from 15 different files into several SQL Server tables every
night. I first bring data from CSV file and store them into 15 different
intermediary tables. Then I execute stored procedure that imports data from
intermediary tables into the tables that our users use.
My question is about clearing the intermediary tables. Right now I am
just truncating the tables before importing new data from CSV files. So I
use Insert Into...Select query. But this can create a problem if any of the
columns in the CSV files are removed. So I am thinking if it is worthwhile
droping the tables and then import data using Select...Into query. This way
even if a column is removed, the data will still be imported from the CSV
files. My question is are there any drawbacks in droping and recreating 15
tables everynight instead of truncating them?
Thanks.Actually, if you're database recovery model is not FULL, a SELECT INTO is
going to run significantly faster than an INSERT SELECT, because it's
considered a BULK operation.
Can't think of any drawbacks.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Nikhil Patel" <nikhil0100@.aol.com> wrote in message
news:O9DvE8GNFHA.3512@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I bring data from 15 different files into several SQL Server tables
> every
> night. I first bring data from CSV file and store them into 15 different
> intermediary tables. Then I execute stored procedure that imports data
> from
> intermediary tables into the tables that our users use.
> My question is about clearing the intermediary tables. Right now I am
> just truncating the tables before importing new data from CSV files. So I
> use Insert Into...Select query. But this can create a problem if any of
> the
> columns in the CSV files are removed. So I am thinking if it is worthwhile
> droping the tables and then import data using Select...Into query. This
> way
> even if a column is removed, the data will still be imported from the CSV
> files. My question is are there any drawbacks in droping and recreating 15
> tables everynight instead of truncating them?
> Thanks.
>|||I can see some drawbacks to creating the tables each time. If columns
are missing or renamed then won't your SPs that access them fail
anyway? You should avoid using SELECT * in production code. Plus,
you'll have to give DDL admin rights to whatever process performs the
load.
One possible approach is to create views that only expose certain
columns in the base table(s) then use a Dynamic Properties task in DTS
to load the data to the appropriate view each time. This does assume
you can read some metadata that determines what columns should be
present. If that metadata isn't available then I would have thought it
desirable to fail the load whenever some data was missing. What if the
file contains zero columns - just empty rows?
David Portas
SQL Server MVP
--sql