May 24, 2012 at 2:15 pm
I want to preface this question by saying that I am new to SQL and new to these forums so bear with me if I am not being concise enough on the problem. I am trying to figure out how I can traverse through a specified column in a table and change the values in a table to an incrementing counter. EX:
create table person(fname varchar(30), lname varchar(50), ssn varchar(11));
insert into person values('John', 'Jay', '123-45-6789');
insert into person values('John', 'Doe', '123-45-6765');
insert into person values('Jane', 'Doe', '321-65-5465');
insert into person values('James', 'Smith', '549-68-1235');
insert into person values('Jane', 'Smith', '654-32-2915');
select * from person
JohnJay123-45-6789
JohnDoe792-41-6295
JaneDoe321-65-5465
JamesSmith549-68-1235
JaneSmith654-32-2915
I don't know what the best way to do this is, but what I am trying to do is create a stored procedure which can accept different column names, data types, etc. The main purpose of this procedure would be to extract the SSN and instead of putting it back in the table replace it with a counter value so the output looks similar to the one below. I was curious if it is possible to accomplish this without the use of cursors? I have been toying around with the idea of using a cursor, but seemingly everywhere I read it says that cursors use up a ton of overhead and not to use them. I am not too familiar with cursors either and I haven't had much luck implementing even simple cursors in my code. Any help on this topic would be greatly appreciated.
JohnJay1001
JohnDoe1002
JaneDoe1003
JamesSmith1004
JaneSmith1005
^^ What I want output to look like
May 24, 2012 at 2:22 pm
This?
select * , rn = ROW_NUMBER() over (order by ssn) + 1000
from person
May 24, 2012 at 2:23 pm
And i have based the order of the rows on SSN. If there are any ID columns that i can base the ordering on instead of SSN, use that instear the ORDER BY clause in ROW_NUMBER.
May 24, 2012 at 2:28 pm
It would be eaiser to simply create an identity columns with a Seed value of 1000. The rownum would need to be calculated everytime the proc is run which may not be a great idea esp for frequently run queries on large datasets.
Is there a corelation between the new value and the SSN ?
May 24, 2012 at 3:22 pm
Thank you for your input!
SSCommitted,
There is no real correlation between the SSN and the new value ........ The purpose of this is to mask the SSN because it is considered sensitive data. Rather than just putting all "XXXXX" or something I am trying to put counter values instead. I am still wondering would you be able to accomplish this without the use of a cursor or would you be forced to use one?
May 24, 2012 at 3:32 pm
blampe (5/24/2012)
Thank you for your input!SSCommitted,
There is no real correlation between the SSN and the new value ........ The purpose of this is to mask the SSN because it is considered sensitive data. Rather than just putting all "XXXXX" or something I am trying to put counter values instead. I am still wondering would you be able to accomplish this without the use of a cursor or would you be forced to use one?
You definitely do not need a cursor for this. You were actually shown two totally ways to accomplish what you are after.
_______________________________________________________________
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/
May 24, 2012 at 3:40 pm
No cursor; a simple way to do this is as follows:
;with list as
(
select
ssn,
row_number() over (order by ssn) + 1000 as ID
from
person
)
update p
set p.ssn = l.ID
from
person p
inner join list l on p.ssn = l.ssn
May 25, 2012 at 1:05 am
May 25, 2012 at 7:50 am
use Brett_Test
GO
if exists (select * from sysobjects where type = 'P' and name = 'countSSN')
drop procedure countSSN
GO
create procedure countSSN
as
set nocount on;
select ssn, rn = ROW_NUMBER() over (order by ssn) + 1000 from person;
GO
exec countSSN;
--ssn --rn
123-45-67651001
123-45-67891002
321-65-54651003
549-68-12351004
654-32-29151005
I got this procedure to run but as you can see the output is still showing the ssn. What I am trying to do is replace the ssn with the values in the rn column and get the output to look something like this. I was also wondering if it would be possible to pass columns as parameters through my "exec countSSN" statement? If I could do that it would be great because then I wouldn't be limited to just the ssn field and I could run this procedure on any column in the table. Again I apologize if these questions are somewhat basic, but I am new to sql.
ssn
____
1001
1002
1003
1004
1005
May 25, 2012 at 8:00 am
blampe (5/25/2012)
use Brett_TestGO
if exists (select * from sysobjects where type = 'P' and name = 'countSSN')
drop procedure countSSN
GO
create procedure countSSN
as
set nocount on;
select ssn, rn = ROW_NUMBER() over (order by ssn) + 1000 from person;
GO
exec countSSN;
--ssn --rn
123-45-67651001
123-45-67891002
321-65-54651003
549-68-12351004
654-32-29151005
I got this procedure to run but as you can see the output is still showing the ssn. What I am trying to do is replace the ssn with the values in the rn column and get the output to look something like this. I was also wondering if it would be possible to pass columns as parameters through my "exec countSSN" statement? If I could do that it would be great because then I wouldn't be limited to just the ssn field and I could run this procedure on any column in the table. Again I apologize if these questions are somewhat basic, but I am new to sql.
ssn
____
1001
1002
1003
1004
1005
It is still showing SSN because the column is in your select statement. 😀
_______________________________________________________________
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/
May 25, 2012 at 8:55 am
I don't think I'm being clear enough, what I want is the procedure to run so it will replace all of the table values under ssn with COMPLETELY DIFFERENT count values. When I run this procedure the user should theoretically be able to execute the "select ssn from person" statement and the output would be:
ssn
____
1001
1002
1003
1004
1005
May 25, 2012 at 8:58 am
blampe (5/25/2012)
I don't think I'm being clear enough, what I want is the procedure to run so it will replace all of the table values under ssn with COMPLETELY DIFFERENT count values. When I run this procedure the user should theoretically be able to execute the "select ssn from person" statement and the output would be:ssn
____
1001
1002
1003
1004
1005
I think you may be right about not being totally clear. 😉
Does this do what you want?
create procedure countSSN
as
set nocount on;
select ssn = ROW_NUMBER() over (order by ssn) + 1000 from person;
_______________________________________________________________
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/
May 25, 2012 at 9:12 am
The countSSN procedure runs fine, but what I am trying to do is actually physically replace the values in the table with these count values generated by the countSSN procedure. In essence, I want to replace the entire ssn column with the column generated by the countSSN procedure.
I was also curious about command line parameters in SQL would it be possible to pass the column name as a parameter to run this procedure on different columns in the table?
May 25, 2012 at 9:24 am
Something more like this:
create table dbo.person(fname varchar(30), lname varchar(50), ssn varchar(11));
GO
insert into dbo.person values('John', 'Jay', '123-45-6789');
insert into dbo.person values('John', 'Doe', '123-45-6765');
insert into dbo.person values('Jane', 'Doe', '321-65-5465');
insert into dbo.person values('James', 'Smith', '549-68-1235');
insert into dbo.person values('Jane', 'Smith', '654-32-2915');
GO
select * from dbo.person;
--John Jay 123-45-6789
--John Doe 792-41-6295
--Jane Doe 321-65-5465
--James Smith 549-68-1235
--Jane Smith 654-32-2915
GO
DECLARE @ssn INT;
SET @ssn = 1000;
UPDATE dbo.person SET
@ssn = @ssn + 1,
ssn = CAST(@ssn AS VARCHAR);
select * from dbo.person;
GO
DROP TABLE dbo.person;
GO
May 25, 2012 at 9:30 am
blampe (5/25/2012)
The countSSN procedure runs fine, but what I am trying to do is actually physically replace the values in the table with these count values generated by the countSSN procedure. In essence, I want to replace the entire ssn column with the column generated by the countSSN procedure.
A few people have given you code to do this... the reason your stored procedure didn't do what you want is that nowhere in it do you actually UPDATE anything. It's just selecting columns.
I was also curious about command line parameters in SQL would it be possible to pass the column name as a parameter to run this procedure on different columns in the table?
It would be possible to write something that could do that; but it's a bad idea. Whenever you're updating data - especially an entire column - it's better to keep it as simple and straightforward as possible. If you need to update multiple columns, then take the time to correctly update the columns.
A more basic problem is that once you do this - replace column values with arbitrary numbers - you've basically lost any ability to relate this data to anything else. Are there any other tables in your database with SSN? If so, how are you going to match "1001" with it's corresponding SSN?
Why not just create a view that replaces the SSN with an ID number (using the row_number technique that you've been shown in this thread) and then have your users access the view rather than the table directly? That way you still have the benefit of a natural key (SSN) instead of an arbitrary counter?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply