How to improve this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to improve this query

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2007-12-13 : 04:08:50

I am having following query which is using cursor to insert data into anothert table. What I am looking for is alternate way for this query.

Declare CModule Cursor for Select MID from PAModule Where MDLSystem=1
Open CModule
Fetch Next from CModule into @MID
WHILE @@FETCH_STATUS = 0
BEGIN
if NOT Exists(Select * from PARoleConfig Where RId = @ROL_ID and RMId = @MID)
Begin
Insert into PARoleConfig
(RId, RMId, RAssigned, RCreatedBy, R_CreatedDate)
Values(@ROL_ID, @MID, 1, 1, getdate())
End
FETCH NEXT FROM CModule into @MID;
END;
CLOSE CModule ;
DEALLOCATE CModule ;

I am looking for alternate way to insert my data.

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-13 : 04:31:23

[code]INSERT PARoleConfig
(
RId,
RMId,
RAssigned,
RCreatedBy,
R_CreatedDate
)
SELECT @ROL_ID,
p.MID,
1,
1,
CURRENT_TIMESTAMP
FROM PAModule AS p
LEFT JOIN PARoleConfig AS c ON c.RMId = p.MID
AND c.RId = @ROL_ID
WHERE p.MDLSystem = 1
AND c.RMID IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2007-12-13 : 05:46:39

Thanks Peso for your reply.

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources