Thanks in advance (even CELKO) DDL attached

I DID NOT create this application.  I am trying to fix it.  The application
recieves a Access database table once a month.  This table contains employees
and multiple columns detailing begining and ending balances, pay amounts
etc... The tables contain UP TO 240 columns per table   The time periods roll
forward so some columns drop off as others are added.  3 years 36 tables.
TERRRIBLE NASTY MESS!! 

I created a couple of tables that allowed me to ennumerate all the tables
(manually) and all the columns in each table.  I then grouped by column name
and created a table that will contain all the columns (total 388) so that I
can create one record for each employee.

I need to be able to insert/update the results table from each monthly table
without having to manually type each column list for the insert update.

I did read: http://www.sommarskog.se/dynamic_sql.html

But I'm thinking I may need to do something to loop through the
IMPORT_MASTER to run against each tables columns

CREATE TABLE [dbo].[IMPORT_MASTER] (
    [DataFileID] [int] IDENTITY (1, 1) NOT NULL ,
    [FileName] [varchar] (50) NULL ,
    [DateRecieved] [datetime] NOT NULL ,
    [DateProcessed] [datetime] NULL
) ON [PRIMARY]
GO

INSERT IMPORT_MASTER Values ('masterfile_011604','2004-01-16
00:00:00.000',NULL)
INSERT IMPORT_MASTER Values ('masterfile_022004','2004-02-20 00:00:00.000',
NULL)
INSERT IMPORT_MASTER Values ('masterfile_032004','2004-03-20 00:00:00.000',
NULL)


CREATE TABLE [dbo].[IMPORT_Process] (
    [ColumnKeyID] [int] IDENTITY (1, 1) NOT NULL ,   
    [DataFileID] [int] NOT NULL ,
    [ColumnName] [varchar] (50) NULL ,
    [ColumnProcess] [varchar] (50) NULL
) ON [PRIMARY]
GO

INSERT IMPORT_PROCESS VALUES (1,'EmployeeID',NULL)
INSERT IMPORT_PROCESS VALUES (1,'autopay',NULL)
INSERT IMPORT_PROCESS VALUES (1,'AutoSelfPay',NULL)
INSERT IMPORT_PROCESS VALUES (1,'Col1',NULL)
INSERT IMPORT_PROCESS VALUES (1,'Col2',Null)
INSERT IMPORT_PROCESS VALUES (1,'Col3',NULL)
INSERT IMPORT_PROCESS VALUES (1,'MCol1',Null)
INSERT IMPORT_PROCESS VALUES (1,'MCol2',Null)
INSERT IMPORT_PROCESS VALUES (1,'MCol3',NULL)
INSERT IMPORT_PROCESS VALUES (2,'EmployeeID',NULL)
INSERT IMPORT_PROCESS VALUES (2,'autopay',NULL)
INSERT IMPORT_PROCESS VALUES (2,'AutoSelfPay',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col2',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col3',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col4',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col5',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol3',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol4',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol5',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol6',NULL)
INSERT IMPORT_PROCESS VALUES (3,'EmployeeID',NULL)
INSERT IMPORT_PROCESS VALUES (3,'autopay',NULL)
INSERT IMPORT_PROCESS VALUES (3,'AutoSelfPay',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col5',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col6',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col7',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col8',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol3',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol4',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol5',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol6',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol7',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol8',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol9',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol10',NULL)



CREATE TABLE [dbo].[IMPORT_TRANSACTION] (
    [ImportTransID] [int] IDENTITY (1, 1) NOT NULL ,
    [DataFileID] [int] NOT NULL ,
    [EmployeeID] [INT] NOT NULL ,
    [familyid] [varchar] (50)  NULL ,
    [firstname] [varchar] (50) NULL ,
    [lastname] [varchar] (50)  NULL ,
    [autopay] [bit] NOT NULL ,
    [autoselfpay] [bit] NOT NULL ,
    [AutoStartART] [money] NULL ,
    [Col1] [money] NULL ,
    [Col2] [money] NULL ,
    [Col3] [money] NULL ,
    [Col4] [money] NULL ,
    [Col5] [money] NULL ,
    [Col6] [money] NULL ,
    [Col7] [money] NULL ,
    [Col8] [money] NULL ,
    [Col9] [money] NULL ,
    [MCol1] [money] NULL ,
    [MCol2] [money] NULL ,
    [MCol3] [money] NULL ,
    [MCol4] [money] NULL ,
    [MCol5] [money] NULL ,
    [MCol6] [money] NULL ,
    [MCol7] [money] NULL ,
    [MCol8] [money] NULL ,
    [MCol9] [money] NULL ,
    [MCol10] [money] NULL
) ON [PRIMARY]
GO

StvJston wrote:

Show quoteHide quoteYou say you want to fix it but I don't understand what fixing it has to
do with what you are attempting here. Here's what I'd assume: 1) Create
a normalized data model in SQL. 2) Create a data loading process that
populates the new data model from the Access one.

For 2) the obvious solution is DTS, in which transformations can map
columns and tables dynamically to your new model. Even assuming you
wanted to implement that in TSQL only (using linked servers?) I don't
see how your metadata tables will help much. A more standard approach
would be to use staging tables that matched the source structure and
then transform those staging tables to the normalized ones using
INSERTs. Maybe you could explain a bit more about what you are trying
to achieve.

Why? But if so, use a WHILE loop.

David,

There is now no way to balance the system from one month to another.  This
entire application model is going away but I have to try and produce a
balance sheet for manual work that was done the last three years to finish
nailing the coffin..  Entries hand entered, adjustments to balances made
etc....  I hadn't thought about using DTS though  This only needs to be done
one time so I'm trying to do it as quick and dirty as I can while still being
able to document what I'm doing so the process could be replicated if needed.


Show quoteHide quote

StvJston (StvJs***@discussions.microsoft.com) writes:
Show quoteHide quoteI had a look at your tables, and I read your narrative, but I had
difficulties to bring it together. What is what? Unless you are taking
the DTS path that David suggested, it would help with more details.

Solved this.  Created a DTS package that completed the task.  Thanks for the
pointers

Show quoteHide quote

Why query optimizer does not select the index?
Popuate field with value from last row
Finding the right record
CHARINDEX in CASE Within SELECT Statement
Default Value Calculation