|
|
You're in the
sql server programming
group:Is there a better way to do this very simple query(s)?
sql server programming:
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 week 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 [quoted text, click to view] "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 >
[quoted text, click to view] "--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.
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 .. [quoted text, click to view] "Tom" <Tom@discussions.microsoft.com> wrote in message I' m almost certain that my approach is wrong but it works! Could someonenews:F747781D-B975-4664-AAF8-90C5A0EFB536@microsoft.com... 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
Actually, it dawned on me that the query would work if the table weren't
already populated. I keyed in on that UPDATE of yours and thought to myself that it would set the entire table's statuses to 1. I then wrote the query but forgot to post a follow-up on that. Enjoy. :-) -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. [quoted text, click to view] "Tom" <Tom@discussions.microsoft.com> wrote in message Tom,news:F8872EC5-DA5B-4A42-AA16-F1AE13A9A476@microsoft.com... 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 week 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 [quoted text, click to view] "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 > >
[quoted text, click to view]
>> If I wanted to be honored by your intelligence I would have contacted you directly. << You might want to learn how newsgroups work. [quoted text, click to view] >> although I agree it is a bad name, I didn't name it! << No, you just posted it without any clean up. And without any DDL, either. [quoted text, click to view] >> I disagree with you on the "StatusID", the value actually comes from a lookup table that makes perfect sense to me,<< 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? [quoted text, click to view] "--CELKO--" wrote: > >> If I wanted to be honored by your intelligence I would have contacted you directly. << > > 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. [quoted text, click to view] > >> although I agree it is a bad name, I didn't name it! << > > 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, actually in this case LACK of intelligence. [quoted text, click to view] > >> I disagree with you on the "StatusID", the value actually comes from a lookup table that makes perfect sense to me,<< > > 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! [quoted text, click to view] > 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!!! [quoted text, click to view] >
> "--CELKO--" wrote: > >> If I wanted to be honored by your intelligence I would have contacted you directly. << > > You might want to learn how newsgroups work. > > >> although I agree it is a bad name, I didn't name it! << > > No, you just posted it without any clean up. And without any DDL, > either. > > >> I disagree with you on the "StatusID", the value actually comes from a lookup table that makes perfect sense to me,<< > > 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. >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |