March 31, 2013 at 7:53 am
Hi,
I am a SQL newbie and need to update code in a Stored Procedure that has a cursor. Is it possible to have multiple queries in a cursor?
Currently, we are checking for duplicates in a database table when importing an Excel upload file. Now, we also want to check for potential duplicates in an Excel upload file and if the Excel file record falls into an already exisiting date range (passing start and end dates). I do not need help with the queries, but just wanted to give a little background information. I need help to understand how to get three queries to work in a cursor and make a decision if duplicates are found.
Can someone please provide sample code on how to place three queries in a cursor and/or provide a good reference?
Basically, on a high level this is what I want to do:
Run Qry 1 - check for duplicates in database table
Run Qry 2 - check for duplicates in file
Run Qry 3 - check to see if record already exists in specific date range
if no duplicates, then "0" count of dups
if dup is found, than Count and error type description
Thanks in advance, any help is much appreciated!
March 31, 2013 at 8:26 am
Without sample table definition(s), and sample data it is difficult to make a recommendation.
That said, I would recommend that you look at the MERGE statement.
For example:
[ WITH <common_table_expression> [,...n] ]
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ] [ ...n ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ] [ ...n ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
;
March 31, 2013 at 10:33 am
I like what BitBucket suggested. But stay far away from cursors. Remove that junk code and use efficient code.
Andrew SQLDBA
April 1, 2013 at 7:47 am
This code creates a table with duplicates. I copy the data into a temp table adding a row column. You could loop through the temp table by
CREATE TABLE t1
(
Id INT,
[DESC] varchar(50)
)
INSERT INTO t1 (id, [desc]) VALUES ( 1,'test 1')
INSERT INTO t1 (id, [desc]) VALUES ( 1,'test 1')
INSERT INTO t1 (id, [desc]) VALUES ( 2,'test 2')
INSERT INTO t1 (id, [desc]) VALUES ( 3,'test 3')
INSERT INTO t1 (id, [desc]) VALUES ( 3,'test 3')
SELECT ROW_NUMBER() OVER(ORDER BY id) AS ROW,* INTO #t1 FROM t1 ORDER BY id
SELECT * FROM #t1
This will show you the id's that are duplicates.
SELECT id FROM #t1 GROUP BY id HAVING COUNT(id) > 1
2nd option would be to use the Row and loop through the rows and write the row number containing the duplicate to a table. Delete the rows from the temp table
#t1 using the table created during the looping process. Then write the temp table back to your final table.
DROP TABLE #t1
DROP TABLE t1
April 1, 2013 at 8:43 am
SQL Server does not Loop thru rows. SQL Server is Set Based.
Please do not indicate that a database loops thru rows.
Andrew SQLDBA
April 1, 2013 at 10:24 am
AndrewSQLDBA (4/1/2013)
SQL Server does not Loop thru rows. SQL Server is Set Based.Please do not indicate that a database loops thru rows.
Andrew SQLDBA
🙂
Give me a set based statement that goes through all 300 of my tables and renames the table to [tablename]+[currentdate] (and I'll remind you that sp_ForEachTable is a systemic cursor) and I'll happily agree with both statements made about looping and cursors.
Sometimes I think we tend tell others NEVER to use a cursor, gods forbid! Find any other way to do it! Evil things! Bad..bad...bad..
When what we really mean is that cursors are sometimes overused or misused when a set-based statement would be more efficient. ie, try to find a set-based solution before falling back to a cursor or looping mechanism. Failing THAT, however, if a cursor is necessary, use a cursor. Just realize that there are performance issues involved in RBAR and be prepared to deal with them.
April 1, 2013 at 10:52 am
Erin Ramsay (4/1/2013)
AndrewSQLDBA (4/1/2013)
SQL Server does not Loop thru rows. SQL Server is Set Based.Please do not indicate that a database loops thru rows.
Andrew SQLDBA
🙂
Give me a set based statement that goes through all 300 of my tables and renames the table to [tablename]+[currentdate] (and I'll remind you that sp_ForEachTable is a systemic cursor) and I'll happily agree with both statements made about looping and cursors.
Sometimes I think we tend tell others NEVER to use a cursor, gods forbid! Find any other way to do it! Evil things! Bad..bad...bad..
When what we really mean is that cursors are sometimes overused or misused when a set-based statement would be more efficient. ie, try to find a set-based solution before falling back to a cursor or looping mechanism. Failing THAT, however, if a cursor is necessary, use a cursor. Just realize that there are performance issues involved in RBAR and be prepared to deal with them.
Okay, how about this:
declare @SQLCmd nvarchar(max);
select
@SQLCmd = stuff((select char(13) + char(10) +
'exec sp_rename @objname = N''' + schema_name(tab.schema_id) + '.' + tab.name + ''', @newname = N''' + schema_name(tab.schema_id) + '.' + tab.name + '_' + convert(varchar(10), getdate(), 112) + ''', @objtype = N''TABLE'''
from sys.tables tab
for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'');
PRINT @SQLCmd;
--exec sp_executesql @SQLCmd;
April 1, 2013 at 10:54 am
Back to helping the original user and to discuss your critque of the vernacular. . .
declare @row int
declare @count int
declare @previd int
declare @id int
Create table #killRow
(
row int
)
select Row_Number() OVER(ORDER BY mytableID) as row, * INTO #mytemptable FROM MyTable...
select count(*) from #mytemptable
set @row = 1
set @previd = 0
while @row <= @count
begin
... do stuff here with items from #mytemptable where row = @row
select @id from #mytemptable where row = @row
if (@id == @previd)
begin
insert into #killRow (row) values(@row)
end
select @previd = id from #mytemptable where row = @row
set @row = @row + 1
end
delete from #mytemptable where row in ( select row from #killRow)
select * into mycleanedtable from #mytemptable
OR
select * into mycleanedtable from #mytemptable where row not in ( select row from #killRow )
My vernacular for looping is adequate to express the series of iterating over these rows. You can call it set based all you like, but the vernacular used to describe
the process is more than adequate.
April 1, 2013 at 10:55 am
Erin Ramsay (4/1/2013)
AndrewSQLDBA (4/1/2013)
SQL Server does not Loop thru rows. SQL Server is Set Based.Please do not indicate that a database loops thru rows.
Andrew SQLDBA
🙂
Give me a set based statement that goes through all 300 of my tables and renames the table to [tablename]+[currentdate] (and I'll remind you that sp_ForEachTable is a systemic cursor) and I'll happily agree with both statements made about looping and cursors.
You said that like it would be challenging. Quite simple in fact.
declare @MyQuery nvarchar(max) =
(
select 'exec sp_rename [' + name + '], [' + name + '_' + convert(varchar(20), getdate(), 112) + '];'
from sys.tables
for xml path('')
)
exec sp_executesql @MyQuery
Sometimes I think we tend tell others NEVER to use a cursor, gods forbid! Find any other way to do it! Evil things! Bad..bad...bad..
When what we really mean is that cursors are sometimes overused or misused when a set-based statement would be more efficient. ie, try to find a set-based solution before falling back to a cursor or looping mechanism. Failing THAT, however, if a cursor is necessary, use a cursor. Just realize that there are performance issues involved in RBAR and be prepared to deal with them.
Yes they are horribly overused. Cursor are very important and serve as an invaluable tool when doing maintenance. They are not always evil but often they only require a change in the way we think about data. 😉
--EDIT--
It seems that while I was writing Lynn posted another version that is similar to mine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 1, 2013 at 11:01 am
Sean Lange (4/1/2013)
Erin Ramsay (4/1/2013)
AndrewSQLDBA (4/1/2013)
SQL Server does not Loop thru rows. SQL Server is Set Based.Please do not indicate that a database loops thru rows.
Andrew SQLDBA
🙂
Give me a set based statement that goes through all 300 of my tables and renames the table to [tablename]+[currentdate] (and I'll remind you that sp_ForEachTable is a systemic cursor) and I'll happily agree with both statements made about looping and cursors.
You said that like it would be challenging. Quite simple in fact.
declare @MyQuery nvarchar(max) =
(
select 'exec sp_rename [' + name + '], [' + name + '_' + convert(varchar(20), getdate(), 112) + '];'
from sys.tables
for xml path('')
)
exec sp_executesql @MyQuery
Sometimes I think we tend tell others NEVER to use a cursor, gods forbid! Find any other way to do it! Evil things! Bad..bad...bad..
When what we really mean is that cursors are sometimes overused or misused when a set-based statement would be more efficient. ie, try to find a set-based solution before falling back to a cursor or looping mechanism. Failing THAT, however, if a cursor is necessary, use a cursor. Just realize that there are performance issues involved in RBAR and be prepared to deal with them.
Yes they are horribly overused. Cursor are very important and serve as an invaluable tool when doing maintenance. They are not always evil but often they only require a change in the way we think about data. 😉
--EDIT--
It seems that while I was writing Lynn posted another version that is similar to mine.
Yes, and mine takes into account the possibility of multiple schemas. I noticed that after my first pass created a sp_rename for two tables with different schemas but the same name. Realized I needed to take that into account.
April 1, 2013 at 11:01 am
That's a nice bit of code there, Sean.
While I've used dynamic sql to either a) build the list of commands I want to run, or b) run dynamic commands, I don't usually combine a) and b) to run a dynamic LIST of commands.. not a bad idea, maybe a little harder to debug than doing it with single command steps but definitely something to keep in mind.
April 1, 2013 at 11:10 am
I concede that this task can be done with a set-based command that generates a list of commands.. lol.. now let's consider a moment.
Now consider your task may take a LARGE block of dynamic SQL (say about 6000 characters) that you're going to execute against all 300 tables.. do you still want to generate a character variable to hold this massive statement so that if (when) it breaks you'll get "Error on line 1 of...." or hey! put it in a procedure and THEN put it in your statement to save space.. but then you still get "Error on line 1 of..." when it breaks.
Generally, I use a cursor because it's a non-production issue, it's developmental and I'm using it only to save myself typing time.. it's likely a one time event.. but I'd rather have ONE particular command fail that I can debug than have the whole block fail and try to figure out where it's falling down.
The point I was trying to get across was that there ARE times an places that a cursor can be useful.. just need to be careful about when and where, you know?
April 1, 2013 at 11:15 am
Erin Ramsay (4/1/2013)
I concede that this task can be done with a set-based command that generates a list of commands.. lol.. now let's consider a moment.Now consider your task may take a LARGE block of dynamic SQL (say about 6000 characters) that you're going to execute against all 300 tables.. do you still want to generate a character variable to hold this massive statement so that if (when) it breaks you'll get "Error on line 1 of...." or hey! put it in a procedure and THEN put it in your statement to save space.. but then you still get "Error on line 1 of..." when it breaks.
Generally, I use a cursor because it's a non-production issue, it's developmental and I'm using it only to save myself typing time.. it's likely a one time event.. but I'd rather have ONE particular command fail that I can debug than have the whole block fail and try to figure out where it's falling down.
The point I was trying to get across was that there ARE times an places that a cursor can be useful.. just need to be careful about when and where, you know?
We aren't denying that there may be times when cursors are appropriate. I admit to using them for maintenance or one-time code. And yes, I have created dynamic code that actually exceeds 8000 bytes and does multiple commands.
April 1, 2013 at 2:34 pm
Lynn Pettis (4/1/2013)
And yes, I have created dynamic code that actually exceeds 8000 bytes and does multiple commands.
Mind if I ask what made you break them into multiple commands?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 1, 2013 at 2:37 pm
I use a slightly different method to avoid some of the entitization problems FOR XML brings with it. Technically it is an undocumented method that I know of but I have not had trouble with it since SQL 2000 and I know of some other techniques that leverage it without trouble too:
DECLARE @SQLCmd NVARCHAR(MAX) = N'';
-- instead of using FOR XML just append the conjured string from each row to the same variable
SELECT @SQLCmd += 'EXEC sys.sp_rename ' + --
'@objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --
'@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --
'@objtype = N''OBJECT'';' + -- OBJECT
NCHAR(13) + NCHAR(10)
FROM sys.tables tab;
-- show the entire variable contents as an XML document...no truncation like with PRINT
SELECT @SQLCmd AS [processing-instruction(query)]
FOR XML PATH(''),
TYPE;
-- when ready, uncomment
--EXEC sys.sp_executesql @SQLCmd;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply