table hosed: Is there a better way to do this very simple query(s)?

I' m almost certain that my approach is wrong but it works! Could someone
tell me the correct way to achieve the following?
-- Start Code --
INSERT INTO
Categories (Description)
SELECT DISTINCT CategoryDescription
FROM Import
UPDATE Categories SET StatusID = 1
UPDATE Categories SET DateAdded = GETDATE()
-- End Code --
Also could someone suggest a good book for SQL programming? As you can tell
I am lost!
Thanks,
TomThis can be done during the INSERT (assuming the table is empty when you
begin):
INSERT INTO
Categories (Description, StatusID, DateAdded)
SELECT DISTINCT CategoryDescription, 1, GETDATE()
FROM Import
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:F747781D-B975-4664-AAF8-90C5A0EFB536@.microsoft.com...
I' m almost certain that my approach is wrong but it works! Could someone
tell me the correct way to achieve the following?
-- Start Code --
INSERT INTO
Categories (Description)
SELECT DISTINCT CategoryDescription
FROM Import
UPDATE Categories SET StatusID = 1
UPDATE Categories SET DateAdded = GETDATE()
-- End Code --
Also could someone suggest a good book for SQL programming? As you can tell
I am lost!
Thanks,
Tom|||Since you did not bother to expalin "Import", I am making some
assumptions.
INSERT INTO Categories (cat_description, foobar_status, start_date,
end_date)
SELECT DISTINCT cat_description, 1, start_date, end_date
FROM Import ;
There cannot such a thing as a "status_id" -- the data element is
either an identifier and belongs to one and only one entity. If it is a
status, it is a value that shows the status of some non-key attribute.
Status of what'
People who never learned RDBMS sometimes make fools of themselves by
using IDENTITY as the key for everything, just like they were still in
file systems and had to have a record number.
You do know that a temporal data element is modeled in durations, not
in points, don't you?|||Tom,
I'm sorry I left out the fact that the table is indeed empty.
That is exactly what I was looking for! Thank You! You brought up a good
point however, next w

when I get an updated category list from my vendor,
how would I go about adding only the new items to the Categories table and
setting the StatusID=1, DateAdded=GetDate()?
Thanks again, I really appreciate the help!!!
Tom
"Tom Moreau" wrote:

> This can be done during the INSERT (assuming the table is empty when you
> begin):
> INSERT INTO
> Categories (Description, StatusID, DateAdded)
> SELECT DISTINCT CategoryDescription, 1, GETDATE()
> FROM Import
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:F747781D-B975-4664-AAF8-90C5A0EFB536@.microsoft.com...
> I' m almost certain that my approach is wrong but it works! Could someone
> tell me the correct way to achieve the following?
> -- Start Code --
> INSERT INTO
> Categories (Description)
> SELECT DISTINCT CategoryDescription
> FROM Import
> UPDATE Categories SET StatusID = 1
> UPDATE Categories SET DateAdded = GETDATE()
> -- End Code --
> Also could someone suggest a good book for SQL programming? As you can
> tell
> I am lost!
> Thanks,
> Tom
>|||
"--CELKO--" wrote:

> Since you did not bother to expalin "Import", I am making some
> assumptions.
> INSERT INTO Categories (cat_description, foobar_status, start_date,
> end_date)
> SELECT DISTINCT cat_description, 1, start_date, end_date
> FROM Import ;
> There cannot such a thing as a "status_id" -- the data element is
> either an identifier and belongs to one and only one entity. If it is a
> status, it is a value that shows the status of some non-key attribute.
> Status of what'
> People who never learned RDBMS sometimes make fools of themselves by
> using IDENTITY as the key for everything, just like they were still in
> file systems and had to have a record number.
> You do know that a temporal data element is modeled in durations, not
> in points, don't you?
>|||CELKO,
Go to bed! If I wanted to be honored by your intelligence I would have
contacted you directly. My question was simple and answered in a prompt,
precise and professional manner.
Tom Moreau, understood my question, thanks Tom.
Your question, and "solution" for that matter, is far from what I was
asking. My question dealt with 3 (three) columns, your solution 4 (four)!
"Import" is a table, although I agree it is a bad name, I didn't name it!
And finally I disagree with you on the "StatusID", the value actually comes
from a lookup table that makes perfect sense to me, my company and most of
the rest of the world! I simplified the sample query to get my question
answered! StatusID id NOT an IDENTITY but a foreign key.
Thanks Again Tom!|||>> If I wanted to be honored by your intelligence I would have contacted you
directly. <<
You might want to learn how newsgroups work.
No, you just posted it without any clean up. And without any DDL,
either.
Fine, but it makes no sense to anyone who uses ISO-11179 standards. It
might not be your company, but it is the rest of the world. But those
who go fishing for quick kludges are probably not going to learn such
things.
And the reason that I gave you four columns is that they model three
data elements. Again, time is modeled in durations of helf-open
intervals. Look up the work done for the past few decades by Rick
Snodgrass at the University of Arizona. Looking at my answer, I think
I would change it to at least this:
INSERT INTO Categories (cat_description, foobar_status, start_date,
end_date)
SELECT DISTINCT cat_description, 1, CURRENT_TIMESTAMP, NULL
FROM ImportStagingTable;
But I would squeeze out blanks, watch the case of the description
string, etc.|||CELKO,
Go away already will you?
"--CELKO--" wrote:

> You might want to learn how newsgroups work.
>
I know how newsgroups work, that's why I posted the question here. Most
people offer useable suggestions and solutions to the QUESTION ASKED not
thier own take as to what it should be.

> No, you just posted it without any clean up. And without any DDL,
> either.
>
Sorry, there was no cleanup to do! It was a simple question in need of a
simple answer! I'm sorry it was beneath you. I agree there was no DDL but
my question was so SIMPLE I didn't feel it was necessary. I simply asked a
SIMPLE question and you felt the need to show off your intelligence, actuall
y
in this case LACK of intelligence.

> Fine, but it makes no sense to anyone who uses ISO-11179 standards. It
> might not be your company, but it is the rest of the world. But those
> who go fishing for quick kludges are probably not going to learn such
> things.
>
In perticular what subsection of ISO-11179 are you talking about? You don't
know this database and therefore you have no business telling what it does
and does not comply to!

> And the reason that I gave you four columns is that they model three
> data elements. Again, time is modeled in durations of helf-open
> intervals. Look up the work done for the past few decades by Rick
> Snodgrass at the University of Arizona. Looking at my answer, I think
> I would change it to at least this:
> INSERT INTO Categories (cat_description, foobar_status, start_date,
> end_date)
> SELECT DISTINCT cat_description, 1, CURRENT_TIMESTAMP, NULL
> FROM ImportStagingTable;
> But I would squeeze out blanks, watch the case of the description
> string, etc.
I agree with your modeling statement but you are missing the point!
Why are you forcing me an end date when I don't need one? Not that it is
either important OR any of your business, I am tracking fish being released
and I need to know the "description", "status", and "date" of the release.
Very simple!!!

>
"--CELKO--" wrote:

> You might want to learn how newsgroups work.
>
> No, you just posted it without any clean up. And without any DDL,
> either.
>
> Fine, but it makes no sense to anyone who uses ISO-11179 standards. It
> might not be your company, but it is the rest of the world. But those
> who go fishing for quick kludges are probably not going to learn such
> things.
> And the reason that I gave you four columns is that they model three
> data elements. Again, time is modeled in durations of helf-open
> intervals. Look up the work done for the past few decades by Rick
> Snodgrass at the University of Arizona. Looking at my answer, I think
> I would change it to at least this:
> INSERT INTO Categories (cat_description, foobar_status, start_date,
> end_date)
> SELECT DISTINCT cat_description, 1, CURRENT_TIMESTAMP, NULL
> FROM ImportStagingTable;
> But I would squeeze out blanks, watch the case of the description
> string, etc.
>

Friday, March 23, 2012

Is there a better way to do this very simple query(s)?

No comments:

Post a Comment

table hosed

Blog Archive

About Me