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!
>