Replace existing table

Hi. I am new to SQL Server so please excuse me if my question is stupid or asked in the wrong place.

I am replicating a number of Visual FoxPro tables in SQL Server. The tables are updated daily. I have got several of them replicated fine through using BULK INSERT to load the existing data and INSERT/UPDATE/DELETE to replicate the daily updates.

However, one of the Visual FoxPro tables is re-created from scratch each day. I could do the same in SQL Server but I would like users to have uninterrupted access to it (i.e. I do not want a query to fail while I am deleting and then re-upsizing the table).

One method that occurs to me would be to upsize the table under a different temporary name and then BEGIN TRANSACTION, delete the old table, rename the new table to the old table and then COMMIT TRANSACTION. Would this work? And is there a better way of doing this?

Many thanks

Dom

I know the users have to go to lunch daily..., so announce a lunch break at 1 pm for all the people and do what to do; if your users don't want to leave do it after they go home.

Sure ,before take a full backup if you have time.

|||Thanks ggciubuc, but I'm afraid thats not an option. The update needs to be done as early as possible (but no earlier than 8am) so it cannot wait until the users have gone home, but it will not be at a consistent time of day because the start time depends on the time taken to complete a number of prior processes.|||

Make a Integration Services package , put it in a job that runs before 8 a.m (eg. 6 a.m); the package can contain:

1.a task that delete the SQL table

2. a task that re-create the SQL table

3. a task with bulk insert

with workflow you can prioritize the processes|||

Thanks Gigi - I have not come across Integration Services before, I will check it out.

However, as stated above, it cannot run earlier than 8am (typically it will run at some point around 10am but some days it may not be until 1pm or later) and I would like to avoid disrupting users if possible.

|||

I recommend to use your proposed solution (with minor change) for this task & (it may be done on the SSIS Package or DTS package), but your approach seems to be good enough to fit for your requirement.

1. Create a table on different temp name (not temp table -- #)

2. Do the manipulation on created table

3. On single transaction, Delete the existing row, insert from created table, drop the created table.

|||

Many thanks Manivannan, I'll give it a go. Deleting all of the the existing rows and then inserting the new records makes much more sense, I guess.

Could I even use a BULK INSERT to append the records (does BULK INSERT work inside a transaction?) and avoid creating the temporary table altogether?

|||

You can use the BULK INSERT on explicit(user) transaction. It can be roll backed also. J