>Is it good practice to create a different temporary table for each monthly text file?
The question kind of answers itself. You have a monthly routine that certainly will not take a month, you create a temp table that will not be persistent and permanent.
=> You do generate a temp table for staging every month, it's temporary, gone after the process so next month you have a new one.
If you have multiple files I would make it depend on their overall size, whether I'd collect their data into one staging table or not, but when I expect them to have data about the same final rows, then collecting them within the same temp table in one go just leads to doublets in the merge of data again or makes massaging them before merging them into production more complicated than when working on each file separately.
You can reuse the same temp table name because your last step of processing a single input file will end on dropping that temp table implicitly or explicitly doesn't matter. The typical destroying of a temp table happens, because the connection to the server is closed. The scope/lifetime of a normal temp table is the session and closing the connection ends it.
Your major decision also could be removing the identity from the temp table only still using it in the destination table for creating unique keys. Then the identity is generated on the fly at the final step. You still need a couple of columns for identifying a row your staging data should update instead of adding it again.
To expand a little: Whether you need to identify rows for updates or not depends on the nature of the data you import of course.
Assume you have a logging data, eg all sales transactions of the last month, every row is a new row. Then the only need of staging table for preparation/massaging the data before inserting it into the target database table is about any data errors or complications of converting text to the T-SQL data types.
Assume you have a list of products from a vendor, essentially the same list every month in their current state, then most rows are not to be inserted into the final table, but updated, if they contain any change at all. This requires that table to have some identifier for the product, which may not be your databases primary key, but a unique product code or other per vendor unique identifier. Your database may collect products from many vendors each having a different type of key and thus you have to generate your own.
In both cases the temp staging table is of use, but will have additional and differing purposes to clean data and be able to use T-SQL MERGE, for example, to both insert and update data from the staging table, which is impossible for a BULK operation just appending the data (Hence Tamar's post mentions APPEND FROM a text file is Foxpro, not MSSQL). The need to not directly bulk load into production mainly comes from being able to finally make a per row decision of insert or update and maybe also reject, for example to only import a certain category of products from a vendors products text file.
Identity can be fine, but not on the staging level. In the first run, you may simply not generate a primary key in the staging table at all, also no compound key on any columns. You want to be able to check for double keys, then you first have to allow them coming in as they are. In the worst case a text file may contain many temporal versions of an original row, thus only the last row for the same record in the text file would need to be in there and you have to reject all previous. such things can, for example, happen, if a vendor creates his monthly text file as the log of changes on products. Whenever a product was changed more than once in a month it is double in this export. Notice, in that case the vendors monthly products text file is not the list of all products but a change log, which might be much smaller then the full list but will have other preprocessing needs.
Finally, there is no single recipe and solution, you have to know the nature of the import file and what you want to take in from it.
Bye, Olaf.
