Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Loop through records and do a INSERT

Posted on 2013-11-11
11
Medium Priority
?
418 Views
Last Modified: 2013-11-11
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
Comment
Question by:mousemat24
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 39638436
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
 

Author Comment

by:mousemat24
ID: 39638451
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
 
LVL 11

Expert Comment

by:Louis01
ID: 39638460
Can you post the SP?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mousemat24
ID: 39638475
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
 
LVL 11

Expert Comment

by:Louis01
ID: 39638488
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
 

Author Comment

by:mousemat24
ID: 39638495
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
 
LVL 11

Expert Comment

by:Louis01
ID: 39638513
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
 

Author Comment

by:mousemat24
ID: 39638530
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
 
LVL 11

Expert Comment

by:Louis01
ID: 39638531
I suspect that maybe the problem is that the SP is called with a @sendToAllUsers parameter that is not equal to 1
0
 
LVL 11

Accepted Solution

by:
Louis01 earned 2000 total points
ID: 39638560
p.s. Check out this link for some more info
0
 

Author Comment

by:mousemat24
ID: 39638771
thanks for that, really useful that was!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question