I have had a request, one of our tables is used as a report and I have been
asked that all fieldnames for months have dashes in them,
like Jan-05 instead of Jan05 and so on...
Now what we have is a CURSOR which loops through all values in another table
generating these fieldnames, like 'Jan-05', Feb-05' etc..
Then the table definition is modified so these months display as fieldnames.
That is fine,
Except later it calls another stored procedure which inserts values from
another table into this one.
INSERT INTO TableName
SELECT *
FROM Tablename
Obviously this is failing because the newly amended table now has dashes in
the fieldnames now.
Now as the fieldnames are created dynamically I cannot simply do INSERT
Tablename ([Jan-05], [feb-5]) etc, as one would normally do,
How could I do this bearing in mind its not always going to be 12 months we
use,
Basically I am asking how can I modify this INSERT statement so it takes
into account the fieldnames with dashes in them
Bearing in mind I don't know what the fieldnames are called as they are
created dynamically by a CURSOR in a different routine.
And its not always 12 months.
have you any ideas?
and the dashes are required
JayneDynamically built field names is a bit unusual. But you will most likely
have to dynamically build the insert command and then execute it. Use the
INFORMATION_SCHEMA.COLUMNS view to define a cursor of column names you need.
Ray
"Little PussyCat" <SPAMSPAM@.NOSPAM.com> wrote in message
news:5s5ug2-nc1.ln1@.tiger.sphynx...
> Hello,
> I have had a request, one of our tables is used as a report and I have
> been
> asked that all fieldnames for months have dashes in them,
> like Jan-05 instead of Jan05 and so on...
> Now what we have is a CURSOR which loops through all values in another
> table
> generating these fieldnames, like 'Jan-05', Feb-05' etc..
> Then the table definition is modified so these months display as
> fieldnames.
> That is fine,
> Except later it calls another stored procedure which inserts values from
> another table into this one.
> INSERT INTO TableName
> SELECT *
> FROM Tablename
> Obviously this is failing because the newly amended table now has dashes
> in
> the fieldnames now.
> Now as the fieldnames are created dynamically I cannot simply do INSERT
> Tablename ([Jan-05], [feb-5]) etc, as one would normally do,
> How could I do this bearing in mind its not always going to be 12 months
> we
> use,
> Basically I am asking how can I modify this INSERT statement so it takes
> into account the fieldnames with dashes in them
> Bearing in mind I don't know what the fieldnames are called as they are
> created dynamically by a CURSOR in a different routine.
> And its not always 12 months.
> have you any ideas?
> and the dashes are required
> Jayne|||Little PussyCat (SPAMSPAM@.NOSPAM.com) writes:
> I have had a request, one of our tables is used as a report and I have
> been asked that all fieldnames for months have dashes in them, like
> Jan-05 instead of Jan05 and so on...
> Now what we have is a CURSOR which loops through all values in another
> table generating these fieldnames, like 'Jan-05', Feb-05' etc.. Then the
> table definition is modified so these months display as fieldnames.
This is a design that I would strongly discourage use of. Month data
is best stored as rows in a subtable. Then if someone want to looks
at this with the rows as column, you define a view for him, but let
the underlying tables be.
> That is fine,
> Except later it calls another stored procedure which inserts values from
> another table into this one.
> INSERT INTO TableName
> SELECT *
> FROM Tablename
> Obviously this is failing because the newly amended table now has dashes
> in the fieldnames now.
There are two more cases of violations of good practice here:
1) Always list columns explicitly with INSERT:
2) Don't use SELECT * in production code.
Anyway, if you let this poor table be and define that view instead, the
problem will go away.
This may not be the answer you were looking for, but creating columns
dynamically is definitely not what relational databases are designed for,
and if you go there, it becomes very painful.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> This is a design that I would strongly discourage use of. Month data
> is best stored as rows in a subtable. Then if someone want to looks
> at this with the rows as column, you define a view for him, but let
> the underlying tables be.
I have recently taken over from someone else in a job so for the time being
I am having to work with how the original code is written, plus I am
learning how all the systems hook together.
Yes, in my opinion I would have done it in a View but until I feel 100% sure
of how all the systems work I would rather work this way.
Oh, I solved my problem, the stored procedure was failing at the ALTER TABLE
Column command in that CURSOR,
It was failing because of the dashes in the fieldname, so all I have to do
is surround the fieldnames with square brackets when its creating the
column, like this
Exec ('ALTER TABLE TableName ADD' + '[' + @.ColName + ']' + 'Money NULL')
And yes I know having dashes in fieldnames is a big No No, and normally I
wouldn't do it, later on when I feel more comfortable in my job I will
change this to a View then I can simply create Aliases for the Month Names
Thanks for your help anyway :-)
Regards,
Jayne|||Little PussyCat (SPAMSPAM@.NOSPAM.com) writes:
> I have recently taken over from someone else in a job so for the time
> being I am having to work with how the original code is written, plus I
> am learning how all the systems hook together. Yes, in my opinion I
> would have done it in a View but until I feel 100% sure of how all the
> systems work I would rather work this way.
I think that is a fallacy. You only run the risk to be let out on
lonely winding roads that you have no idea where they lead to or
even is what behind the next curve.
> And yes I know having dashes in fieldnames is a big No No,
I wouldn't say so. After all, SQL is designed to accommodate this
possibility. I don't use it myself, as all those brackets or quotes
makes the code more bulky. I'd say the dashes is the smallest of your
problems.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment