INSERT INTO @table (without dynamic sql)

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?

e.g.

DECLARE @tablename varchar(128)
SET @tablename = ‘Blee’
INSERT INTO @tablename
SELECT * FROM blah

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

EXAMPLE:

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.)

Create four identical tables. (These don’t have to have the switch type in but it might be a good idea for creating a partitioned view for getting the data out if they do.)
CREATE TABLE dbo.TABLE1(A int NULL,    B int NULL,    C int NULL) ON PRIMARY
GO
CREATE TABLE dbo.TABLE2(A int NULL,    B int NULL,    C int NULL) ON PRIMARY
GO
CREATE TABLE dbo.TABLE3(A int NULL,    B int NULL,    C int NULL) ON PRIMARY
GO
CREATE TABLE dbo.TABLE4(A int NULL,    B int NULL,    C int NULL) ON PRIMARY
GO
now script the import table (note that this one MUST have the switch type in and it mustn’t be NULL)

CREATE TABLE [dbo].[TABLE_import]( [A] [int] NULL, [B] [int] NULL, [C] [int] NULL, [Data_type] [int] NOT NULL) ON [PRIMARY]
Now create a trigger on this table. Note that this is an INSTEAD OF trigger and not an AFTER trigger.
CREATE TRIGGER dbo.trImport_insert ON  dbo.TABLE_import INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO TABLE1 (A,B,C) SELECT A,B,C FROM inserted WHERE Data_type = 1
INSERT INTO TABLE2 (A,B,C) SELECT A,B,C FROM inserted WHERE Data_type = 2
INSERT INTO TABLE3 (A,B,C) SELECT A,B,C FROM inserted WHERE Data_type = 3
INSERT INTO TABLE4 (A,B,C) SELECT A,B,C FROM inserted WHERE Data_type = 4
END
Insert your data in to the import table

INSERT INTO dbo.TABLE_import
(
A
,B
,C
,Data_type
)
SELECT
A
,B
,C
,data_type
from QUERY
Now have a look at the data in the individual tables and you will see it has been split
SELECT A,B,C FROM example_dynamic_insert_to_table.dbo.TABLE1
SELECT A,B,C FROM example_dynamic_insert_to_table.dbo.TABLE2
SELECT A,B,C FROM example_dynamic_insert_to_table.dbo.TABLE3
SELECT A,B,C FROM example_dynamic_insert_to_table.dbo.TABLE4

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.

Dave

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , , . Bookmark the permalink.

One Response to INSERT INTO @table (without dynamic sql)

  1. This can be the ideal document on this subject matter I’ve viewed. Visitors can identify together with your views and understand anything out of your understanding of this matter.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s