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
+2
A:
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'.
RQDQ
2010-08-18 03:09:33
+2
A:
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)
OMG Ponies
2010-08-18 03:12:59
That works perfect. Thanks
Abey
2010-08-18 03:24:43
You got to it way faster than I did - I had to look up ROW_NUMBER in the docs!
RQDQ
2010-08-18 03:25:32
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)
Abey
2010-08-18 04:09:24
@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)"
OMG Ponies
2010-08-18 17:23:00