Got a table with 1200 rows. Added a new column which will contain incremental values - Candidate1 Candidate2 Candidate3 . . . Candidate1200
What is the best way to insert these values , non manual way. I am using SQL Server 2008
Add a comment |
2 Answers
Assuming there's an IDENTITY column (I called it id for this example):
UPDATE YOUR_TABLE SET new_column = (SELECT 'Candidate'+ CAST(ROW_NUMBER() OVER(ORDER BY yt.id) AS VARCHAR(4)) FROM YOUR_TABLE yt WHERE yt.id = YOUR_TABLE.id) 3 Comments
RQDQ
You got to it way faster than I did - I had to look up ROW_NUMBER in the docs!
Abey
Just wondering if there wasn't any ID column or we had to do the similar operation in a new table with 2 columns (ID, Candidate)
OMG Ponies
@Abey: ROW_NUMBER require an ORDER BY clause at a minimum, shouldn't matter if you're not caring about any given row getting a specific value. I don't understand what you mean regarding "a new table with 2 columns (ID, Candidate)"
You might be able to use ROW_NUMBER
http://msdn.microsoft.com/en-us/library/ms186734.aspx
Perhaps perform a query that gets the row number and use that query to perform an update on the source table where you concatenate the Row Number with 'Candidate'.
