|
|
Posted by andro on 04/24/06 02:41
Hugo!
Thanx again for your help.
Regarding the first part.....nothing special - it's just that I'm kinda
bored to repeatedly enter nearly the same query for every system here.
(There are not just several but say 40-50 which are changing from project to
project.)
I just wondered (please note: I'm a newbie) is there a way to instruct the
system in a kind of "loop" DO - UNTIL or FOR/NEXT for every system table in
database to repat the qurey within the tables using "parameter" TBL_NAME
where neccessary. Some kind of automation maybe - you know. (Seems very
advanced request).
(I heaven't checked your link yet ).
Second part of yours is advanced to me.
Seems that I have to try harder from now on.
Thank you for your time.
"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:kr1o42hvi5h1pjuj9ama8o4a0a7rabseee@4ax.com...
> On Mon, 24 Apr 2006 00:46:02 +0200, andro wrote:
>
>>Hugo !
>>
>>Thank you very much for your help.
>>(I've noticed that such a simple question is not so simple to resolve with
>>basic SQL knowledge such as mine).
>>It is difficult to use first approach by entering everytime TBL_NAME
>>(system
>>here).
>
> Hi Andro,
>
> I'm sorry, I don't understand this part of your message.
>
>>
>>Is there any easier way - by using table names as "parameters"?
>
> There is, but it's not recommended, because of the security
> implications. If you want to read about the method *and* the risks it
> has, go to http://www.sommarskog.se/dynamic_sql.html.
>
>>
>>I like your second approach.
>>Would you be more specific about "extra column (***part of the primary
>>key***)"
>>What do you mean by "part of the primary key"?
>
> That would be easier to answer if you had told me exactly how yoour
> tables currently look.
>
> I'll use a made-up example to illustrate this. Consider the following
> (bad!) design for daily sales data:
>
> CREATE TABLE North_Sales
> (SaleDate smalldatetime NOT NULL,
> ProductNo int NOT NULL,
> AmountSold int NOT NULL,
> PRIMARY KEY (SaleDate, ProductNo),
> FOREIGN KEY (ProductNo) REFERENCES Products
> )
> CREATE TABLE East_Sales
> (SaleDate smalldatetime NOT NULL,
> ProductNo int NOT NULL,
> AmountSold int NOT NULL,
> PRIMARY KEY (SaleDate, ProductNo),
> FOREIGN KEY (ProductNo) REFERENCES Products
> )
> (and two more for regions South and West)
>
> These four tables can and should be replaced by this single table:
>
> CREATE TABLE Sales
> (SaleDate smalldatetime NOT NULL,
> Region char(5) NOT NULL,
> ProductNo int NOT NULL,
> AmountSold int NOT NULL,
> PRIMARY KEY (SaleDate, Region, ProductNo),
> FOREIGN KEY (ProductNo) REFERENCES Products,
> CHECK (Region IN ('North', 'South', 'East', 'West'))
> )
>
> Notice how I added a column "Region", _AND* added this column to the
> list of columns that make up the primary key.
>
> (I also added a check constraint - this should be replaced by a FOREIGN
> KEY constraint if there's a Regions table in the database as well).
>
> --
> Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|