Fastest way to replace all data in a SQL table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to periodically replace all of the data in a SQL table (with no
relationships) from an Excel file (Several times a day).

I've written code wich currently that runs a "delete query" and then an
append query. This approach also takes down my website (written in .NET)
while the queries run.

I tried a maketable query, but it just deletes the linked SQL table. Can I
modify the destination DB and Dest connection string to my SQL table on this
type of query?

Or, what is the best approach for replacing all of the data in a SQL table?
 
Ken said:
I need to periodically replace all of the data in a SQL table (with
no relationships) from an Excel file (Several times a day).

I've written code wich currently that runs a "delete query" and then
an append query. This approach also takes down my website (written
in .NET) while the queries run.

I tried a maketable query, but it just deletes the linked SQL table.
Can I modify the destination DB and Dest connection string to my SQL
table on this type of query?

Or, what is the best approach for replacing all of the data in a SQL
table?
Click to expand...

Have you looked at DTS? Generally faster because the deletes and updates need
not be logged.
 
I need to periodically replace all of the data in a SQL table (with no
relationships) from an Excel file (Several times a day).

I've written code wich currently that runs a "delete query" and then an
append query. This approach also takes down my website (written in .NET)
while the queries run.

I tried a maketable query, but it just deletes the linked SQL table. Can I
modify the destination DB and Dest connection string to my SQL table on this
type of query?

Or, what is the best approach for replacing all of the data in a SQL table?
Click to expand...

A PassThrough query

"TRUNCATE tablename;"

will quickly empty the table (much faster than a delete query); you
could then run your Append. This needs to be a passthrough as (I don't
think) Truncate is supported JET SQL.

John W. Vinson[MVP]
 
Back
Top