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

Long meetings (6-7 hours a day): Being “babysat” by supervisor

Image
Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote 31 down vote favorite 4 I hold a PhD in mechanical engineering with a 7 year background in self-guided, self-paced research including 2 years as a graduate level course instructor and a subject matter expert for various engineering projects in a university setting in the United States. For the last 10-11 months, I am working in a research industry in France where my task is to debug C++ spaghetti code written over a period of several years by my current supervisor. It is a team of 2: just I and my supervisor. Diurnally, I am posed with a "bug of the day" to get out of this C++ code. My supervisor generally gives me about 15-30 minutes to solve it and then accosts me about whether or not the task is completed and then rinses and repeats this until the end of the day. Off-late, my supervisor has been holding 6-7 hour long "meetin
Read more

Is the Concept of Multiple Fantasy Races Scientifically Flawed? [closed]

Image
Clash Royale CLAN TAG #URR8PPP up vote 2 down vote favorite Many fantasy worlds have multiple humanoid species (elves, orcs, humans, etc) living in the same world, in addition to that, they often have the ability to interbreed. And I can't help but question their believability... fantasy-races reproduction interspecies-relations share | improve this question edited Aug 9 at 14:46 Michael Kjörling ♦ 21.1k 10 85 161 asked Aug 9 at 13:13 Chlodio 118 6 closed as unclear what you're asking by MichaelK, Gryphon, John, Vincent, Frostfyre Aug 9 at 15:25 Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question. 3 Stack Exchange exist to
Read more

Confectionery

Image
Prepared foods made with a lot of sugar or other cabohydrates "Sweetmeat" redirects here. For the racehorse, see Sweetmeat (horse). Not to be confused with Sweetbread. This Kransekake is a traditional Scandinavian baker's confection. Confectionery is the art of making confections , which are food items that are rich in sugar and carbohydrates. Exact definitions are difficult. [1] In general, though, confectionery is divided into two broad and somewhat overlapping categories, bakers' confections and sugar confections. [2] Bakers' confectionery, also called flour confections , includes principally sweet pastries, cakes, and similar baked goods. Sugar confectionery includes candies ( sweets in British English), candied nuts, chocolates, chewing gum, bubble gum, pastillage, and other confections that are made primarily of sugar. In some cases, chocolate confections (confections made of chocolate) are treated as a separate category, as are sugar-free versions of
Read more