Hi,

I need to either change the owner of a table or create a
replacement table and load the data into it. I tried insert
into select but there is an identity column and it won't
allow that due to an "identity field".

Could I BCP out the data and insert into a new table owned
by different id?

Thx,
rick_806

In ASE 15.0.2 both of the following work:

1 - insert/select

-- create target_table to look like source_table
create target_table (<column_list_including_identity_column>)
go
set identity_insert target_table on
go
insert target_table (<column_list>)
select <column_list> from source_table
go

NOTE: bcp out of source_table and into target_table should also work; see the '-E' bcp option.

2 - select/into

select * into target_table
from source_table
go

If you're getting an error then please post your @@version as well as the complete set of SQL along with all outputs
(including all error messages).

----------------------------

Alternatively you could look at modifying system tables (eg, change sysobjects.uid).

You'll need to peruse the ER diagram of the system tables to see what other tables you may need to modify based on your
environment (eg, sysprotects, sysencryptkeys, systypes).

If you go this route I'd suggest you use a test/dummy database *and* make sure you checkpoint and bounce the dataserver
after you've made the changes so that the dataserver's in-memory structures pick up the system table changes.

And no, this is not an operation supported by Sybase TechSupport.



rick_806 wrote:

In ASE 15.0.2 both of the following work:

1 - insert/select

-- create target_table to look like source_table
create target_table (<column_list_including_identity_column>)
go
set identity_insert target_table on
go
insert target_table (<column_list>)
select <column_list> from source_table
go

NOTE: bcp out of source_table and into target_table should also work; see the '-E' bcp option.

2 - select/into

select * into target_table
from source_table
go

If you're getting an error then please post your @@version as well as the complete set of SQL along with all outputs
(including all error messages).

----------------------------

Alternatively you could look at modifying system tables (eg, change sysobjects.uid).

You'll need to peruse the ER diagram of the system tables to see what other tables you may need to modify based on your
environment (eg, sysprotects, sysencryptkeys, systypes).

If you go this route I'd suggest you use a test/dummy database *and* make sure you checkpoint and bounce the dataserver
after you've made the changes so that the dataserver's in-memory structures pick up the system table changes.

And no, this is not an operation supported by Sybase TechSupport.



rick_806 wrote:

In ASE 15.0.2 both of the following work:

1 - insert/select

-- create target_table to look like source_table
create target_table (<column_list_including_identity_column>)
go
set identity_insert target_table on
go
insert target_table (<column_list>)
select <column_list> from source_table
go

NOTE: bcp out of source_table and into target_table should also work; see the '-E' bcp option.

2 - select/into

select * into target_table
from source_table
go

If you're getting an error then please post your @@version as well as the complete set of SQL along with all outputs
(including all error messages).

----------------------------

Alternatively you could look at modifying system tables (eg, change sysobjects.uid).

You'll need to peruse the ER diagram of the system tables to see what other tables you may need to modify based on your
environment (eg, sysprotects, sysencryptkeys, systypes).

If you go this route I'd suggest you use a test/dummy database *and* make sure you checkpoint and bounce the dataserver
after you've made the changes so that the dataserver's in-memory structures pick up the system table changes.

And no, this is not an operation supported by Sybase TechSupport.



rick_806 wrote:

In ASE 15.0.2 both of the following work:

1 - insert/select

-- create target_table to look like source_table
create target_table (<column_list_including_identity_column>)
go
set identity_insert target_table on
go
insert target_table (<column_list>)
select <column_list> from source_table
go

NOTE: bcp out of source_table and into target_table should also work; see the '-E' bcp option.

2 - select/into

select * into target_table
from source_table
go

If you're getting an error then please post your @@version as well as the complete set of SQL along with all outputs
(including all error messages).

----------------------------

Alternatively you could look at modifying system tables (eg, change sysobjects.uid).

You'll need to peruse the ER diagram of the system tables to see what other tables you may need to modify based on your
environment (eg, sysprotects, sysencryptkeys, systypes).

If you go this route I'd suggest you use a test/dummy database *and* make sure you checkpoint and bounce the dataserver
after you've made the changes so that the dataserver's in-memory structures pick up the system table changes.

And no, this is not an operation supported by Sybase TechSupport.



rick_806 wrote:

In ASE 15.0.2 both of the following work:

1 - insert/select

-- create target_table to look like source_table
create target_table (<column_list_including_identity_column>)
go
set identity_insert target_table on
go
insert target_table (<column_list>)
select <column_list> from source_table
go

NOTE: bcp out of source_table and into target_table should also work; see the '-E' bcp option.

2 - select/into

select * into target_table
from source_table
go

If you're getting an error then please post your @@version as well as the complete set of SQL along with all outputs
(including all error messages).

----------------------------

Alternatively you could look at modifying system tables (eg, change sysobjects.uid).

You'll need to peruse the ER diagram of the system tables to see what other tables you may need to modify based on your
environment (eg, sysprotects, sysencryptkeys, systypes).

If you go this route I'd suggest you use a test/dummy database *and* make sure you checkpoint and bounce the dataserver
after you've made the changes so that the dataserver's in-memory structures pick up the system table changes.

And no, this is not an operation supported by Sybase TechSupport.



rick_806 wrote:

In ASE 15.0.2 both of the following work:

1 - insert/select

-- create target_table to look like source_table
create target_table (<column_list_including_identity_column>)
go
set identity_insert target_table on
go
insert target_table (<column_list>)
select <column_list> from source_table
go

NOTE: bcp out of source_table and into target_table should also work; see the '-E' bcp option.

2 - select/into

select * into target_table
from source_table
go

If you're getting an error then please post your @@version as well as the complete set of SQL along with all outputs
(including all error messages).

----------------------------

Alternatively you could look at modifying system tables (eg, change sysobjects.uid).

You'll need to peruse the ER diagram of the system tables to see what other tables you may need to modify based on your
environment (eg, sysprotects, sysencryptkeys, systypes).

If you go this route I'd suggest you use a test/dummy database *and* make sure you checkpoint and bounce the dataserver
after you've made the changes so that the dataserver's in-memory structures pick up the system table changes.

And no, this is not an operation supported by Sybase TechSupport.



rick_806 wrote:

In ASE 15.0.2 both of the following work:

1 - insert/select

-- create target_table to look like source_table
create target_table (<column_list_including_identity_column>)
go
set identity_insert target_table on
go
insert target_table (<column_list>)
select <column_list> from source_table
go

NOTE: bcp out of source_table and into target_table should also work; see the '-E' bcp option.

2 - select/into

select * into target_table
from source_table
go

If you're getting an error then please post your @@version as well as the complete set of SQL along with all outputs
(including all error messages).

----------------------------

Alternatively you could look at modifying system tables (eg, change sysobjects.uid).

You'll need to peruse the ER diagram of the system tables to see what other tables you may need to modify based on your
environment (eg, sysprotects, sysencryptkeys, systypes).

If you go this route I'd suggest you use a test/dummy database *and* make sure you checkpoint and bounce the dataserver
after you've made the changes so that the dataserver's in-memory structures pick up the system table changes.

And no, this is not an operation supported by Sybase TechSupport.



rick_806 wrote:

In ASE 15.0.2 both of the following work:

1 - insert/select

-- create target_table to look like source_table
create target_table (<column_list_including_identity_column>)
go
set identity_insert target_table on
go
insert target_table (<column_list>)
select <column_list> from source_table
go

NOTE: bcp out of source_table and into target_table should also work; see the '-E' bcp option.

2 - select/into

select * into target_table
from source_table
go

If you're getting an error then please post your @@version as well as the complete set of SQL along with all outputs
(including all error messages).

----------------------------

Alternatively you could look at modifying system tables (eg, change sysobjects.uid).

You'll need to peruse the ER diagram of the system tables to see what other tables you may need to modify based on your
environment (eg, sysprotects, sysencryptkeys, systypes).

If you go this route I'd suggest you use a test/dummy database *and* make sure you checkpoint and bounce the dataserver
after you've made the changes so that the dataserver's in-memory structures pick up the system table changes.

And no, this is not an operation supported by Sybase TechSupport.



rick_806 wrote:

In ASE 15.0.2 both of the following work:

1 - insert/select

-- create target_table to look like source_table
create target_table (<column_list_including_identity_column>)
go
set identity_insert target_table on
go
insert target_table (<column_list>)
select <column_list> from source_table
go

NOTE: bcp out of source_table and into target_table should also work; see the '-E' bcp option.

2 - select/into

select * into target_table
from source_table
go

If you're getting an error then please post your @@version as well as the complete set of SQL along with all outputs
(including all error messages).

----------------------------

Alternatively you could look at modifying system tables (eg, change sysobjects.uid).

You'll need to peruse the ER diagram of the system tables to see what other tables you may need to modify based on your
environment (eg, sysprotects, sysencryptkeys, systypes).

If you go this route I'd suggest you use a test/dummy database *and* make sure you checkpoint and bounce the dataserver
after you've made the changes so that the dataserver's in-memory structures pick up the system table changes.

And no, this is not an operation supported by Sybase TechSupport.



rick_806 wrote: