SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author

Clustered Instances

Steve Jones - SSC Editor — Thu, 15 Dec 2011 22:39:06 GMT

Comments posted to this topic are about the item [B]Clustered Instances[/B]

Checking database access

Steve Jones - SSC Editor — Tue, 10 Jan 2012 20:49:52 GMT

Comments posted to this topic are about the item [B]Checking database access[/B]

SQL Server Agent Proxies

Steve Jones - SSC Editor — Mon, 14 May 2012 22:10:15 GMT

Comments posted to this topic are about the item [B]SQL Server Agent Proxies[/B]

You have 1,000,000 records in your customers table....

Steve Jones - SSC Editor — Fri, 03 Apr 2009 12:23:56 GMT

Comments posted to this topic are about the item [B]You have 1,000,000 records in your customers table....[/B]

Which SQL Server 2000 upgrade path is invalid?

Steve Jones - SSC Editor — Fri, 27 Mar 2009 15:21:32 GMT

Comments posted to this topic are about the item [B]Which SQL Server 2000 upgrade path is invalid?[/B]

You need to create a DTSRUN command to...

Steve Jones - SSC Editor — Sun, 23 Sep 2007 12:57:43 GMT

Comments posted to this topic are about the item [B]You need to create a DTSRUN command to...[/B]

Your Windows 2000 IIS application is installed on...

Steve Jones - SSC Editor — Mon, 25 Feb 2008 08:51:45 GMT

Comments posted to this topic are about the item [B]Your Windows 2000 IIS application is installed on...[/B]

Setup SQL Server on Amazon RDS

Steve Jones - SSC Editor — Tue, 08 May 2012 22:29:05 GMT

Comments posted to this topic are about the item [B]Setup SQL Server on Amazon RDS[/B]

SQL server 2008 Build List

Steve Jones - SSC Editor — Thu, 25 Sep 2008 13:10:12 GMT

Comments posted to this topic are about the item [B]SQL server 2008 Build List[/B]

Contained Database users

Steve Jones - SSC Editor — Wed, 11 Apr 2012 23:43:04 GMT

Comments posted to this topic are about the item [B]Contained Database users[/B]

Without cursor i need the solution to update 12 lakhs records

msmartsairam — Thu, 12 Apr 2012 09:12:30 GMT

Hi,Please suggest me how do i update the below workorderidtemp table colums claimid,cliamseq without using below cursorI had a workorderid's which is having child id's as parentid's , i don't know how many parentid's does one workorderid have i need to update claimid as 1 those parentid's which comes under that workorderid.At the same i need to update claimseq like if one workorderid have 10 parentid's the latest parentid has a claimseq 10 and workorderid has a claimseq 1.i have sample table with data likeCREATE TABLE [dbo].[workorderidtemp]( [WorkOrderID] [int] NOT NULL, [ParentID] [int] NULL, [ClaimID] [int] NULL, [ClaimSeq] [int] NULL, [calldate] [datetime] NULL) ON [PRIMARY]INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1','0','0','0',convert(datetime,'Jan 1 2001 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('11','1','0','0',convert(datetime,'Jan 1 2002 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('12','1','0','0',convert(datetime,'Jan 1 2003 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('13','1','0','0',convert(datetime,'Jan 1 2003 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('111','11','0','0',convert(datetime,'Jan 2 2004 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('112','11','0','0',convert(datetime,'Jan 5 2004 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('113','12','0','0',convert(datetime,'Jan 3 2004 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('114','13','0','0',convert(datetime,'Jan 3 2003 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('115','12','0','0',convert(datetime,'Jan 2 2004 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1111','111','0','0',convert(datetime,'Jan 2 2005 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1112','111','0','0',convert(datetime,'Jan 2 2006 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1113','113','0','0',convert(datetime,'Jan 2 2007 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1114','112','0','0',convert(datetime,'Jan 3 2005 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1115','113','0','0',convert(datetime,'Jan 8 2007 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1116','114','0','0',convert(datetime,'Jan 12 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1117','115','0','0',convert(datetime,'Feb 2 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('21','2','0','0',convert(datetime,'Feb 23 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('22','2','0','0',convert(datetime,'Feb 25 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('23','2','0','0',convert(datetime,'Mar 2 2009 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('221','21','0','0',convert(datetime,'Dec 2 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('222','21','0','0',convert(datetime,'Feb 3 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('223','22','0','0',convert(datetime,'Feb 2 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('224','23','0','0',convert(datetime,'Feb 3 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('225','22','0','0',convert(datetime,'Feb 4 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2221','222','0','0',convert(datetime,'Feb 5 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2222','222','0','0',convert(datetime,'Feb 6 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2223','223','0','0',convert(datetime,'Feb 7 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2224','222','0','0',convert(datetime,'Feb 8 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2225','223','0','0',convert(datetime,'Feb 9 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2226','224','0','0',convert(datetime,'Feb 10 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2227','225','0','0',convert(datetime,'Feb 11 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('3','0','0','0',convert(datetime,'Mar 2 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('4','4','0','0',convert(datetime,'Apr 2 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('5','5','0','0',convert(datetime,'May 2 2008 12:00AM'))INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2','0','0','0',convert(datetime,'Feb 2 2008 12:00AM'))where i need to update the claimid and claimseqFor one workorderid and those parentid will have claimid has a '1' and next workorderid and those parentid will have claimid has a '2'At the same i need to update claimseq where one workorderid strats with claimseq '1' and its latest parentis will have latest claimseqI have a cursor to updated these columnsupdate WorkOrderidtemp set ClaimId = Workorderid, ClaimSeq = 1 where parentid is nulldeclare @count int declare @workorderid as intdeclare @claimid as int-- deallocate cur_WODECLARE cur_WO CURSOR FOR select workorderid, claimid from WorkOrderidTemp where claimId is not null and parentid is nullOpen cur_WOFETCH NEXT from cur_WO into @workorderid, @claimidWHILE @@FETCH_STATUS = 0Beginselect @count = 1while @count > 0 begin update WorkOrderidTemp set claimId = @claimid where parentid in (select workorderid from WorkOrderidTemp where claimid = @claimid) select @count = count(*) from WorkOrderidTemp where parentid in ( select workorderid from WorkOrderidTemp where claimid = @claimid ) and claimId is null end FETCH NEXT from cur_WO into @workorderid, @claimidEndBut i need to update 12 lakhs records in my database ,please is there any othere solution to update these columnsIf i use these cursor it is taking too much of time

AlwaysOn Secondaries

Steve Jones - SSC Editor — Wed, 28 Mar 2012 20:57:23 GMT

Comments posted to this topic are about the item [B]AlwaysOn Secondaries[/B]

DateTime Functions

Steve Jones - SSC Editor — Wed, 04 Apr 2012 20:17:08 GMT

Comments posted to this topic are about the item [B]DateTime Functions[/B]

NOT BETWEEN

Steve Jones - SSC Editor — Thu, 29 Mar 2012 21:27:48 GMT

Comments posted to this topic are about the item [B]NOT BETWEEN[/B]

MDS

Steve Jones - SSC Editor — Tue, 03 Apr 2012 21:35:32 GMT

Comments posted to this topic are about the item [B]MDS[/B]

The Beginning of April

Steve Jones - SSC Editor — Sat, 31 Mar 2012 13:03:55 GMT

Comments posted to this topic are about the item [B]The Beginning of April[/B]

How to add Identity an existing column

Ramana P — Tue, 03 Apr 2012 03:51:29 GMT

Hi All, Can any one help to me ,How to add Identity an existing column, It is empty table.CREATE table Temp(ID int, Name varchar(10))SELECT 1 as ADTID, ID,Name INTO temp1 from tempALTER TABLE temp1 ALTER COLUMN ADTID INT NOT NULL ALTER TABLE temp1 ADD CONSTRAINT PKKK PRIMARY KEY (ADTID)I need to add identity on ADTID column, How i can Add ThanksRamana.P

SOME

Steve Jones - SSC Editor — Tue, 20 Mar 2012 22:31:04 GMT

Comments posted to this topic are about the item [B]SOME[/B]

SQL Server Agent Roles

Steve Jones - SSC Editor — Wed, 21 Mar 2012 21:16:13 GMT

Comments posted to this topic are about the item [B]SQL Server Agent Roles[/B]

Contained Databases

Steve Jones - SSC Editor — Tue, 27 Mar 2012 21:51:18 GMT

Comments posted to this topic are about the item [B]Contained Databases[/B]

SQL Server 2012 Build List

Steve Jones - SSC Editor — Mon, 02 Apr 2012 09:48:32 GMT

Comments posted to this topic are about the item [B]SQL Server 2012 Build List[/B]

Enabling TDE

Steve Jones - SSC Editor — Sun, 29 May 2011 01:58:07 GMT

Comments posted to this topic are about the item [B]Enabling TDE[/B]

Resource Governor Pools

Steve Jones - SSC Editor — Wed, 11 May 2011 23:23:52 GMT

Comments posted to this topic are about the item [B]Resource Governor Pools[/B]

SQL Server Agent Roles

Steve Jones - SSC Editor — Mon, 27 Feb 2012 20:33:31 GMT

Comments posted to this topic are about the item [B]SQL Server Agent Roles[/B]

BETWEEN

Steve Jones - SSC Editor — Sat, 03 Mar 2012 15:07:40 GMT

Comments posted to this topic are about the item [B]BETWEEN[/B]

CREATE ROLE

Steve Jones - SSC Editor — Mon, 12 Mar 2012 21:11:58 GMT

Comments posted to this topic are about the item [B]CREATE ROLE[/B]

Query Plans

Steve Jones - SSC Editor — Thu, 02 Jun 2011 23:37:44 GMT

Comments posted to this topic are about the item [B]Query Plans[/B]

SQLCLR Appdomains

Steve Jones - SSC Editor — Thu, 24 Feb 2011 22:32:10 GMT

Comments posted to this topic are about the item [B]SQLCLR Appdomains[/B]

Service Service Accounts

Steve Jones - SSC Editor — Thu, 23 Feb 2012 20:43:59 GMT

Comments posted to this topic are about the item [B]Service Service Accounts[/B]

SQL CMD

Steve Jones - SSC Editor — Mon, 07 Feb 2011 23:25:22 GMT

Comments posted to this topic are about the item [B]SQL CMD[/B]

Restore

Steve Jones - SSC Editor — Sat, 15 Jan 2011 11:40:08 GMT

Comments posted to this topic are about the item [B]Restore[/B]

Page Compression

Steve Jones - SSC Editor — Sun, 15 May 2011 08:14:11 GMT

Comments posted to this topic are about the item [B]Page Compression[/B]

Bad Passwords

Steve Jones - SSC Editor — Wed, 25 May 2011 22:00:37 GMT

Comments posted to this topic are about the item [B]Bad Passwords[/B]

New Log Files

Steve Jones - SSC Editor — Thu, 30 Dec 2010 21:52:03 GMT

Comments posted to this topic are about the item [B]New Log Files[/B]

SQL Server 2008 Build List

Steve Jones - SSC Editor — Wed, 21 Apr 2010 19:40:47 GMT

Comments posted to this topic are about the item [B]SQL Server 2008 Build List[/B]

Model Database

Steve Jones - SSC Editor — Tue, 21 Feb 2012 20:36:39 GMT

Comments posted to this topic are about the item [B]Model Database[/B]

NUMA

Steve Jones - SSC Editor — Mon, 15 Aug 2011 21:22:03 GMT

Comments posted to this topic are about the item [B]NUMA[/B]

Happy President's Day 2012

Steve Jones - SSC Editor — Sun, 19 Feb 2012 10:18:17 GMT

Comments posted to this topic are about the item [B]Happy President's Day 2012[/B]

Subscriber Backup Devices

Steve Jones - SSC Editor — Wed, 15 Feb 2012 20:26:33 GMT

Comments posted to this topic are about the item [B]Subscriber Backup Devices[/B]

SQL Agent Roles

Steve Jones - SSC Editor — Mon, 06 Feb 2012 21:35:18 GMT

Comments posted to this topic are about the item [B]SQL Agent Roles[/B]