I have a script (can't us stored procedure) that checks for duplicate records and then eliminates them. It first selects dups and then checks variable, @DeleteDups, to determine if the user wants to eliminate one dups until only one record is left. If @DeleteDups = 1, the delete portion of the script is run. I would like to stop the script with a dialog box that asks if the user really wants to delete the dups. How can one create such a dialog box?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
T-SQL Dialog Box
- Thread starter grnzbra
- Start date
Chris Miller
Programmer
There is no T-SQL messagebox or other dialog, as T-SQL runs serverside and not at client. If there would be uerinterface it make no sense popping up up serverside, where a user can't see it and can't interact with, but SQL Server has no other task than to send back results of the script - at best multiple results and errors. The SQL Server has no permissions to act on the client that connects to it, so such visual interactions are not part of the T-SQL language at all. You can think of a connection as having two pipes, the main one for sending back results, like stdout and the other for noticing the connnected clients of errors, like stderr.
The only other command that returns something to the client I know from the top of my head is PRINT, which used within SSMS is shown in the messages tab, so it makes use of the "stderr" pipe.
If you want to have user interface, you have to program something with any client tool, starting from powershell, vba in office, or java Visual Studio languges that act as moderation between user and database server. In short that's what classically can be done with a ddesktop application. But also a web application, provided the server side of such a web app is able to work with thee SQL Server.
So the whole world is open for your need, but not T-SQL.
Notice, I was waiting with my answer to see if others know something else. Now that you got no other answer after a day I'm pretty confident that's all there is.
There is at least one thing SSMS does that's not T-SQL, it's the GO command, which is not part of T-SQL, but means send the script up to that line with GO to SQL-Server, execute and come back.
And here's one hint there could be a bit more than GO: If you look into the documentation of GO the title is "SQL Server Utilities Statements - GO".
That suggests it's only one of more such statements that are run on the utuility side - client side. I guess using sqlcmd.exe instead of SSMS you might have more at hand, like DOS interactions with users at the shell window level or console, likke waiting for a Y/N or a number.
When it comes to executing T-SQL script your best option without further programming is define variables at the top of the script - to make it comfortable for a "user" and allow setting vriable values, but that's not interactive, that's done in advance.
But if you ask me, the best option to offer interaction with the user is starting with that aspect and write a desktop or web application with an interface to start with, instead of handling everything just through SSMS query windows and scripts. That's meant for DBAs, not end users, anyway.
On the other hand, if you are a DBA, ou shouldn't have problems scripting SQL to answer your own database questions very individually, use multiple scripts, get at the data you need to put into parameterization of the next scripts, etc.
Chriss
The only other command that returns something to the client I know from the top of my head is PRINT, which used within SSMS is shown in the messages tab, so it makes use of the "stderr" pipe.
If you want to have user interface, you have to program something with any client tool, starting from powershell, vba in office, or java Visual Studio languges that act as moderation between user and database server. In short that's what classically can be done with a ddesktop application. But also a web application, provided the server side of such a web app is able to work with thee SQL Server.
So the whole world is open for your need, but not T-SQL.
Notice, I was waiting with my answer to see if others know something else. Now that you got no other answer after a day I'm pretty confident that's all there is.
There is at least one thing SSMS does that's not T-SQL, it's the GO command, which is not part of T-SQL, but means send the script up to that line with GO to SQL-Server, execute and come back.
And here's one hint there could be a bit more than GO: If you look into the documentation of GO the title is "SQL Server Utilities Statements - GO".
That suggests it's only one of more such statements that are run on the utuility side - client side. I guess using sqlcmd.exe instead of SSMS you might have more at hand, like DOS interactions with users at the shell window level or console, likke waiting for a Y/N or a number.
When it comes to executing T-SQL script your best option without further programming is define variables at the top of the script - to make it comfortable for a "user" and allow setting vriable values, but that's not interactive, that's done in advance.
But if you ask me, the best option to offer interaction with the user is starting with that aspect and write a desktop or web application with an interface to start with, instead of handling everything just through SSMS query windows and scripts. That's meant for DBAs, not end users, anyway.
On the other hand, if you are a DBA, ou shouldn't have problems scripting SQL to answer your own database questions very individually, use multiple scripts, get at the data you need to put into parameterization of the next scripts, etc.
Chriss
Your database obviously allows creating duplicate records. If that’s OK, why do you need to delete the ‘Dups’? Either do not allow duplicate records (easy), or display a warning in your application at the Insert statement that ‘This action will create a duplicate record(s). Do you want to continue? Y/N’, or write the (dups) records into a temp table to be resolved later.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
Chris Miller
Programmer
Andy is right, it's as simple as defining a column or several columns to be unique in the table design to not ever get duplicates.
I'm not sure if you're asking only for this specific problem or a more general idea of interaction.
One way that could work here as far as you didn't prevent duplicates is to write a script that lists all duplicates not only as a script result, but puts them into a table. Then ask the "user" to delete the one record they want to keep. That sounds counterintuitive, so see it this way: Ask them to specify an elemination list.
The idea would be the next script they run as the aftermath of this would eliminate all records from the original table it finds in that result table, so the users can keep one or also more records - if that makes sense at all - by deleting it/them from the result you'd call an "elimination list".
Then the script you need would be a DELETE using an INNER JOIN on the elimination list.
I'd also find this far more effective than being asked Y/N for every record. Besides the result list gives me an overview to decide from, instead of only seeing row by row, it's easier to see the worthless duplicates when you have the list of them all and not just have them one by one, isn't it?
And last not least it's also not hard to decide automatically by state of other fields, contradictions that need to be removed, or less valuable records with nulls in fields, for example. I also already corrected data not just by picking which record to keep, but by merging information from multiple records that each was only half the record that should exist. So it's not just an elemination process often, anyway.
Chriss
I'm not sure if you're asking only for this specific problem or a more general idea of interaction.
One way that could work here as far as you didn't prevent duplicates is to write a script that lists all duplicates not only as a script result, but puts them into a table. Then ask the "user" to delete the one record they want to keep. That sounds counterintuitive, so see it this way: Ask them to specify an elemination list.
The idea would be the next script they run as the aftermath of this would eliminate all records from the original table it finds in that result table, so the users can keep one or also more records - if that makes sense at all - by deleting it/them from the result you'd call an "elimination list".
Then the script you need would be a DELETE using an INNER JOIN on the elimination list.
I'd also find this far more effective than being asked Y/N for every record. Besides the result list gives me an overview to decide from, instead of only seeing row by row, it's easier to see the worthless duplicates when you have the list of them all and not just have them one by one, isn't it?
And last not least it's also not hard to decide automatically by state of other fields, contradictions that need to be removed, or less valuable records with nulls in fields, for example. I also already corrected data not just by picking which record to keep, but by merging information from multiple records that each was only half the record that should exist. So it's not just an elemination process often, anyway.
Chriss
- Thread starter
- #5
Thanks for the suggestions. Unfortunately, the "user" is a dba who may or may not have set the @DeleteDups variable to zero when he just wants to determine if there are duplicates but doesn't want to eliminate them at that time.
I didn't design the database and am not allowed to modify it so I can't set up the table to prevent duplicate records. It shouldn't have dups and there is a complex series of scripts to be run on this database which will be seriously messed up if there are dups. So, eventually the dups must be eliminated but they should be found and inspected to see if there might be a reason for their existence.
I didn't design the database and am not allowed to modify it so I can't set up the table to prevent duplicate records. It shouldn't have dups and there is a complex series of scripts to be run on this database which will be seriously messed up if there are dups. So, eventually the dups must be eliminated but they should be found and inspected to see if there might be a reason for their existence.
![[pc2]](/data/assets/smilies/pc2.gif "[pc2] [pc2]")
Chris Miller
Programmer
I agree, bad db design.
So where is the problem? Then just as I suggested the first result of an analysis script should be determining all duplicates. How to process them will then be the task of another script. You're still trying to squeeze two tasks into one script. Either ou start programming and can offer that or you split this up into two smaller tasks, as is a usual strategy. Never heard of "divide et impera".
I suggested you put the reults into a new table and use that for further processing, but it's completely up to you, how to go on after knowing the duplicates.
As I also said it will be useful to have the full overview anyway. So what's now hindering you to at least come up with the first script that finds and lists the duplicates? You're very focused on this variable @DeleteDups. Well, the simple solution would be to shorten the script in the first place and not run the deletion part. Then make a second script and see how ou handle the "user input" about what to delete there. I suggest you use a table to be able to define an elemination list. Don't know what suits you best.
Now don't get at me saying you can't modify the scripts you have. You wanted to add a dialog to them, didn't you? You can also always start with a new listduplicates.sql text file and write the script up to finding the duplicates, to run that via SSMS.
Chriss
grnzbra said:
So where is the problem? Then just as I suggested the first result of an analysis script should be determining all duplicates. How to process them will then be the task of another script. You're still trying to squeeze two tasks into one script. Either ou start programming and can offer that or you split this up into two smaller tasks, as is a usual strategy. Never heard of "divide et impera".
I suggested you put the reults into a new table and use that for further processing, but it's completely up to you, how to go on after knowing the duplicates.
As I also said it will be useful to have the full overview anyway. So what's now hindering you to at least come up with the first script that finds and lists the duplicates? You're very focused on this variable @DeleteDups. Well, the simple solution would be to shorten the script in the first place and not run the deletion part. Then make a second script and see how ou handle the "user input" about what to delete there. I suggest you use a table to be able to define an elemination list. Don't know what suits you best.
Now don't get at me saying you can't modify the scripts you have. You wanted to add a dialog to them, didn't you? You can also always start with a new listduplicates.sql text file and write the script up to finding the duplicates, to run that via SSMS.
Chriss
Chris Miller
Programmer
If I'd tart from scratch the first script would be a simple SQL, even just like this:
Where fieldlist is a comma-separated list of fields, that should not have duplicates. In the simplest case just one field.
To let the user see the full rows that have duplicates you'll need to query:
Where field1, field2 are the fields of the fieldlist of the first part of the script, in the simplest case, again, just one field.
So even if you start from scratch it's mainly a two liner of code. And looking into the duplicates you then might either write a delete query using the key values of the records you want to remove or do as I suggested and extend this using table to use as input for a next script.
Chriss
Code:
Select fieldlist FROM table group by fieldlist having count(*)>1Where fieldlist is a comma-separated list of fields, that should not have duplicates. In the simplest case just one field.
To let the user see the full rows that have duplicates you'll need to query:
Code:
;With duplicates as Select fieldlist FROM table group by fieldlist having count(*)>1
SELECT * FROM table T inner join duplicates D on T.field1 = D.field1 and T.field2 = D.field2,...Where field1, field2 are the fields of the fieldlist of the first part of the script, in the simplest case, again, just one field.
So even if you start from scratch it's mainly a two liner of code. And looking into the duplicates you then might either write a delete query using the key values of the records you want to remove or do as I suggested and extend this using table to use as input for a next script.
Chriss
Chris Miller
Programmer
If you have no ownership of the database, as it seems, creating a table to store this result and then use it in a second script could also be done using temp tables.
You can create global temp tables starting their name with a double,like ##duplicates. You don't require to be granted permission to create tables in this database.
On the more general ide about them and how to use tables as tble-valued parameters, here's a nice article by redgate:
That can become your vehicle for the missing dialog interaction with the user. Split up tasks into partI and partII and let partI have a table as a result, maybe a temp table, that the dba can modify before running partII, and you have your user interaction part done.
It would, as said, be an easy task, if you do some desktop programming with anything that can offer a more comfortable user interaction than just running scripts in SSMS, but that's obviously a steeper learning curve.
Chriss
You can create global temp tables starting their name with a double,like ##duplicates. You don't require to be granted permission to create tables in this database.
On the more general ide about them and how to use tables as tble-valued parameters, here's a nice article by redgate:
That can become your vehicle for the missing dialog interaction with the user. Split up tasks into partI and partII and let partI have a table as a result, maybe a temp table, that the dba can modify before running partII, and you have your user interaction part done.
It would, as said, be an easy task, if you do some desktop programming with anything that can offer a more comfortable user interaction than just running scripts in SSMS, but that's obviously a steeper learning curve.
Chriss
- Status
Similar threads
- Locked
- Question
- Locked
- Question
