August 24, 2012 at 9:02 am
Hi,
I am newbie. I was reading some very basic stuff about TVC. I had seen that in one example the author has used TVC to insert the data where as in the other example he used the normal insert statement. I would like to know that when we were having simple insert statement to insert into the table then why they invented TVC. 🙂
August 24, 2012 at 9:11 am
TVCs simplify a number of things.
Before, you had to do one of these things:
insert into dbo.MyTable (Col1, Col2)
values (1, 2);
insert into dbo.MyTable (Col1, Col2)
values (2, 2);
insert into dbo.MyTable (Col1, Col2)
values (3, 2);
insert into dbo.MyTable (Col1, Col2)
values (4, 2);
insert into dbo.MyTable (Col1, Col2)
values (5, 2);
or
insert into dbo.MyTable (Col1, Col2)
select 1, 2 union all
select 2, 2 union all
select 3, 2 union all
select 4, 2 union all
select 5, 2;
Now you can do:
insert into dbo.MyTable (Col1, Col2)
values (1,2),(2,2),(3,2),(4,2),(5,2);
It's less typing.
You can also use them in other places.
with Seeds as (
select Val from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) TCV(Val),
Numbers as (select row_number() over (order by S1.Val) as Number
from Seeds as S1
cross join Seeds as S2
cross join Seeds as S3
cross join Seeds as S4)
select Number
from Numbers;
That construct gives you a very simple inline-Numbers-table, which can be used for a large number of things.
Is that better than a whole string of Union All statements? It does the same thing, but it's less typing. I also consider it easier to read when it's a large number of values.
Using the Concatenate function, it also makes it very easy to turn Excel data into something you can copy-and-paste into a query window.
So, no, it's not some huge improvement over typing Union All strings, but it is nice to have.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 24, 2012 at 9:58 am
TVC implies a set of row value expressions; single-row value expressions can be very useful, see this article [/url]by Dangler Dwain.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 26, 2012 at 6:39 pm
ChrisM@Work (8/24/2012)
TVC implies a set of row value expressions; single-row value expressions can be very useful, see this article [/url]by Dangler Dwain.
Just what am I dangling? I didn't think the resolution on my picture was that high! 😛
I prefer Angler Dwain or just Fishman.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply