By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,779 Members | 1,111 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,779 IT Pros & Developers. It's quick & easy.

generated by default or as always

P: n/a
i would like to know when i create a table with identity column,
should i used generated by default or generated as always. since when
i create this table, i might copy some data from another table with
identity column also, (so if i use generated as always, i can't do
insert into new_tables select * from old_table), then i might also
insert new data into this new table (if i do generated by default,
then the new table could have the same identity column value as the
data i copy from another table. please advice
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ian
db********@yahoo.com wrote:
i would like to know when i create a table with identity column,
should i used generated by default or generated as always. since when
i create this table, i might copy some data from another table with
identity column also, (so if i use generated as always, i can't do
insert into new_tables select * from old_table), then i might also
insert new data into this new table (if i do generated by default,
then the new table could have the same identity column value as the
data i copy from another table. please advice


The reason people use 'generated always' is so that the database will
prevent users (or applications) from inserting their own values into
the identity column, no matter what. When a user can assign a value
to an identity column you run the risk of causing problems when the
identity runs into a duplicate value.

It is possible to LOAD data into a generated-always column using LOAD
with the MODIFIED BY IDENTITYOVERRIDE option. If you do this, though,
make sure you set the appropriate starting point for your identity to
avoid conflicting values.
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #2

P: n/a
i am not using load utility, i am using sql command like insert in the
jdbc application.

Ian <ia*****@mobileaudio.com> wrote in message news:<41**********@corp.newsgroups.com>...
db********@yahoo.com wrote:
i would like to know when i create a table with identity column,
should i used generated by default or generated as always. since when
i create this table, i might copy some data from another table with
identity column also, (so if i use generated as always, i can't do
insert into new_tables select * from old_table), then i might also
insert new data into this new table (if i do generated by default,
then the new table could have the same identity column value as the
data i copy from another table. please advice


The reason people use 'generated always' is so that the database will
prevent users (or applications) from inserting their own values into
the identity column, no matter what. When a user can assign a value
to an identity column you run the risk of causing problems when the
identity runs into a duplicate value.

It is possible to LOAD data into a generated-always column using LOAD
with the MODIFIED BY IDENTITYOVERRIDE option. If you do this, though,
make sure you set the appropriate starting point for your identity to
avoid conflicting values.
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.