March 17, 2009 at 10:37 am
Lynn Pettis (3/17/2009)[
Sorry, had to make a slight correction, emphasis is mine.
Woops! with my limited command of the Englsih language (not my mother tonge) I assumed Lynn was a female name.:blush:
March 17, 2009 at 10:45 am
Well for you folks that are still here.
What I am speaking about is language, not about application.
It seems many have failed to grasp that simple concept, and appear too conceded and bull headed to even try.
why would any of you think that foreach(select something) impies a cursor within t-sql?
Is it because the only way we are able to do a loop in t-sql is with a cursor?
As some friendly people pointed out on the msdn forums, they suggested using SSIS, which I think is a great solution. But by the same tolken, it would seem easier to make stuff with scripts for some of the on-the fly things I am likely to do.
When it is possible for MS to change the language of t-sql, and the fact that they already have in 2008, as well as added debugging capability. It seems more likely they will make it more c like in the future.
March 17, 2009 at 11:15 am
foxjazz,
You are like this guy who learnt how to use a hammer. For him the only way to join two pieces is by using a nail. He does not grasp a concept that sometimes a screw, a glue or a bolt could be a better fit.
It would help you if you made an effort and read some of the replies...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 17, 2009 at 11:16 am
If I were you, change jobs so you can be fully a C# developer. If we may believe you, SQL will be obsolete in a few years anyway.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
March 17, 2009 at 11:17 am
foxjazz (3/17/2009)
Well for you folks that are still here.What I am speaking about is language, not about application.
It seems many have failed to grasp that simple concept, and appear too conceded and bull headed to even try.
why would any of you think that foreach(select something) impies a cursor within t-sql?
Is it because the only way we are able to do a loop in t-sql is with a cursor?
As some friendly people pointed out on the msdn forums, they suggested using SSIS, which I think is a great solution. But by the same tolken, it would seem easier to make stuff with scripts for some of the on-the fly things I am likely to do.
When it is possible for MS to change the language of t-sql, and the fact that they already have in 2008, as well as added debugging capability. It seems more likely they will make it more c like in the future.
LOLOLOLOLOL!!!
If it makes you feel better to keep insisting on how (fill in the blank) everyone else is, while you are superior in every possible fashion, by all means, continue to feel that way.
After all, laughing at you has provided me with hours of entertainment so far. And I don't even have to pay for it!
So, please, keep going dude. Your posts are better than watching videos of skateboarders wipe out on stair railings, and are about the same kind of entertainment.
- 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
March 17, 2009 at 12:01 pm
foxjazz (3/17/2009)
why would any of you think that foreach(select something) impies a cursor within t-sql?Is it because the only way we are able to do a loop in t-sql is with a cursor?
It must since every bit of SQL code you write uses cursors. The following foreach(do something) implies a loop of some kind.
We have shown you that you DO NOT NEED CURSORS, but you don't seem to understand that at all. You are the one that keeps coming back to only being able to do things using cursors and loops.
Yes, SSIS has a FOREACH container. It has its uses, and it isn't to run SQL code one row at a time like a cursor. We use it to process files in the file system, which makes sense.
March 17, 2009 at 12:05 pm
conceded
The word is "conceited". Conceded is something you should have done days ago. Sorry, I couldn't resist that one. 😉 By the way, "tolken" should be "token".
Is it because the only way we are able to do a loop in t-sql is with a cursor?
No, there are also WHILE loops. You were the one who brought up cursors and insisted, quite incorrectly, that they were the only way to handle certain problems in the "real world, baby". Pretty much all of the rest of us know about cursors and WHILE loops, but choose not to use them because there are much faster set-based solutions.
Don't you know of some BAD ways to code in C# ?? I'm sure you do, but you have learned to avoid them, as soon as you learned a better way to do it. That's why everyone just keeps telling you that you don't understand SQL enough yet. Your examples proved that you don't get the core concepts yet. You know the words but you can't walk the walk. There is no shame in that if you would just accept it and ask for help rather than getting up on a soapbox to rant.
As some friendly people pointed out on the msdn forums, they suggested using SSIS, which I think is a great solution. But by the same tolken, it would seem easier to make stuff with scripts for some of the on-the fly things I am likely to do.
People here can be very friendly to those that truly come seeking assistance. Seems to me that you came spoiling for a fight. You would have gotten a far different response if your initial post had been "Can someone teach me how to do this without using a cursor?"
SSIS has many uses, but I predict you will spend hours working with it to do some things that could be done perfectly well in T-SQL.
By the way, debugging capability through Visual Studio has been around for a while. Some day you may hear about something called ADO.Net. Systems and languages change and evolve. If you truly want to be a part of the process of making them change for the better, drop the attitude and make constructive suggestions politely. More people will listen if you do. No guarantees they will agree with you, but at least the discussion will be positive and may lead to something productive.
I smile and shake my head when I listen to you, because you are ignorant of so many things, and yet so sure you know it all. You're obviously young and naieve in assuming that the world you grew up in is the world as it has always been. I will refrain from quoting the Maxims of Baloo, but anyone who cares to can Google them.
We understand what you've been saying, but we we see that you are way lost in the woods. However, since you obviously consider yourself head and shoulders above the crowd, why don't you just write yourself a front end that translates the syntax you prefer into SQL. Of course, for it to run efficiently, you would have to first understand SQL much better than you do today.
Better yet, hook up with Steve Dassin and explore using his preferred flavor. This suggestion is made in all earnestness, and without any sarcasm. Maybe you guys can change the world. Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 12:14 pm
J (3/17/2009)
Lynn Pettis (3/17/2009)[
Sorry, had to make a slight correction, emphasis is mine.
Woops! with my limited command of the Englsih language (not my mother tonge) I assumed Lynn was a female name.:blush:
No worries, several people have thought the same. 😉
March 17, 2009 at 12:30 pm
J (3/17/2009)
Lynn Pettis (3/17/2009)[
Sorry, had to make a slight correction, emphasis is mine.
Woops! with my limited command of the Englsih language (not my mother tonge) I assumed Lynn was a female name.:blush:
In the US, "Lynn" is almost four times as common for women as it is for men. It's not the language barrier that got you on that one. (Approx 1 in 700 US women is named "Lynn", while approx 1 in 2,600 men have the name. That doesn't account for varient spellings, just the specific version.)
- 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
March 17, 2009 at 12:43 pm
LOLOLOLOLOL!!!
If it makes you feel better to keep insisting on how (fill in the blank) everyone else is, while you are superior in every possible fashion, by all means, continue to feel that way.
After all, laughing at you has provided me with hours of entertainment so far. And I don't even have to pay for it!
So, please, keep going dude. Your posts are better than watching videos of skateboarders wipe out on stair railings, and are about the same kind of entertainment.
I so agree! I never knew SSC had such a humouristic angle.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
March 17, 2009 at 1:10 pm
Schadenfreude: Your best entertainment value.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 4:09 pm
The following is a script that I use to partally populate one of our tables.
And the table is provided.
based on the distinct results (from any table you would like to create) please write a set based solution to populate the given table. No, you can't change the structure of the table, or use an identity column.
--delete from category where categorytypeid = 1
declare @sel cursor
declare @make varchar(100)
declare @parentid int
declare @mx int
set @sel = cursor for
SELECT DISTINCT EquipmentMake.EquipmentMake, Category.GenCategoryID
FROM EquipmentMake INNER JOIN
EquipmentModel ON EquipmentMake.EquipmentMakeID = EquipmentModel.EquipmentMakeID INNER JOIN
EquipmentType ON EquipmentModel.EquipmentTypeID = EquipmentType.EquipmentTypeID INNER JOIN
EquipmentClass ON EquipmentType.EquipmentClassID = EquipmentClass.EquipmentClassID INNER JOIN
Category ON EquipmentClass.EquipmentClass = Category.Description
WHERE (Category.CategoryTypeID = 0)
open @sel
fetch next from @sel into @make,@parentid
while (@@fetch_status = 0)
begin
--select @mx = max(gencategoryid) + 1 from category
insert into category ( categorytypeid,parentid,description,hasrelation)
values(1,@parentid,@make,0)
fetch next from @sel into @make,@parentid
end
----------------
USE [PartsTrakStaging]
GO
/****** Object: Table [dbo].[Category] Script Date: 03/17/2009 16:02:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Category](
[Description] [varchar](150) NULL,
[ParentID] [int] NULL,
[CategoryTypeID] [int] NOT NULL,
[GenCategoryID] [int] IDENTITY(1,1) NOT NULL,
[ProductLineID] [int] NULL,
[BookMark] [bit] NULL,
[HasRelation] [bit] NOT NULL,
[Polk] [int] NULL,
[SourceId] [int] NULL,
[MaintID] [int] NULL,
[YearId] [int] NULL,
[TopParentId] [int] NULL,
[CID] [char](4) NULL,
[cc] [char](10) NULL,
[Cyl] [char](2) NULL,
[EngineType] [char](2) NULL,
[Fuel] [char](10) NULL,
[Liter] [char](5) NULL,
[FoD] [int] NULL,
[ChangedDate] [datetime] NULL,
[ChangedType] [char](1) NULL,
[here] [tinyint] NULL,
[MakeType] [int] NULL,
[Verified] [int] NULL,
[EquipmentID] [int] NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[GenCategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [FK_Category_CategoryType] FOREIGN KEY([CategoryTypeID])
REFERENCES [dbo].[CategoryType] ([CategoryTypeID])
GO
ALTER TABLE [dbo].[Category] CHECK CONSTRAINT [FK_Category_CategoryType]
GO
March 17, 2009 at 4:23 pm
This will have to wait until tomorrow because we're having guests over tonight to watch "The Quiet Man." Someone else may beat me to it. You probably should provide some sample structures for the underlying tables in the joins and just a few rows of sample data. Everyone gets asked for that so the coders can test their solutions. It's a courtesy to those who are helping you out.
No, you can't change the structure of the table, or use an identity column.
Just out of curiousity, what is the business requirement behind this? Is this for an existing table which you are forbidden to change? I ask only because I've seen people paint themselves into a corner by insisting that a bad structure was the only way to go. Some wise changes to the underlying tables can save a world of effort and headaches in the long run.
It would also be civil of you to ask nicely, rather than throw this out like an "Oh yeah?" This thread started out as a rant, but it doesn't have to end that way. If your bad attitude persists, you will find fewer and fewer people willing to help you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 4:26 pm
INSERT INTO Category (CategoryTypeID, ParentID, Description, HasRelation)
SELECT DISTINCT
1 As CategoryTypeID
,Category.GenCategoryID Ad ParentID
,EquipmentMake.EquipmentMake As Description
,0 As HasRelation
FROM EquipmentMake mk
INNER JOIN EquipmentModel mod ON mod.EquipmentMakeID = mk.EquipmentMakeID
INNER JOIN EquipmentType typ ON typ.EquipmenttypeID = mod.EquipmentTypeID
INNER JOIN EquipmentClass cls ON cls.EquipmentClassID = typ.EquipmentClassID
INNER JOIN Category cat ON cat.Description = cls.EquipmentClass
WHERE cat.CategoryTypeID = 0;
Does this work? I even cleaned it up for you a bit...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 17, 2009 at 4:33 pm
Sorry that doesn't work,
gencategoryid is the primary key on the table.
Although nice try.
Let me make the following correction to my t-sql...
--delete from category where categorytypeid = 1
declare @sel cursor
declare @make varchar(100)
declare @parentid int
declare @mx int
select @mx = max(gencategoryid) + 1 from category
set @sel = cursor for
SELECT DISTINCT EquipmentMake.EquipmentMake, Category.GenCategoryID
FROM EquipmentMake INNER JOIN
EquipmentModel ON EquipmentMake.EquipmentMakeID = EquipmentModel.EquipmentMakeID INNER JOIN
EquipmentType ON EquipmentModel.EquipmentTypeID = EquipmentType.EquipmentTypeID INNER JOIN
EquipmentClass ON EquipmentType.EquipmentClassID = EquipmentClass.EquipmentClassID INNER JOIN
Category ON EquipmentClass.EquipmentClass = Category.Description
WHERE (Category.CategoryTypeID = 0)
open @sel
fetch next from @sel into @make,@parentid
while (@@fetch_status = 0)
begin
--
insert into category (gencategoryid, categorytypeid,parentid,description,hasrelation)
values(@mx,1,@parentid,@make,0)
set @mx = @mx + 1
fetch next from @sel into @make,@parentid
end
Viewing 15 posts - 181 through 195 (of 465 total)
You must be logged in to reply to this topic. Login to reply