Rewriting of delete SQL script.

Zack
Zack used Ask the Experts™
on
Hi EE,

For the following code

begin tran

create table #UserList (fldUserName nvarchar(max) collate SQL_Latin1_General_CP1_CI_AS)
insert into #UserList (fldUserName) values

DELETE from tblPERMS_Incidents where Username_Incidents collate SQL_Latin1_General_CP1_CI_AS in (select fldUserName from #UserList)

drop table #UserList
rollback tran

Open in new window


Is there a method of rewriting this so I am not limited to deleting 999 rows at one time?

Any assistance is welcome.

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
There is no such obvious limit. The temporary table can have more user names, and the number of deleted rows depends on the amount of rows with matching names.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
As Qlemo mentioned, your current script doesn't have any option available to delete only 999 records as it can delete all eligible records.
Can you kindly clarify the below as I got some doubts from your question..
1. Why fldUserName was declared as nvarchar(max) if it is going to just store your UserName.. Username fields ideally won't exceed 20/25 characters at all.
fldUserName nvarchar(max)
2. Your INSERT into #UserList query is missing the values.. Kindly clarify whether you meant to insert only 999 records to this table.
insert into #UserList (fldUserName) values
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>Is there a method of rewriting this so I am not limited to deleting 999 rows at one time?
There would be if this line is somewhere above your code ... SET ROWCOUNT 999

Otherwise no.  I've had multiple clients where I've seen this in an archive/delete job where the ultimate goal is to delete a large subset of the table (loop, cursor) and minimize locking/blocking (SET ROWCOUNT) at the same time.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>fldUserName nvarchar(max)
As a total aside, what values are typically inserted into this column?  I don't know too many user names that have so many characters that would require an nvarchar(max), n unicode yes, max no, so it appears this column is defined excessively large, which would slow down all queries.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
The code you provided has no LIMIT statement, so unsure why you imagine your code limits to 999 rows.

Suggestion: If your code really does LIMIT to 999 rows, this relates to your input data matching 999 rows, rather than your SQL.

Said another way, only your starting data limits rows acted on, not your SQL code.
ZackGeneral IT Goto Guy

Author

Commented:
Hi David/Jim/Qlemo/Raja,

My apologies for not getting back sooner internet connection issues. To clarify my question yes the limit occurs on the input data values insert inbetween the lines

insert into #UserList (fldUserName) values

('asdfsa')
('gwerq') --The manual insert errors. 
etc

DELETE from tblPERMS_Incidents where Username_Incidents collate SQL_Latin1_General_CP1_CI_AS in (select fldUserName from #UserList)

Open in new window


When I put any more that 999 lines I get the error message,

Thank you
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Got it..
Usage of INSERT INTO clause as a Table valued Constructor has a limit of 1000 rows..
If you really want to use the INSERT INTO #UserList with all values, then you need to use a derived table approach to allow more than 1000 rows
Check example E from MSDN here..(other approaches like BCP or creating multiple insert statements would make your code little bit tedious)
https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15
Check the Limitations and Restrictions for more details about the 1000 rows limit..
When used as the VALUES clause of an INSERT ... VALUES statement, there is a limit of 1000 rows. Error 10738 is returned if the number of rows exceeds the maximum.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
By the way, kindly clarify whether these 1000+ values that are inserted are same across all scenarios or vary..
If it varies, kindly try inserting/storing all those values to a temp table for easier handling instead of writing those 1000+ values in the INSERT INTO clause..
ZackGeneral IT Goto Guy

Author

Commented:
Thank you kindly to all of you that assisted.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial