Solved

Loop through records and do a INSERT

Posted on 2013-11-11
11
412 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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

775 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