Dynamic Table Insert TSQL

Question

Hi all i have been struggling this for a while now! I have some data files with a date in them, these are dumped into a staging table. What i would like my script/function to do is to read the date for each record in the staging table and move to the appropriate table. Now i know i could do this very easily just using some static inserts, for example

INSERT INTO TABLE_2011_08
WHERE Datafields = 2011_08

However i want it to be dynamic, so i was thinking something along the lines of a function/stored procedure to pass in the date for each record. However my brain is melting a bit with this!

The data records in the staging table could be something like this:-

RecordA 2011-08-30 Data Data Data
RecordB 2011-08-31 Data Data Data
RecordC 2011-09-01 Data Data Data
RecordD 2011-09-02 Data Data Data

Answer 1

Here you go:

CREATE TABLE dbo.Some_Meaningful_Descriptive_Name (
    record_id    CHAR(7)        NOT NULL,
    some_date    DATETIME       NOT NULL,
    some_data    VARCHAR(20)    NOT NULL,
    ...
)

Now your import process just has to put them in the table. No dynamic requirements and no need to create a new table every month.

If you really need to make them look separate then create views over the table:

CREATE VIEW dbo.Some_Meaningful_Descriptive_Name_2011_08
AS
BEGIN

    SELECT
        record_id,
        some_data
    FROM
        dbo.Some_Meaningful_Descriptive_Name
    WHERE
        some_date >= '2011-08-01 00:00:00.000' AND
        some_date <  '2011-09-01 00:00:00.000'
END

It's going to be pretty rare when you have so many rows that you need to actually put them into separate tables. (we're talking 10's of millions of rows). With partitioning now, even then it probably isn't necessary.

Putting them into separate tables is just going to make it harder to work with them in the future.