Using cursor to update if exists and insert if not

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
1
down vote

favorite












I am trying to write a cursor. I have created a table for capturing login name and time from sys.dm_exec_sessions. Now, I need to write a cursor to update the login time to last login time if the login already exists and insert a row in case login does not exists in my table. I have come up with following, but unfortunately I am getting an error with subquery returning more than one value for it. Any ideas???



declare @log as varchar(200)
declare @log_time as datetime
declare LoginsSize cursor for
 SELECT login_name, login_time
 FROM sys.dm_exec_sessions 
open LoginsSize
 fetch next from LoginsSize into @log, @log_time
 while( @@fetch_status = 0)
 begin 
 If (Select Login from [dbo].[LoginsForDBUserList]) = @log 

 Begin
 UPDATE [dbo].[LoginsForDBUserList]
 SET LastLoginTime = @log_time
 WHERE [login]= @log
 END

 Else 

 Begin
 Insert Into [dbo].[LoginsForDBUserList]
 SELECT login_name, login_time
 FROM sys.dm_exec_sessions
 END

 fetch next from LoginsSize into @log, @log_time
 close LoginsSize
 deallocate LoginsSize

end





sql-server cursors







share|improve this question









New contributor




Jay is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.



















  • why not use a MERGE statement?
    – Michael Kutz
    56 mins ago










  • @MichaelKutz - Make sure to Use Caution with SQL Server's MERGE Statement
    – Scott Hodgin
    53 mins ago










  • @MichaelKutz I made it work using Merge statement too, thanks much.
    – Jay
    13 mins ago
















up vote
1
down vote

favorite












I am trying to write a cursor. I have created a table for capturing login name and time from sys.dm_exec_sessions. Now, I need to write a cursor to update the login time to last login time if the login already exists and insert a row in case login does not exists in my table. I have come up with following, but unfortunately I am getting an error with subquery returning more than one value for it. Any ideas???



declare @log as varchar(200)
declare @log_time as datetime
declare LoginsSize cursor for
 SELECT login_name, login_time
 FROM sys.dm_exec_sessions 
open LoginsSize
 fetch next from LoginsSize into @log, @log_time
 while( @@fetch_status = 0)
 begin 
 If (Select Login from [dbo].[LoginsForDBUserList]) = @log 

 Begin
 UPDATE [dbo].[LoginsForDBUserList]
 SET LastLoginTime = @log_time
 WHERE [login]= @log
 END

 Else 

 Begin
 Insert Into [dbo].[LoginsForDBUserList]
 SELECT login_name, login_time
 FROM sys.dm_exec_sessions
 END

 fetch next from LoginsSize into @log, @log_time
 close LoginsSize
 deallocate LoginsSize

end





sql-server cursors







share|improve this question









New contributor




Jay is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.



















  • why not use a MERGE statement?
    – Michael Kutz
    56 mins ago










  • @MichaelKutz - Make sure to Use Caution with SQL Server's MERGE Statement
    – Scott Hodgin
    53 mins ago










  • @MichaelKutz I made it work using Merge statement too, thanks much.
    – Jay
    13 mins ago












up vote
1
down vote

favorite









up vote
1
down vote

favorite











I am trying to write a cursor. I have created a table for capturing login name and time from sys.dm_exec_sessions. Now, I need to write a cursor to update the login time to last login time if the login already exists and insert a row in case login does not exists in my table. I have come up with following, but unfortunately I am getting an error with subquery returning more than one value for it. Any ideas???



declare @log as varchar(200)
declare @log_time as datetime
declare LoginsSize cursor for
 SELECT login_name, login_time
 FROM sys.dm_exec_sessions 
open LoginsSize
 fetch next from LoginsSize into @log, @log_time
 while( @@fetch_status = 0)
 begin 
 If (Select Login from [dbo].[LoginsForDBUserList]) = @log 

 Begin
 UPDATE [dbo].[LoginsForDBUserList]
 SET LastLoginTime = @log_time
 WHERE [login]= @log
 END

 Else 

 Begin
 Insert Into [dbo].[LoginsForDBUserList]
 SELECT login_name, login_time
 FROM sys.dm_exec_sessions
 END

 fetch next from LoginsSize into @log, @log_time
 close LoginsSize
 deallocate LoginsSize

end





sql-server cursors







share|improve this question









New contributor




Jay is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











I am trying to write a cursor. I have created a table for capturing login name and time from sys.dm_exec_sessions. Now, I need to write a cursor to update the login time to last login time if the login already exists and insert a row in case login does not exists in my table. I have come up with following, but unfortunately I am getting an error with subquery returning more than one value for it. Any ideas???



declare @log as varchar(200)
declare @log_time as datetime
declare LoginsSize cursor for
 SELECT login_name, login_time
 FROM sys.dm_exec_sessions 
open LoginsSize
 fetch next from LoginsSize into @log, @log_time
 while( @@fetch_status = 0)
 begin 
 If (Select Login from [dbo].[LoginsForDBUserList]) = @log 

 Begin
 UPDATE [dbo].[LoginsForDBUserList]
 SET LastLoginTime = @log_time
 WHERE [login]= @log
 END

 Else 

 Begin
 Insert Into [dbo].[LoginsForDBUserList]
 SELECT login_name, login_time
 FROM sys.dm_exec_sessions
 END

 fetch next from LoginsSize into @log, @log_time
 close LoginsSize
 deallocate LoginsSize

end





sql-server cursors




sql-server cursors






share|improve this question









New contributor




Jay is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




Jay is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 1 hour ago









Glorfindel

6371515




6371515






New contributor




Jay is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 3 hours ago









Jay

82




82




New contributor




Jay is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Jay is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Jay is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











  • why not use a MERGE statement?
    – Michael Kutz
    56 mins ago










  • @MichaelKutz - Make sure to Use Caution with SQL Server's MERGE Statement
    – Scott Hodgin
    53 mins ago










  • @MichaelKutz I made it work using Merge statement too, thanks much.
    – Jay
    13 mins ago
















  • why not use a MERGE statement?
    – Michael Kutz
    56 mins ago










  • @MichaelKutz - Make sure to Use Caution with SQL Server's MERGE Statement
    – Scott Hodgin
    53 mins ago










  • @MichaelKutz I made it work using Merge statement too, thanks much.
    – Jay
    13 mins ago















why not use a MERGE statement?
– Michael Kutz
56 mins ago




why not use a MERGE statement?
– Michael Kutz
56 mins ago












@MichaelKutz - Make sure to Use Caution with SQL Server's MERGE Statement
– Scott Hodgin
53 mins ago




@MichaelKutz - Make sure to Use Caution with SQL Server's MERGE Statement
– Scott Hodgin
53 mins ago












@MichaelKutz I made it work using Merge statement too, thanks much.
– Jay
13 mins ago




@MichaelKutz I made it work using Merge statement too, thanks much.
– Jay
13 mins ago










1 Answer
1






active

oldest

votes

















up vote
3
down vote



accepted










I think that error is being generated because of this line



If (Select Login from [dbo].[LoginsForDBUserList]) = @log


Multiple rows are being returned from the left side of the equal sign and is attempting to be compared to the single value @log.



I've made some adjustments to your original query and it works for me. Give this a try:



--demo setup
DROP TABLE IF EXISTS LoginsForDBUserList 
GO
CREATE TABLE LoginsForDBUserList (
 LOGIN VARCHAR(200)
 ,LastLoginTime DATETIME
 )

--Adjustments to your original process
DECLARE @log AS VARCHAR(200)
DECLARE @log_time AS DATETIME

DECLARE LoginsSize CURSOR
FOR
SELECT login_name
 ,login_time
FROM sys.dm_exec_sessions

OPEN LoginsSize

FETCH NEXT
FROM LoginsSize
INTO @log
 ,@log_time

WHILE (@@fetch_status = 0)
BEGIN
 IF EXISTS (
 SELECT LOGIN
 FROM LoginsForDBUserList
 WHERE LOGIN = @log
 )
 BEGIN
 UPDATE LoginsForDBUserList
 SET LastLoginTime = @log_time
 WHERE [login] = @log
 END
 ELSE
 BEGIN
 INSERT INTO LoginsForDBUserList
 SELECT login_name
 ,login_time
 FROM sys.dm_exec_sessions
 END

 FETCH NEXT
 FROM LoginsSize
 INTO @log
 ,@log_time
END

CLOSE LoginsSize

DEALLOCATE LoginsSize

SELECT *
FROM LoginsForDBUserList





share|improve this answer




















  • Thanks Scott. works like a charm!!! Thanks...
    – Jay
    13 mins ago










Your Answer







StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);






Jay is a new contributor. Be nice, and check out our Code of Conduct.









 

draft saved


draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218994%2fusing-cursor-to-update-if-exists-and-insert-if-not%23new-answer', 'question_page');

);

Post as a guest

















1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
3
down vote



accepted










I think that error is being generated because of this line



If (Select Login from [dbo].[LoginsForDBUserList]) = @log


Multiple rows are being returned from the left side of the equal sign and is attempting to be compared to the single value @log.



I've made some adjustments to your original query and it works for me. Give this a try:



--demo setup
DROP TABLE IF EXISTS LoginsForDBUserList 
GO
CREATE TABLE LoginsForDBUserList (
 LOGIN VARCHAR(200)
 ,LastLoginTime DATETIME
 )

--Adjustments to your original process
DECLARE @log AS VARCHAR(200)
DECLARE @log_time AS DATETIME

DECLARE LoginsSize CURSOR
FOR
SELECT login_name
 ,login_time
FROM sys.dm_exec_sessions

OPEN LoginsSize

FETCH NEXT
FROM LoginsSize
INTO @log
 ,@log_time

WHILE (@@fetch_status = 0)
BEGIN
 IF EXISTS (
 SELECT LOGIN
 FROM LoginsForDBUserList
 WHERE LOGIN = @log
 )
 BEGIN
 UPDATE LoginsForDBUserList
 SET LastLoginTime = @log_time
 WHERE [login] = @log
 END
 ELSE
 BEGIN
 INSERT INTO LoginsForDBUserList
 SELECT login_name
 ,login_time
 FROM sys.dm_exec_sessions
 END

 FETCH NEXT
 FROM LoginsSize
 INTO @log
 ,@log_time
END

CLOSE LoginsSize

DEALLOCATE LoginsSize

SELECT *
FROM LoginsForDBUserList





share|improve this answer




















  • Thanks Scott. works like a charm!!! Thanks...
    – Jay
    13 mins ago














up vote
3
down vote



accepted










I think that error is being generated because of this line



If (Select Login from [dbo].[LoginsForDBUserList]) = @log


Multiple rows are being returned from the left side of the equal sign and is attempting to be compared to the single value @log.



I've made some adjustments to your original query and it works for me. Give this a try:



--demo setup
DROP TABLE IF EXISTS LoginsForDBUserList 
GO
CREATE TABLE LoginsForDBUserList (
 LOGIN VARCHAR(200)
 ,LastLoginTime DATETIME
 )

--Adjustments to your original process
DECLARE @log AS VARCHAR(200)
DECLARE @log_time AS DATETIME

DECLARE LoginsSize CURSOR
FOR
SELECT login_name
 ,login_time
FROM sys.dm_exec_sessions

OPEN LoginsSize

FETCH NEXT
FROM LoginsSize
INTO @log
 ,@log_time

WHILE (@@fetch_status = 0)
BEGIN
 IF EXISTS (
 SELECT LOGIN
 FROM LoginsForDBUserList
 WHERE LOGIN = @log
 )
 BEGIN
 UPDATE LoginsForDBUserList
 SET LastLoginTime = @log_time
 WHERE [login] = @log
 END
 ELSE
 BEGIN
 INSERT INTO LoginsForDBUserList
 SELECT login_name
 ,login_time
 FROM sys.dm_exec_sessions
 END

 FETCH NEXT
 FROM LoginsSize
 INTO @log
 ,@log_time
END

CLOSE LoginsSize

DEALLOCATE LoginsSize

SELECT *
FROM LoginsForDBUserList





share|improve this answer




















  • Thanks Scott. works like a charm!!! Thanks...
    – Jay
    13 mins ago












up vote
3
down vote



accepted







up vote
3
down vote



accepted






I think that error is being generated because of this line



If (Select Login from [dbo].[LoginsForDBUserList]) = @log


Multiple rows are being returned from the left side of the equal sign and is attempting to be compared to the single value @log.



I've made some adjustments to your original query and it works for me. Give this a try:



--demo setup
DROP TABLE IF EXISTS LoginsForDBUserList 
GO
CREATE TABLE LoginsForDBUserList (
 LOGIN VARCHAR(200)
 ,LastLoginTime DATETIME
 )

--Adjustments to your original process
DECLARE @log AS VARCHAR(200)
DECLARE @log_time AS DATETIME

DECLARE LoginsSize CURSOR
FOR
SELECT login_name
 ,login_time
FROM sys.dm_exec_sessions

OPEN LoginsSize

FETCH NEXT
FROM LoginsSize
INTO @log
 ,@log_time

WHILE (@@fetch_status = 0)
BEGIN
 IF EXISTS (
 SELECT LOGIN
 FROM LoginsForDBUserList
 WHERE LOGIN = @log
 )
 BEGIN
 UPDATE LoginsForDBUserList
 SET LastLoginTime = @log_time
 WHERE [login] = @log
 END
 ELSE
 BEGIN
 INSERT INTO LoginsForDBUserList
 SELECT login_name
 ,login_time
 FROM sys.dm_exec_sessions
 END

 FETCH NEXT
 FROM LoginsSize
 INTO @log
 ,@log_time
END

CLOSE LoginsSize

DEALLOCATE LoginsSize

SELECT *
FROM LoginsForDBUserList





share|improve this answer












I think that error is being generated because of this line



If (Select Login from [dbo].[LoginsForDBUserList]) = @log


Multiple rows are being returned from the left side of the equal sign and is attempting to be compared to the single value @log.



I've made some adjustments to your original query and it works for me. Give this a try:



--demo setup
DROP TABLE IF EXISTS LoginsForDBUserList 
GO
CREATE TABLE LoginsForDBUserList (
 LOGIN VARCHAR(200)
 ,LastLoginTime DATETIME
 )

--Adjustments to your original process
DECLARE @log AS VARCHAR(200)
DECLARE @log_time AS DATETIME

DECLARE LoginsSize CURSOR
FOR
SELECT login_name
 ,login_time
FROM sys.dm_exec_sessions

OPEN LoginsSize

FETCH NEXT
FROM LoginsSize
INTO @log
 ,@log_time

WHILE (@@fetch_status = 0)
BEGIN
 IF EXISTS (
 SELECT LOGIN
 FROM LoginsForDBUserList
 WHERE LOGIN = @log
 )
 BEGIN
 UPDATE LoginsForDBUserList
 SET LastLoginTime = @log_time
 WHERE [login] = @log
 END
 ELSE
 BEGIN
 INSERT INTO LoginsForDBUserList
 SELECT login_name
 ,login_time
 FROM sys.dm_exec_sessions
 END

 FETCH NEXT
 FROM LoginsSize
 INTO @log
 ,@log_time
END

CLOSE LoginsSize

DEALLOCATE LoginsSize

SELECT *
FROM LoginsForDBUserList






share|improve this answer












share|improve this answer



share|improve this answer










answered 2 hours ago









Scott Hodgin

15.7k11535




15.7k11535











  • Thanks Scott. works like a charm!!! Thanks...
    – Jay
    13 mins ago
















  • Thanks Scott. works like a charm!!! Thanks...
    – Jay
    13 mins ago















Thanks Scott. works like a charm!!! Thanks...
– Jay
13 mins ago




Thanks Scott. works like a charm!!! Thanks...
– Jay
13 mins ago










Jay is a new contributor. Be nice, and check out our Code of Conduct.









 

draft saved


draft discarded


















Jay is a new contributor. Be nice, and check out our Code of Conduct.












Jay is a new contributor. Be nice, and check out our Code of Conduct.











Jay is a new contributor. Be nice, and check out our Code of Conduct.













 


draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218994%2fusing-cursor-to-update-if-exists-and-insert-if-not%23new-answer', 'question_page');

);

Post as a guest
































































Comments

Post a Comment

Popular posts from this blog

What does second last employer means? [closed]

Image
Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote -3 down vote favorite Say, for example I have worked in the following companies, Company A => 2010 - 2011 Company B => 2011 - 2012 Company C => 2012 - Till Date Company D => Have offer Now assume Company D is asking to submit documents from my second last employer. So from the list of companies given, which company would be qualified as second last employer? software-industry employer share | improve this question asked Aug 10 '14 at 5:16 Rajaprabhu Aravindasamy 559 1 6 13 closed as off-topic by Jim G., jmort253 ♦ Aug 11 '14 at 1:58 This question appears to be off-topic. The users who voted to close gave this specific reason: "Questions seeking advice on company-specific regulations, agreements, or policies should be directed to your manager or HR department. Questions that address...
Read more

Installing NextGIS Connect into QGIS 3?

Image
Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote 1 down vote favorite Just acquired free version of NEXTGIS and trying to install the plugin for QGIS. It tells me it was not designed for version 3 of QGIS. How do I set up the QGIS-NEXTGIS interface then? qgis qgis-plugins nextgis share | improve this question edited 3 hours ago PolyGeo ♦ 51.9k 17 77 233 asked 4 hours ago IanGS 16 4 New contributor IanGS is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. add a comment  |  up vote 1 down vote favorite Just acquired free version of NEXTGIS and trying to install the plugin for QGIS. It tells me it was not designed for version 3 of QGIS. How do I set up the QGIS-NEXTGIS interface then? qgis qgis-plugin...
Read more

One-line joke

A one-liner is a joke that is delivered in a single line. A good one-liner is said to be pithy - concise and meaningful. [1] Comedians and actors use this comedic method as part of their act, e.g. Jimmy Carr, Tommy Cooper, Rodney Dangerfield, Norm Macdonald, Ken Dodd, Stewart Francis, Zach Galifianakis, Mitch Hedberg, Anthony Jeselnik, Milton Jones, Shappi Khorsandi, Jay London, Mark Linn-Baker, Demetri Martin, Groucho Marx, Dan Mintz, Emo Philips, Tim Vine, Steven Wright and Henny Youngman and Arnold Schwarzenegger. [ citation needed ] Many fictional characters are also known to deliver one-liners, including James Bond, who usually includes pithy and laconic quips after disposing of a villain. [ citation needed ] Examples "A baby seal walks into a club." "A dyslexic man walks into a bra." (Anonymous) "There are three types of people, those who can count and those who can't." "Venison's dear, isn't it?" (Jimmy Carr) "An escala...
Read more