|
Comments
|
Mike Dumais on
12/15/2008
It might be the audio, but I was unable to make out some of the verbage when describing the use of this statement:
SELECT employeeID, employeeName
FROM (VALUES (1, 'Jeff Brown'), (2, 'Jim Smith')) T(employee_ID, employeeName);
I take it that this is just a short version of doing:
CREATE Table Employees (
employee_ID INT PRIMARY KEY,
employeeName VARCHAR (35));
INSERT INTO Employees VALUES (1, 'Jeff Brown'), (2, 'Jim Smith');
SELECT employeeID, employeeName
FROM Employees;
DROP TABLE EMployees;
|
|
Robert McKee on
12/15/2008
Video was good, however the quiz at the end was incorrect. You may insert many rows in SQL 2005 with a single INSERT command by using the INSERT ... SELECT variant.
|
|
Bill Nicolich on
12/15/2008
Difficult to understand the speaking voice.
|
|
|
I think it could use a bit more context - that is, when would you use row-table constructors (especially the derived table example).
|
|
|
Accent is a little hard to follow at times
|
|
Robert Neal on
12/15/2008
hard to understand and video ended before demo of using row value contructor in select.
|
|
Kris Robinett on
12/15/2008
Hard to understand speech.
|
|
Plamen Ratchev on
12/15/2008
Mike, the syntax to generate a derived table on the fly:
SELECT employeeID, employeeName
FROM (VALUES (1, 'Jeff Brown'), (2, 'Jim Smith')) T(employeeID, employeeName); shows the use of row value constructors instead of multiple UNION statements: SELECT employeeID, employeeName
FROM (SELECT 1, 'Jeff Brown' UNION ALL SELECT 2, 'Jim Smith') T(employeeID, employeeName);. The row value constructor version is more compact and intuitive.
|
|
Plamen Ratchev on
12/15/2008
Eric, the use of row value constructors in derived tables is useful when you need to create nomenclatures on the fly. For example, if you have table with test results where the answer is stored as Y or N, then to decode Y and N to Yes and No you can use a derived table: SELECT T.question, C.description
FROM Tests AS T
JOIN (VALUES('Y', 'Yes'), ('N', 'No')) AS C(code, description)
ON T.answer = C.code; Another example is generating a simple table with numbers: SELECT n
FROM (VALUES(1), (2), (3), (4), (5)) AS Numbers(n);
|
|
Plamen Ratchev on
12/15/2008
Robert, you are correct, the quiz should have emphasized how many rows can be inserted with a single INSERT VALUES statement. Using INSERT SELECT you can insert multiple rows in prior versions of SQL Server.
|
|
|
Can a subsequent query refer to the table T defined in the second example
|
|
Plamen Ratchev on
12/15/2008
Marc, if you need to reference the derived table created with row value constructors multiple times in the query, you have to repeat the whole expression defining the derived table. A better approach is to use a Common Table Expression, like this: WITH T
AS (SELECT employeeID, employeeName
FROM (VALUES (1, 'Jeff Brown'), (2, 'Jim Smith'))
AS A(employeeID, employeeName))
SELECT employeeID, employeeName FROM T; This way you can reference the common table expression multiple times in the query. However, if you need another query that is not part of the common table expression to reference the table, then you have to define a view or use an inline table valued function. Here is example using a view: CREATE VIEW Employees
AS
SELECT employeeID, employeeName
FROM (VALUES (1, 'Jeff Brown'), (2, 'Jim Smith'))
AS T(employeeID, employeeName);
GO
SELECT employeeID, employeeName FROM Employees;
|
|
|
i HAVE NOT ABLE TO SEE THIS VIDEO
|
|
Plamen Ratchev on
12/16/2008
Robert, there is not demo of using row value constructor in SELECT. The row value constructor serves as source for generating data rows. As such it can be used to feed INSERT statements, or in the FROM clause, but there is not direct use in the SELECT clause. The SELECT clause can reference the columns generated by a row value constructor in the FROM clause, as it was demonstrated in the examples. I have a few more examples on my blog: http://pratchev.blogspot.com/2008/01/table-value-constructors-in-sql-server.html
|
|
|
1. Would appreciate if you type less (keep most of the SQL queries already typed in the Query Window) and focus more on speaking/demoing part.
2. Make your code available, if somebody wants to try it later
|
|
|
Nice quick clear highlight of something that's been seriously lacking in T-SQL.
|
|
|
The first 90 seconds was good. The last 30 seconds was confusing and I did not understand.
|
|
Kris Robinett on
5/27/2011
Did he create a table in the 2nd step or was it just a select statement - unclear.
|
|
Keith Cooper on
5/27/2011
good information, BUT it was very difficult to understand what was being said due to the dialect of the speaker.
|
|
|
I'm learning every day a new thing from you
Thanks so much.
|
|
Robert McEuen on
5/27/2011
Scripts should have been prepared beforehand so more time could be spent explaining practical use of the discussed item intead of watching him type.
|
|
|
Very good
|
|
Darren Bates on
5/27/2011
Great example. Voice accent was sometimes difficult to understand, but not too bad.
|
|
|
Accent was hard to follow, but content was good. The next step is to take this syntax and use it in a merge statement to update a table with a fixed set of values.
|
|
|
The content was great but I had trouble understanding the accent. The I didn't get the second example. It went by too fast.
|
|
|
This guy is very difficult to understand and his English has not improved at all since the first time I tried to figure out what he was saying. He also wasted good explanation time by having us watch him type- boring.
|
|
|
hard to understand what he was saying
|
|
Keith Badeau on
5/27/2011
I didn't know about being able to use row constructors in a derived table. Good to know. And to think I almost didn't watch the video because it was too "elementary".
|
|
|
good demo
|
|
|
One more, slightly more complex, example would have been good, but wheels are turning...
|
|
|
Thank you! I was trying to remember how the same technique the other day.
|
|
|
Thank you! I was trying to remember how to use the same technique the other day.
|
|
|
simple but great examples. Good job
|
|
|
Very simple and excellent
|
|
|
Good !
|
|
|
If the scripts were created before the video started, we would not have to wait for typing instructor.
|
|
|
Hard to understand his dialect...
|
|
Maurice Ivory on
11/2/2011
It was pretty good, but the voice has difficult to grasp.
|
|
|
Question #1 on the quiz (which I did get 100% correct :D) is technically incorrectly posed, since you can in fact INSERT unlimited rows with a single INSERT statement in SQL 2005, just not with the VALUES clause. :)
|