i have a table in SQL server 2000 database which need to be updated monthly.. it is required to delete all the record in the table before updating it. the table consist of 6000+ records..the database which contains this table held on the hosting servers located in US

what is the best option to do this beside enterprise manager...

secondly...the data i want to insert in my table is stored in a CSV format file. is it possible that i may upload the CSV file on the hosting server and use ASP to import data from CSV file into the SQL server.

truncate table tablename

BULK INSERT tablename
        FROM 'c:\inetpub\wwwroot\yoursite\yourfile.csv'
        WITH
        ( FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n',
        KEEPIDENTITY
        )

Take the keepidentity out if you don't have an ident column, you might have to set other parameters based on your data but you should get the idea. check the online book
    

yup i got the whole of it.. thanks a lot..

but the data base server is a different physical server than the web server. so i cant use it ...the above example will only work provied that webservr and datanbase servers r on the same physical server..

i could think of a software based solution which can read the CSV file from my PC or the hosting server and do the task or an ASP solution which can do it all...

i dont care it is cost me to install and buy a component provided that component doest cost a fortune..

played around with the DTS package little bit..well i think to use th mentioned method.. i still need file system access to the Db server or the network share path where the CSV file is stored..

i think an ASP page with all the data from the CSV file in it can be made which will do all the opreation but the amount of data to be inserted is around 2.5 MB when in a raw text format (.asp, .csv, .txt) and i bet that would kill the server and hosting ppl with be mad @ me

the only viable solution i can think of is to import the CSV into MDB, then upload it to the hosting and make some ASP which will first delete all the records in the table and then read te MDB and insert the records into the table on SQL server in batches of 50 or 100 so they dont eat up all the processor cycles and kill the web server

OR

import CSV into MDB and then use the access upsize wizard to export all data to SQL server with a differnt table name..well this would take long time tho but i dont think more than 30 minutes in a 500+K upload speed...after all data has ben upsized sucessfully.. use ASP or enterprise manager to delete the old table and rename new table to coressponding name and change coloum data types.. i prefer to do the table renaming and deleting using ASP bcuz that way i could update the Db from anywhere regardless the availibility of enterprise manager and top of everything it just sucks when usuing enterprise manger to manage database remotely unless u got T1

suguesstion for a better solution are still welcome..bear in mind that the table is required to be updated every 30-40 days and all old records MUST be deleted before new ones are inserted.. ..