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:4932
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
