|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Is there a better way to do this very simple query(s)?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 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" <T**@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 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 Show quote "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" <T**@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 > > 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 .. "Tom" <T**@discussions.microsoft.com> wrote in message I'm sorry I left out the fact that the table is indeed empty.news:F8872EC5-DA5B-4A42-AA16-F1AE13A9A476@microsoft.com... Tom, 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 Show quote "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" <T**@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?
Show quote
"--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.>> 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. Itmight 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: I know how newsgroups work, that's why I posted the question here. Most > >> If I wanted to be honored by your intelligence I would have contacted you directly. << > > You might want to learn how newsgroups work. > people offer useable suggestions and solutions to the QUESTION ASKED not thier own take as to what it should be. > >> although I agree it is a bad name, I didn't name it! << Sorry, there was no cleanup to do! It was a simple question in need of a > > No, you just posted it without any clean up. And without any DDL, > either. > 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. > >> I disagree with you on the "StatusID", the value actually comes from a lookup table that makes perfect sense to me,<< In perticular what subsection of ISO-11179 are you talking about? You don't > > 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. > 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 I agree with your modeling statement but you are missing the point!> 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. 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!!! Show quote > > "--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. > >  |
|||||||||||||||||||||||
Other interesting topics