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
mousemat24Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Louis01Connect With a Mentor Commented:
p.s. Check out this link for some more info
0
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Louis01Commented:
Can you post the SP?
0
 
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
 
mousemat24Author Commented:
thanks for that, really useful that was!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.