Rewriting of delete SQL script.
Hi EE,
For the following code
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.
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 tranIs there a method of rewriting this so I am not limited to deleting 999 rows at one time?
Any assistance is welcome.
Thank you.
Qlemo
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.
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.
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
>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.
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.
>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.
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.
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.
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.
ASKER
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
When I put any more that 999 lines I get the error message,
Thank you
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)When I put any more that 999 lines I get the error message,
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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..
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..
ASKER
Thank you kindly to all of you that assisted.