I need to import data (>> 1 GB) into MS-SQL-Server. For several reasons I need to import this data into a staging table (not directly into the target). From staging the data is loaded into the target table. The data in the staging table is never used again. I have several ways to do:
- Load the data into staging stable, mark this data as new, load only marked data into target, unmark data.
- Delete all data in staging, load the data into staging table, load all data into target.
- Truncate staging table, load into staging, load into target
- Drop staging table, create staging table, laod into staging table, load into target.
- Drop staging database, create staging table, load into staging table, load into target.
If the solution is not restricted by your server role: What would be the recomended solution? Which is most robust?
Solution 1 is most likely nonsense if you don't need the data anymore. Solution 3 performances much better compared to 1 and 2. But what between solution 4 and 3? Actually I would prefer solution 5 as I can change nealy every parameter within the staging process within my load procedure but you need rights to drop&create database. This is quite a lot for a load process. So in most of the cases I use drop and recreate the staging table. But is this the recommended way?