|
[VIEWED 5350
TIMES]
|
SAVE! for ease of future access.
|
|
|
|
virusno1
Please log in to subscribe to virusno1's postings.
Posted on 11-20-08 1:21
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0 
? 
|
| |
Anybody knows in what case, we have to use Cursor? I had done Some data cleansing operation using Cursor such as Removing Duplicate Reports, Increasing the salary of employee depending upon their title. But Beside that can anybody suggest me any better and practical example which can be solved only by cursors.
|
| |
|
|
|
|
lanthus
Please log in to subscribe to lanthus's postings.
Posted on 11-20-08 2:12
PM
Reply
[Subscribe]
|
| Login in to Rate this Post:
0
?
|
| |
basically, a cursor is nothing but just a variable. but the difference is a variable can hold a value at a time but a cursor can hold multiple values at a time. So the main purpose of using cursor is to hold the entire row values so that what ever manipulation that we wanna do in the table can be done with a single command.
hope that might help...........
|
| |
|
|
virusno1
Please log in to subscribe to virusno1's postings.
Posted on 11-20-08 2:21
PM
Reply
[Subscribe]
|
| Login in to Rate this Post:
0
?
|
| |
Thanks Lanthus, but If we can do same operation using select or other DML statement, then use of cursor should be avoided. For example I can store the variable using following statement also select @prodID=Prod from Product I am trying to find specific situation where we 'have to' use the cursor
|
| |
|
|
techGuy
Please log in to subscribe to techGuy's postings.
Posted on 11-20-08 2:45
PM
Reply
[Subscribe]
|
| Login in to Rate this Post:
0
?
|
| |
Dont know abt T-sql, but can you store multiple values in @prod ? if yes, how do u fetch it? Generally, cursor is used to store multiple values and later fetch it sequentially. Suppose i want to truncate some talbes then, cursor tb is select table_name from user_talbes where table_name like "tb%" this will store all the table names starting with tb in varable tb. Then i fetch it to truncate one by one. open tb loop fetch tb into tbname truncate table '|| tbname ||' end loop well the syntax may not be correct. 
|
| |
|
|
virusno1
Please log in to subscribe to virusno1's postings.
Posted on 11-20-08 3:43
PM
Reply
[Subscribe]
|
| Login in to Rate this Post:
0
?
|
| |
hmm..thats the reasonable answer thanks techy...
|
| |
|
|
rawbee
Please log in to subscribe to rawbee's postings.
Posted on 11-20-08 4:03
PM
Reply
[Subscribe]
|
| Login in to Rate this Post:
0
?
|
| |
absoutly Techi....
don't look at how to use cursor. first of all think how you can avoid curssor .like using Temp table,Temp table variable or doing sub queries.. as you know when you use curssor, preformance become slower.... ..
ani BTW here is code for Truncate all Table in SQL 2005. might help you guys later on..
How to used:
1.- Create table to hold constraints values
2.-Populate table
3.- Create cursor to remove constraints
4.- truncate all data
Use DatabaseName --Temporary table to hold constraints info most of the time at a different location
-- or database
--This could be a temp table however set as static
IF EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')
truncate table T_FK_Xref
go
--Create Table to store constraint information
IF NOT EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')
Create table DatabaseName.dbo.T_FK_Xref (
ID int identity (1,1),
ConstraintName varchar (255),
MasterTable varchar (255),
MasterColumn varchar (255),
ChildTable varchar (255),
ChildColumn varchar (255),
FKOrder int
)
go
--Store Constraints
insert into DatabaseName.dbo.T_FK_Xref(ConstraintName,MasterTable,MasterColumn,ChildTable,ChildColumn,FKOrder)
SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable
,sc2.name MasterColumn
,object_name(fkeyid) ChildTable
,sc1.name ChildColumn
,cast (sf.keyno as int) FKOrder
FROM sysforeignkeys sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno
go
use databaseName --Database to removed constraints
go
---Ready to remove constraints
declare @ConstraintName varchar (max) -- Name of the Constraint
declare @ChildTable varchar (max) -- Name of Child Table
declare @MasterTable varchar (max)--Name of Parent Table
declare @ChildColumn varchar (max)--Column of Child Table FK
declare @MasterColumn varchar (max)-- Parent Column PK
declare @FKOrder smallint -- Fk order
declare @sqlcmd varchar (max) --Dynamic Sql String
-- Create cursor to get constraint Information
declare drop_constraints cursor
fast_forward
for
SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable
,sc2.name MasterColumn
,object_name(fkeyid) ChildTable
,sc1.name ChildColumn
,cast (sf.keyno as int) FKOrder
FROM sysforeignkeys sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno
open drop_constraints
fetch next from drop_constraints
into
@ConstraintName
, @MasterTable
, @MasterColumn
, @ChildTable
, @ChildColumn
, @FKOrder
while @@Fetch_status = 0
begin
-- Create Dynamic Sql to drop constraint
--+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'
select @sqlcmd = 'alter table '+@ChildTable+' drop constraint '+@ConstraintName
If EXISTs (select object_name(constid) from sysforeignkeys where object_name(constid) = @ConstraintName)
exec (@sqlcmd)
fetch next from drop_constraints
into
@ConstraintName
, @MasterTable
, @MasterColumn
, @ChildTable
, @ChildColumn
, @FKOrder
end
close drop_constraints
deallocate drop_constraints
go
--Removed CHECK Constraint-------------------------
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' --NOCHECK Constraints
print 'All Constraints Disable'
go
--truncate All tables if trying to empty the database
--- Ensure the T_X_ref database is located on a different database
------------- Truncate All Tables from Model ----------------
-----To limit tables a table with sub model tables must be created and used joins-----
EXEC sp_MSForEachTable 'truncate TABLE ? '
print 'All tables truncated'
go
------------------------------------------------------------------------------------------------------
And we can find/delete duplicate record without cursor
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicatevalueColumn1, DuplicateValueColumn2,
DuplicateValueColumn2)
ENJOY IN SQL WORLD/.........
|
| |
|
|
wTwP
Please log in to subscribe to wTwP's postings.
Posted on 11-20-08 4:30
PM
Reply
[Subscribe]
|
| Login in to Rate this Post:
0
?
|
| |
Hi, I am looking for consultancy to learn sql server...and of coarse for marketing and placement. I am here in STL, MO...it will be great if i can find one here....but i am flexible to move thanks.
|
| |
|
|
virusno1
Please log in to subscribe to virusno1's postings.
Posted on 11-20-08 5:39
PM
Reply
[Subscribe]
|
| Login in to Rate this Post:
0
?
|
| |
rawbee, yeah i am absolutely agree with you that by using the cursor we have to sacrifice performance. But in some cases, instead of going into Complex T-sql query and sub-query we can use cursor. Cursor is generally consider to be 'sauteni aama ko chora' among DBA/Developer' and everybody tries to avoid it as far as possible although it might save your life some time  . But Instead of going into your complex code which you posted above. I think we can use cursor and write much simpler code by using INFORMATION_SCHEMA system view. isn't it?
|
| |
