help with query (limiting insert)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with query (limiting insert)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-21 : 03:47:51

Hi,

I have the following insert stored procedure which works perfectly. What I want to add in terms of functionality is the ability to restrict the total number of photos each user has.

Normally most of my inserts are just single inserts and I do a check on the record count before however since this is a list, it seems quite a bit more complicated.

Any one have any suggestions on the best way to approach this ? I want each "userID" to only be allowed 100 rows in this table.

Any suggestions much appreciated!

Thanks again,
mike123

CREATE PROCEDURE [dbo].[insert_favourites_List]
(
@userID [int],
@MessageIDTXT VarChar(255)
)
AS SET NOCOUNT ON

Declare @SQL VarChar(900)
Select @SQL = 'INSERT INTO tblfavouritephotos (userID, counterID, dateAdded) ((select ' + CAST(@userID As varchar) +', counterID, getdate() FROM tblExtraPhotos WHERE [counterID] IN (' + @MessageIDTXT + ') AND counterID NOT IN (SELECT counterID FROM tblfavouritephotos WHERE userID = ' + CAST(@userID As varchar) +')))'
Exec ( @SQL)

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-04-21 : 09:33:48

You could get the count of how many rows the userID currently has, subtract that from 100, and use select top. Something like:


DECLARE @count int
SELECT  @count = count(*)
FROM    tblfavouritephotos
WHERE   userID = @userID

INSERT  INTO tblfavouritephotos (userID, counterID, dateAdded)
SELECT  TOP(100 - @count) bla bla bla

/jeff

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-21 : 12:43:30

or you can do a SET ROWCOUNT if the order doesnt matter. It will be faster then SELECT TOP X...

SET ROWCOUNT 100
INSERT INTO ...
SELECT bla bla..

SET ROWCOUNT 0

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-22 : 20:16:18

Hey Guys,

I think the complication lies in the fact I am passing a string for the insert, so its difficult to determine how many rows I am actually inserting. Also, if I am going to insert just part of the string that complicates things as well.

For instance if I have 98 rows, and I pass another 10 to get inserted. Is there a way I can easily get just 2 to be inserted?

Either that, or I am missing something you guys are saying. I do checks on single row inserts all the time and don't have a problem.

Thanks again!
mike123

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-23 : 01:26:31

Since you want to referance the recordset during runtime to get the current count, In my opinion, your best option would be to use a reccursive query CTE. Otherwise you can probably use what JSHelper sugested as well.

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-04-23 : 23:31:29

mike123,

You don't need to know how many rows are passed (to be inserted), only how many rows currently exist in the table you're inserting into.

The fact that you're passing a string for the insert is a seperate issue. Since your question was how to limit the number of inserts, I assumed that you had worked out how to seperate your string of comma-delimited (again assuming because of "...WHERE [counterID] IN (' + @MessageIDTXT + ')..." it would have to be) values and just gave an idea on how to limit the number of rows inserted.

You have a couple of options: pass the values as XML (read up on openXML) or parse the string into the seperate values into a temp table. Either way, your final insert-select could still be limited by TOP.

If you go recursive, you'll have to select current count after each insert to know when to stop.

/jeff

Kristen
Test

22859 Posts

Posted - 2007-04-24 : 04:26:26

"For instance if I have 98 rows, and I pass another 10 to get inserted. Is there a way I can easily get just 2 to be inserted?"

That's what jshelper's code (Posted - 04/21/2007 : 09:33:48 ) does, isn't it? - If not please post how you need your Insert to differ.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources