Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

Loop through records and do a INSERT

Hi

Hope you can help me, I have this code, which I can't change, as you can see it only works for 1 record

	insert into core.Message (LicenseeId,MessageType,MessageTimestamp,IsRead,MessageText,UserId,FromUserId,IsRemoved)
	output inserted.MessageId
	SELECT
		@LicenseeId,@msgType,getdate(),@IsRead,@msgText,UserId,@fromUserId,@Isremoved
	FROM
		core.lsr_User 
	INNER JOIN
		core.lsr_UserAccounts 
		ON core.lsr_User.UserAccountId = core.lsr_UserAccounts.UserAccountId  
	WHERE
	(
		core.lsr_UserAccounts.Status = 'A'
	)

Open in new window


Can someone help me so that it the "new piece", will be pretty much the same as above, BUT it should work with lots of records? So, I want to do this:

	SELECT
		@LicenseeId,@msgType,getdate(),@IsRead,@msgText,UserId,@fromUserId,@Isremoved
	FROM
		core.lsr_User 
	INNER JOIN
		core.lsr_UserAccounts 
		ON core.lsr_User.UserAccountId = core.lsr_UserAccounts.UserAccountId  
	WHERE
	(
		core.lsr_UserAccounts.Status = 'A'
	)

Open in new window


loop through all the records above and do a INSERT i.e.

	insert into core.Message (LicenseeId,MessageType,MessageTimestamp,IsRead,MessageText,UserId,FromUserId,IsRemoved)
	output inserted.MessageId

Open in new window


Hope this makes sense?

thanks
0
mousemat24
Asked:
mousemat24
  • 6
  • 5
1 Solution
 
Louis01Commented:
I have this code, which I can't change
Kindly explain?

If you use just "output inserted.MessageId" the list of MessageId's created would be returned to the calling application.

You can use it as follows though: "output inserted.MessageId INTO @SomePreviouslyDeclaredTableVariable(MessageId)" but saying you can't change the code means you'll have to try something else.

Maybe you can explain a bit more of the problem? What calls this code? Is it contained in a SP?
0
 
mousemat24Author Commented:
Sorry, when I say I cant make changes, I mean, its an existing SP, so I can't change existing functionality, however, I have to add new functionality where it gets a list of records i.e.

SELECT
		@LicenseeId,@msgType,getdate(),@IsRead,@msgText,UserId,@fromUserId,@Isremoved
	FROM
		core.lsr_User 
	INNER JOIN
		core.lsr_UserAccounts 
		ON core.lsr_User.UserAccountId = core.lsr_UserAccounts.UserAccountId  
	WHERE
	(
		core.lsr_UserAccounts.Status = 'A'
	)

Open in new window


Once I've got all the records I need, I need to run the following:

insert into core.Message (LicenseeId,MessageType,MessageTimestamp,IsRead,MessageText,UserId,FromUserId,IsRemoved)
	output inserted.MessageId

Open in new window


Hope I've made it clearer?

thank you
0
 
Louis01Commented:
Can you post the SP?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mousemat24Author Commented:
Here is the SP, I've added a comment ("-- I added this, this is where I have to loop through records")

that is where I want to loop through the records, I know its not doing what its supposed to be doing.

thanks
sp.sql
0
 
Louis01Commented:
I'm confused...

This SP already Inserts multiple records into the core.Message table. No need to loop through them to insert them again? Why do you want to do it again?

I'm guessing, but I think this SP already does what it's supposed to? Could the problem be elsewhere?
0
 
mousemat24Author Commented:
Where it says:

if @sendToAllUsers = 1
		Begin
			insert into core.Message (LicenseeId,MessageType,MessageTimestamp,IsRead,MessageText,UserId,FromUserId,IsRemoved)
			output inserted.MessageId
			SELECT
			   @LicenseeId,@msgType,getdate(),@IsRead,@msgText,UserId,@fromUserId,@Isremoved
			FROM
			   core.lsr_User 
			INNER JOIN
			   core.lsr_UserAccounts 
				  ON core.lsr_User.UserAccountId = core.lsr_UserAccounts.UserAccountId  
			WHERE
			   (
				  core.lsr_UserAccounts.Status = ''A''
			   )	
		End

Open in new window


I added that, but its all wrong, as it needs to go through all records where core.lsr_UserAccounts.Status = ''A'':

SELECT			  @LicenseeId,@msgType,getdate(),@IsRead,@msgText,UserId,@fromUserId,@Isremoved
			FROM
			   core.lsr_User 
			INNER JOIN
			   core.lsr_UserAccounts 
				  ON core.lsr_User.UserAccountId = core.lsr_UserAccounts.UserAccountId  
			WHERE
			   (
				  core.lsr_UserAccounts.Status = ''A''
			   )	

Open in new window


and do a INSERT from the record set. Does this make sense? Or am I confusing you more?

thanks
0
 
Louis01Commented:
But the code you  have in the "if @sendToAllUsers = 1" part would insert multiple records if the select part returns multiple records... and then the SP returns all MessageId's inserted...

What does this SP do currently?
What do you want it to do?
0
 
mousemat24Author Commented:
oh I didn't know that, I didn't know SQL was clever to know to INSERT rows based on a selection, I thought I had to write a do while loop based on the SELECTion.

thanks for helping me out
0
 
Louis01Commented:
I suspect that maybe the problem is that the SP is called with a @sendToAllUsers parameter that is not equal to 1
0
 
Louis01Commented:
p.s. Check out this link for some more info
0
 
mousemat24Author Commented:
thanks for that, really useful that was!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now