Dynamic table and column list

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 employee
s
and multiple columns detailing begining and ending balances, pay amounts
etc... The tables contain UP TO 240 columns per table The time periods rol
l
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]
GOStvJston wrote:

> Thanks in advance (even CELKO) DDL attached
> I DID NOT create this application. I am trying to fix it. The applicatio
n
> recieves a Access database table once a month. This table contains employ
ees
> and multiple columns detailing begining and ending balances, pay amounts
> etc... The tables contain UP TO 240 columns per table The time periods r
oll
> 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 na
me
> 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 tab
le
> without having to manually type each column list for the insert update.
> I did read: http://www.sommarskog.se/dynamic_sql.html
>
You 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.

> But I'm thinking I may need to do something to loop through the
> IMPORT_MASTER to run against each tables columns
Why? But if so, use a WHILE loop.
David Portas
SQL Server MVP
--|||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 bein
g
able to document what I'm doing so the process could be replicated if needed
.
"David Portas" wrote:

> StvJston wrote:
>
> You 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 Portas
> SQL Server MVP
> --
>|||StvJston (StvJston@.discussions.microsoft.com) writes:
> 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 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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Solved this. Created a DTS package that completed the task. Thanks for the
pointers
"Erland Sommarskog" wrote:

> StvJston (StvJston@.discussions.microsoft.com) writes:
> I 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.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>