I decided to do a quick post In addition to the posts by Peter and Louis. And yes I copied Louis' table and changed it a little :-)
Row Constructor will get the most use by sending in a bunch of data to a proc and execute that. This way the app can just concatenate a bunch of values without having to call the proc 20 times or even having to know how many rows are affected. Another good thing about this is that it either succeeds or fails. If you call a proc 20 times to insert a row and one of them fails then you have to have special logic to delete all the other rows.
Here is how you would do that with dynamic SQL, of course you would sanitize the input to check for SQL injection.
--Create the table
CREATE
TABLE SomeRating (
SomeRatingId
int NOT NULL,
Code
varchar(20) NOT NULL,
Description varchar(200) NULL,
AllowYouthRentalFlag
bit NOT NULL
)
GO
--put all that comma delimited junk in a variable
DECLARE
@V varchar(max)
SELECT
@V = '(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)'
DECLARE
@SQL varchar(max)
SELECT
@SQL= 'INSERT INTO SomeRating (SomeRatingId, Code, Description, AllowYouthRentalFlag)' +
'VALUES '
+ @v
--print, after all this is our unit test :-)
PRINT
@SQL
--Here is where the magic happens, or so they say
EXEC
(@SQL)
--Look at all that data
SELECT
* FROM SomeRating