May 2, 2012 at 6:22 am
Hi,
I want to insert 100 rows like (1,2,3,4,..100) without using loops into a table using CTE. can you please help.
May 2, 2012 at 6:28 am
NLV (5/2/2012)
Hi,I want to insert 100 rows like (1,2,3,4,..100) without using loops into a table using CTE. can you please help.
WITH CTE(n) AS(SELECT 1
FROM (VALUES(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1))a(n))
INSERT INTO dbo.yourTable
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM CTE x, CTE y;
May 2, 2012 at 6:28 am
create table #T (
Col1 int);
insert into #T (Col1)
values (1),(2),(3),(4),(5);
Like that? Or something else?
- 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
May 2, 2012 at 6:36 am
I would use any system table that has more than 100 rows
With Top100 (SrNo)
AS
(select top 100 ROW_NUMBER() over(order by Name) SrNo from sys.all_columns)
Select SrNo from Top100
Daxesh
May 2, 2012 at 6:42 am
Thanks for the help Daxesh.
May 2, 2012 at 7:17 am
Cadavre (5/2/2012)
NLV (5/2/2012)
Hi,I want to insert 100 rows like (1,2,3,4,..100) without using loops into a table using CTE. can you please help.
WITH CTE(n) AS(SELECT 1FROM (VALUES(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1))a(n))
INSERT INTO dbo.yourTable
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM CTE x, CTE y;
This join syntax has been deprecated. You shouldn't be using it in new development. Use CROSS JOIN instead.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 2, 2012 at 8:09 am
does the following count as a loop?
INSERT INTO dbo.yourTable default values
go 100
??:-P??
MVDBA
May 2, 2012 at 8:25 am
drew.allen (5/2/2012)
Cadavre (5/2/2012)
NLV (5/2/2012)
Hi,I want to insert 100 rows like (1,2,3,4,..100) without using loops into a table using CTE. can you please help.
WITH CTE(n) AS(SELECT 1FROM (VALUES(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1))a(n))
INSERT INTO dbo.yourTable
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM CTE x, CTE y;
This join syntax has been deprecated. You shouldn't be using it in new development. Use CROSS JOIN instead.
Drew
Per what?
The Outer Join syntax using a comma and *= or =* has been deprecated, but when was a comma for a cross join deprecated? I must have missed that one.
Edit:
Checked the SQL 2012 documentation. Still lists the From clause as:
[ FROM { <table_source> } [ ,...n ] ]
So a comma-separated list of table sources is still active per MSDN.
- 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
May 2, 2012 at 11:03 am
Cadavre (5/2/2012)
FROM CTE x, CTE y;
drew.allen (5/2/2012)
This join syntax has been deprecated. You shouldn't be using it in new development. Use CROSS JOIN instead.
GSquared (5/2/2012)
Per what?The Outer Join syntax using a comma and *= or =* has been deprecated, but when was a comma for a cross join deprecated? I must have missed that one.
It's kind of hidden. While the deprecation list only specifically mentions *= and =*, this article http://msdn.microsoft.com/en-us/library/dd172122%28v=vs.90%29.aspx mentions both INNER and OUTER JOINs being deprecated. Since INNER JOINs don't use *= or =* by definition, they must be referring to the comma separation. Also, since any INNER JOIN can obviously be written as a CROSS JOIN with an equivalent WHERE clause, the only way they can discontinue the use of old-style INNER JOINs is to also discontinue the use of old-style CROSS JOINs.
Checked the SQL 2012 documentation. Still lists the From clause as:
[ FROM { <table_source> } [ ,...n ] ]
So a comma-separated list of table sources is still active per MSDN.
I said that the old-style joins were deprecated, not discontinued. Obviously, MS hasn't decided to officially discontinue the old-style joins, yet.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 2, 2012 at 11:12 am
drew.allen (5/2/2012)
Cadavre (5/2/2012)
FROM CTE x, CTE y;
drew.allen (5/2/2012)
This join syntax has been deprecated. You shouldn't be using it in new development. Use CROSS JOIN instead.GSquared (5/2/2012)
Per what?The Outer Join syntax using a comma and *= or =* has been deprecated, but when was a comma for a cross join deprecated? I must have missed that one.
It's kind of hidden. While the deprecation list only specifically mentions *= and =*, this article http://msdn.microsoft.com/en-us/library/dd172122%28v=vs.90%29.aspx mentions both INNER and OUTER JOINs being deprecated. Since INNER JOINs don't use *= or =* by definition, they must be referring to the comma separation. Also, since any INNER JOIN can obviously be written as a CROSS JOIN with an equivalent WHERE clause, the only way they can discontinue the use of old-style INNER JOINs is to also discontinue the use of old-style CROSS JOINs.
Checked the SQL 2012 documentation. Still lists the From clause as:
[ FROM { <table_source> } [ ,...n ] ]
So a comma-separated list of table sources is still active per MSDN.
I said that the old-style joins were deprecated, not discontinued. Obviously, MS hasn't decided to officially discontinue the old-style joins, yet.
Drew
Not seeing that in there. It says *= and =* are deprecated. Nothing else.
It even links to an article on "Using Inner Joins" which says:
In the ISO standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that ISO supports in the WHERE clause. Inner joins specified in the WHERE clause are known as old-style inner joins.
Using a comma to join tables in this manner, as a Cross Join (whether filtered later in the Where clause or not) is part of the current ISO standard for SQL queries. It's documented as being valid in MSDN. And the article you mention is about old-style outer joins being deprecated and is very specific about that.
- 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
May 2, 2012 at 12:27 pm
It clearly says,
Joins that use the deprecated syntax fall into two categories:
Inner Join
...
Outer Join
or were you referring to not finding CROSS JOIN?
It doesn't specifically state anything about CROSS JOINS. That has to be inferred from what it says about INNER JOINS.
Also, remember that the main purpose for the ANSI JOINS is to clearly differentiate JOIN conditions from WHERE conditions and to prevent you from creating the wrong type of join. If you specify an INNER JOIN, you also have to specify an ON condition. You cannot accidentally create a CROSS JOIN using the ANSI syntax.
Using the comma syntax, you cannot easily distinguish inner join conditions from where conditions and you can forget to apply an inner join condition thereby accidentally creating a CROSS JOIN.
Furthermore, it's just clearer. If you specify CROSS JOIN, it's absolutely clear that you are expecting the Cartesian product of the two tables.
I simply see no advantage and plenty of disadvantages to using the comma syntax for joins of any type.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 3, 2012 at 8:27 am
drew.allen (5/2/2012)
It clearly says,Joins that use the deprecated syntax fall into two categories:
Inner Join
...
Outer Join
or were you referring to not finding CROSS JOIN?
It doesn't specifically state anything about CROSS JOINS. That has to be inferred from what it says about INNER JOINS.
Also, remember that the main purpose for the ANSI JOINS is to clearly differentiate JOIN conditions from WHERE conditions and to prevent you from creating the wrong type of join. If you specify an INNER JOIN, you also have to specify an ON condition. You cannot accidentally create a CROSS JOIN using the ANSI syntax.
Using the comma syntax, you cannot easily distinguish inner join conditions from where conditions and you can forget to apply an inner join condition thereby accidentally creating a CROSS JOIN.
Furthermore, it's just clearer. If you specify CROSS JOIN, it's absolutely clear that you are expecting the Cartesian product of the two tables.
I simply see no advantage and plenty of disadvantages to using the comma syntax for joins of any type.
Drew
I agree with you that CROSS JOIN being explicit is a superior syntax, for readability mainly. I don't use commas that way for that exact reason. But your claim that it's deprecated is, so far as I can tell, incorrect. That's all I'm disagreeing with.
- 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
May 3, 2012 at 8:31 am
I wasn't aware it had been deprecated, and reading on BOL I still don't think it has.
I do agree that CROSS JOIN is better than a comma, in this case I simply cut out part of a CTE tally table script that I keep in my "locker". Since all of those use the comma syntax in the CTE parts, so did my solution.
May 3, 2012 at 9:46 am
I agree with you that CROSS JOIN being explicit is a superior syntax, for readability mainly. I don't use commas that way for that exact reason. But your claim that it's deprecated is, so far as I can tell, incorrect. That's all I'm disagreeing with.
The link I provided earlier, http://msdn.microsoft.com/en-us/library/dd172122%28v=vs.90%29.aspx gives examples of deprecated syntax and updates.
-- Example 1: Deprecated syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] = [T2].[ID]
-- Example 2: Current syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
INNER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]
both of these are equivalent to the following
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2
CROSS JOIN [dbo].[Table1] T1
WHERE [T1].[ID] = [T2].[ID]
Clearly the deprecated syntax does not use the *= or =* syntax, but it is still listed as being deprecated. So what is it about that particular syntax that is being deprecated? Clearly it's not the SELECT clause, because the SELECT clause is exactly the same in the deprecated and current examples.
It's also fairly clear that the explicit CROSS JOIN with the WHERE clause isn't being deprecated, even thought it's not the most straightforward approach. Since the only difference between the explicit CROSS JOIN and the comma CROSS JOIN is the comma syntax, it appears that the comma syntax is what is being deprecated. I think that CROSS JOIN was left out, because it's just so rarely used, and also because the person who wrote it up wasn't clear, for whatever reason, on exactly what was being deprecated.
If you have some other logical explanation for why only the first example is being deprecated, I would love to hear it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 3, 2012 at 10:07 am
drew.allen (5/3/2012)
I agree with you that CROSS JOIN being explicit is a superior syntax, for readability mainly. I don't use commas that way for that exact reason. But your claim that it's deprecated is, so far as I can tell, incorrect. That's all I'm disagreeing with.
The link I provided earlier, http://msdn.microsoft.com/en-us/library/dd172122%28v=vs.90%29.aspx gives examples of deprecated syntax and updates.
-- Example 1: Deprecated syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] = [T2].[ID]
-- Example 2: Current syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
INNER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]
both of these are equivalent to the following
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2
CROSS JOIN [dbo].[Table1] T1
WHERE [T1].[ID] = [T2].[ID]
Clearly the deprecated syntax does not use the *= or =* syntax, but it is still listed as being deprecated. So what is it about that particular syntax that is being deprecated? Clearly it's not the SELECT clause, because the SELECT clause is exactly the same in the deprecated and current examples.
It's also fairly clear that the explicit CROSS JOIN with the WHERE clause isn't being deprecated, even thought it's not the most straightforward approach. Since the only difference between the explicit CROSS JOIN and the comma CROSS JOIN is the comma syntax, it appears that the comma syntax is what is being deprecated. I think that CROSS JOIN was left out, because it's just so rarely used, and also because the person who wrote it up wasn't clear, for whatever reason, on exactly what was being deprecated.
If you have some other logical explanation for why only the first example is being deprecated, I would love to hear it.
Drew
Actually this material wasn't written by SQL Server team and I would say it's irrelevant to MS T-SQL...
Scroll page down and read the comment (which many would agree with, as it's a true)...
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply