I can't believe this function is not simple. All I need to do is copy
the entire contents of one database table into another existing
table. The two tables exist on separate databases. I am trying to
load the patient list from Database A patients_visits table to
Database B patients_visits table.

I tried some SQL commands, and the best I could do was to create a
brand new table in my database with the copied contents. The new table
is called patients_two. So I tried this commmand:

insert into patients_visits select * from patients_two

Freaking SQL complains:

"An explicit value for the identity column in table 'patients_visits'
can only be specified when a column list is used and IDENTITY_INSERT
is ON."

Can anyone tell me what I am doing wrong, or is there an easier
method?

Thanks!

INSERT ... SELECT ... FROM is the right method. If you want to also copy the
identity values instead of letting SQL Server generate them, you need to run

SET IDENTITY_INSERT <target table> ON

before the INSERT statement. Look up SET IDENTITY_INSERT in Books Online.

Linchi

"alvinstraight38@hotmail.com" wrote:

> I can't believe this function is not simple. All I need to do is copy
> the entire contents of one database table into another existing
> table. The two tables exist on separate databases. I am trying to
> load the patient list from Database A patients_visits table to
> Database B patients_visits table.
>
> I tried some SQL commands, and the best I could do was to create a
> brand new table in my database with the copied contents. The new table
> is called patients_two. So I tried this commmand:
>
> insert into patients_visits select * from patients_two
>
> Freaking SQL complains:
>
> "An explicit value for the identity column in table 'patients_visits'
> can only be specified when a column list is used and IDENTITY_INSERT
> is ON."
>
> Can anyone tell me what I am doing wrong, or is there an easier
> method?
>
> Thanks!
>

To copy between databases you have to qualify the tables with their
database: DBName.User_Schema.TableName

To insert the data into existing table you have to list the columns when
running the statement. If you do not want to copy the exact values of the
IDENTITY column you can exclude it from the list, it will automatically
generate values in the target table. If you want to copy the values, then
set IDENTITY_INSERT to ON.

Examples below assuming the tables are under the 'dbo' user/schema:

1) Without IDENTITY column:
INSERT INTO DatabaseB.dbo.patients_visits (column1, column2, ...)
SELECT column1, column2, ... FROM DatabaseA.dbo.patients_visits

2). With IDENTITY column - set IDENTITY_INSERT to ON:
SET IDENTITY_INSERT DatabaseB.dbo.patients_visits ON

INSERT INTO DatabaseB.dbo.patients_visits (ident_column, column1, column2,
....)
SELECT ident_column, column1, column2, ... FROM
DatabaseA.dbo.patients_visits

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Try http://www.sqlscripter.com to generate data scripts.


"alvinstraight38@hotmail.com" wrote:

> I can't believe this function is not simple. All I need to do is copy
> the entire contents of one database table into another existing
> table. The two tables exist on separate databases. I am trying to
> load the patient list from Database A patients_visits table to
> Database B patients_visits table.
>
> I tried some SQL commands, and the best I could do was to create a
> brand new table in my database with the copied contents. The new table
> is called patients_two. So I tried this commmand:
>
> insert into patients_visits select * from patients_two
>
> Freaking SQL complains:
>
> "An explicit value for the identity column in table 'patients_visits'
> can only be specified when a column list is used and IDENTITY_INSERT
> is ON."
>
> Can anyone tell me what I am doing wrong, or is there an easier
> method?
>
> Thanks!
>