Solved

Loop through records and do a INSERT

Posted on 2013-11-11
11
416 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 500 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

691 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