OK, I got it to work! I had to start with cfset x = 1. Thanks.
Nancy -----Original Message----- From: William Seiter [mailto:[email protected]] Sent: Sunday, December 20, 2009 5:51 AM To: cf-newbie Subject: RE: How to add new column with (Is Identity) Hey Nancy, If I understand correctly, you have an existing table without a primary key that you need to add a primary key to. Using cfoutput, just do a select of all the records and then do an update of that column with the running numbers. <cfquery name="tablevalues"> Select [current unique identifier_column] from [tablename] </cfquery> </cfquery> <cfset x = 1000> <cfoutput query="tablevalues"> <cfquery name="tablevalues"> Update [tablename] set [newcolumnname]= #x# Where [current unique identifier_column] = #[current unique identifier_column] # </cfquery> <cfset x = x + 1> </cfoutput> -----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Friday, December 18, 2009 12:04 PM To: cf-newbie Subject: How to add new column with (Is Identity) This is an SQL question, but I hope someone can help. I have a table that I need to add a new ID column that starts with any number and increments by 1. I can do this automatically in the Design if I am creating a new table, by setting this up in Identity Specification. But how do I write an SQL statement to add numbers in an existing table, in a new column, that are consecutive and unique that already has over 1000 records? After I have the new field filled with numbers, then I can go to the Column Properties and set up Identity Specification to add a new ID number to new records that is consecutive. Nancy Graham Air Cargo Web Developer 502.329.3263 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4933 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
