Well, I am just discovering this feature, mostly because I never saw it demoed at any of the sessions I have attended so far on 2008. Not that it was kept particularly hidden, I have seen the title before, but I hadn’t tried it out, or seen the depth that they have “finally” implemented.<\/p>\n
Tonight, I am working on my chapter where I create some tables as part of a big example, and I had the code from the 2005 version of the book (and I add the primary key to the table later in the book, as well as other constraints, so don’t judge me!):<\/p>\n
CREATE TABLE Inventory.MovieRating (
MovieRatingId int NOT NULL,
Code varchar(20) NOT NULL,
Description varchar(200) NULL,
AllowYouthRentalFlag bit NOT NULL
)
GO<\/p>\n
INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUES (0, ‘UR’,’Unrated’,1)
INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUE (1, ‘G’,’General Audiences’,1),
INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUES (2, ‘PG’,’Parental Guidance’,1),
INSERT INTO Inventory.MovieRating(MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUES (3, ‘PG-13′,’Parental Guidance for Children Under 13’,1),
INSERT INTO Inventory.MovieRating(MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUES (4, ‘R’,’Restricted, No Children Under 17 without Parent’,0)<\/p>\n
(Another variety is to use:<\/p>\n
INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag)
SELECT 0, ‘UR’,’Unrated’,1
UNION ALL
SELECT 1, ‘G’,’General Audiences’,1
UNION ALL
SELECT 3, ‘PG-13′,’Parental Guidance for Children Under 13’,1
UNION ALL
SELECT 4, ‘R’,’Restricted, No Children Under 17 without Parent’,0<\/p>\n
But that is not that much better (certainly a little better). I felt for the book that using VALUES was the more “proper” way to do it. However, now, in the 2008 edition, I obviously have to change all <\/em>of the code to use the latest and greatest syntax, so I use row constructors, and this turns into:<\/p>\n INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) And it just strikes me at how…simple this is, and how readable this is. I know I have typed the UNION ALL stuff hundreds, possibly thousands (well hundreds more like) of times in the forums over the past year and a half dummying up data for someone who was asking for help but couldn’t take the time to do it for us.<\/p>\n","protected":false},"excerpt":{"rendered":" Well, I am just discovering this feature, mostly because I never saw it demoed at any of the sessions I have attended so far on 2008. Not that it was kept particularly hidden, I have seen the title before, but I hadn’t tried it out, or seen the depth that they have “finally” implemented. Tonight,……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82026","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82026","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82026"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82026\/revisions"}],"predecessor-version":[{"id":82374,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82026\/revisions\/82374"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82026"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82026"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82026"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82026"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
VALUES (0, ‘UR’,’Unrated’,1),
(1, ‘G’,’General Audiences’,1),
(2, ‘PG’,’Parental Guidance’,1),
(3, ‘PG-13′,’Parental Guidance for Children Under 13’,1),
(4, ‘R’,’Restricted, No Children Under 17 without Parent’,0)<\/p>\n