Microsoft SQL Server forum - How to create table names by using macro variable? Thanks!

Author

How to create table names by using macro variable? Thanks!

rong.guo@gmail.com

2005-10-27, 9:25 am

Greetings!

I am now doing one type of analysis every month, and wanted to creat
table names in a more efficient way.

Here is what happens now, everytime I do the analysis, I will create a
table called something like customer_20050930, and then update the
table by using several update steps. Then next month I will create a
table called customer_20051031. Does anyone know if there is a better
way to do it? like using a macro variable for the month-end date? Now
everytime I have to change the table name in every single update step,
which would cause errors if I forget to change one of them. By using a
macro, I would only need to change it once.

Thanks!

--CELKO--

2005-10-27, 9:25 am

This is how we programmed tape file systems in the 1950's. The IBM
convention was to have "yyddd" numbering on the tape labels.

In SQL, you would have one table, and build VIEWs from the appropriate
date column. The idea of an RDBMS is that you have a data model and
the tables represent entites in that model. Creating tables on the
fly says that you have no validate data model and in your Universe,
elephants fall out of the sky.

Hugo Kornelis

2005-10-27, 9:25 am

On 21 Oct 2005 10:24:10 -0700, rong.guo@gmail.com wrote:

>Greetings!
>
>I am now doing one type of analysis every month, and wanted to creat
>table names in a more efficient way.
>
>Here is what happens now, everytime I do the analysis, I will create a
>table called something like customer_20050930, and then update the
>table by using several update steps. Then next month I will create a
>table called customer_20051031. Does anyone know if there is a better
>way to do it?

Hi rong.guo,

Yes: add a column "Month" (or, better yet, two columns PeriodStart and
PeriodEnd) to your table, and add it to the primary key. I.e. if the
current primary key definition is

ALTER TABLE xxx
ADD PRIMARY KEY (DivisionID, ProductID)

you'll change it to

ALTER TABLE xxx
ADD PRIMARY KEY (PeriodStart, DivisionID, ProductID)
ALTER TABLE xxx
ADD UNIQUE (DivisionID, ProductID, PeriodEnd)

Then, in your code, add variables to hold PeriodStart and PeriodEnd and
use them in your queries. Next month, you'll only have to change the
values assigned to @PeriodStart and @PeriodEnd at the start of the
script.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

rola

2005-10-27, 9:25 am

Thank you both for your advise!!

I think i got myself a little bit confused... As I always use the
tables created by our DBA, I am not very familiar with
creating/altering tables, as well as the primary key (sorry). Here is
what i do now every month:

--At the end of September
select A, B (A B would change every month)
into customer_20050930
from table_A (already in our database and will be updated monthly)
group by A, B

--At the end of October
select A, B (A B would change every month)
into customer_20051031
from table_A (already in our database and will be updated monthly)
group by A, B

I am wondering how I can apply what you suggested to my current query?
Could you please show me the query?

Many thanks!

Hugo Kornelis wrote:
> On 21 Oct 2005 10:24:10 -0700, rong.guo@gmail.com wrote:
>
>
> Hi rong.guo,
>
> Yes: add a column "Month" (or, better yet, two columns PeriodStart and
> PeriodEnd) to your table, and add it to the primary key. I.e. if the
> current primary key definition is
>
> ALTER TABLE xxx
> ADD PRIMARY KEY (DivisionID, ProductID)
>
> you'll change it to
>
> ALTER TABLE xxx
> ADD PRIMARY KEY (PeriodStart, DivisionID, ProductID)
> ALTER TABLE xxx
> ADD UNIQUE (DivisionID, ProductID, PeriodEnd)
>
> Then, in your code, add variables to hold PeriodStart and PeriodEnd and
> use them in your queries. Next month, you'll only have to change the
> values assigned to @PeriodStart and @PeriodEnd at the start of the
> script.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

--CELKO--

2005-10-27, 9:25 am

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

What you did post makes no sense. There is no consistent definition of
these tables, since the coumns change from month to month. You cannot
compare them at all.

The GROUP BY can be replaced with a SELECT DISTINCT in your pseudo-code
There is no date value to use for the groupings. Etc. What are you
trying to do?

Erland Sommarskog

2005-10-27, 9:25 am

rola (rong.guo@gmail.com) writes:
> I think i got myself a little bit confused... As I always use the
> tables created by our DBA, I am not very familiar with
> creating/altering tables, as well as the primary key (sorry). Here is
> what i do now every month:
>
> --At the end of September
> select A, B (A B would change every month)
> into customer_20050930
> from table_A (already in our database and will be updated monthly)
> group by A, B
>
> --At the end of October
> select A, B (A B would change every month)
> into customer_20051031
> from table_A (already in our database and will be updated monthly)
> group by A, B
>
> I am wondering how I can apply what you suggested to my current query?
> Could you please show me the query?

If the tables can look different from month to month, then it is a little
more tricky to use one table with month as key. Of course, you can alter
the table to add or drop columns, but this may not be feasible.

I would suggest that the best is to use any text editor with a find-replace
functon to replace the strings. Since the queries change from to month, you
need to edit anyway.

The alternative is to use dynamic SQL, but that would make the script more
difficult to maintain.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Erland Sommarskog

2005-10-27, 9:25 am

--CELKO-- (jcelko212@earthlink
.net) writes:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>
> What you did post makes no sense. There is no consistent definition of
> these tables, since the coumns change from month to month. You cannot
> compare them at all.

Depends on exactly which columns he puts in, on how much he want to compare.

I have actually been playing this game recently. I've been running traces
at some customer sites, and to anaylse the trace, I say:

SELECT ...
INTO trc1024
FROM ::fn_get_trace_table
(...)

Thus, each trace gets a new table. Partly this is out of laziness, but
it could well be that the tables are different, if I decide to include
another column in the trace.

If I wanted to compare data over several days, this would be a difficult
setup to work with. But typically I'm mainly interested in the most recent
trace file, and the worst performance hogs in this trace. If I want to
compare data over several days, I can run a SELECT from a couple of tables
(rarely more than 3), since I'm usually only interested in one procedure
in such case.

The bottom line: don't do more work than you have to do!

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Hugo Kornelis

2005-10-27, 9:25 am

On 21 Oct 2005 21:35:46 -0700, rola wrote:

(snip)
>I am wondering how I can apply what you suggested to my current query?
>Could you please show me the query?

Hi rola,

No, I can't.

The only things you've shown here thus far are snippets of your current
solution. I could try to apply some band-aids to the spots where it
hurts most, but I'd much rather know the real problem and try to cure
that. It's possible that your current solution is indeed the best for
your particular problem - but it's also possible that there are better
ways to get the job done.

Can you post more information about your problem? I'd like to know the
structure of your tables (posted as CREATE TABLE statements; irrelevant
columns may be omitted, but please include all constraints and
properties), some rows of sample data to illustrate what your data
typically looks like (posted as INSERT statements) and a description of
the actual business problem that you're trying to solve.

Check out www.aspfaq.com/5006 for more details on the best way to ask
for help in these groups.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links