A client of mine had some data in a single table but wanted it split in to different identical tables based on some data type. This is a common problem often fixed with the use of dynamic sql.
Basically his question was. How do you do insert data into a dynamic table variable?
This is not possible in SQL Server without using dynamic sql and, as we all know, dynamic sql is bad.
People often want to do this but most of the time the table name is not totally dynamic and if this is true for you then might consider using this method instead of dynamic sql. (though, if size is your problem then you could also consider partitioning)
Use an import table with an instead of trigger.
- Create an import table which is a superset of all the columns that will you could put in to the individual tables and call it xxxxxxx_import.
- Now create an INSTEAD OF trigger on that table. This is a trigger that operates instead of the requested action. In this case, instead of inserting in to this table it inserts it in to another table based on a condition (see example below for what to put in this trigger)
- now all we need to do is insert in to this virtual table instead of the individual tables
For this example I have created identical tables but they don’t have to be as long as the master is a superset and the trigger does not try to insert data in to a table that does not contain that field. (This will be obvious at design time when the trigger is compiled but be careful of changing the receiving table schemas after the trigger is put in place.)
The great thing about this system is that the data can be indexed and if you want you could create a partitioned view on it.
Of course this is a bit of an unusual situation as you could have just used a single table and put 2005 style partitioning on the table assuming you have 2005+ Enterprise edition.
Hope this helps you.