Azuraith Azuraith - 1 month ago 13
SQL Question

Can i copy data from one database table to another already existing database table sql server?

So i have 2 very similar databases, they are identical except for the data that exists in the tables. I want to copy the data from the EQUIP_MODEL table that exists in the PILOT database to the EQUIP_MODEL table that exists in the DOMAIN database.

Is this even possible? or do i have to do manual inserts for all the data?

Answer

You can use fully qualified names in Insert statement

INSERT INTO DOMAIN.SCHEMANAME.EQUIP_MODEL (col1,col2,col3...)
SELECT col1,col2,col3.. FROM PILOT.SCHEMANAME.EQUIP_MODEL

To get the foreign key values (not the exact code you have to alter based on column name and mapping)

INSERT INTO DOMAIN.SCHEMANAME.EQUIP_MODEL
            (id,col2,col3)
SELECT sp.id,
       col2,
       col3
FROM   PILOT.SCHEMANAME.EQUIP_MODEL em
       JOIN PILOT.SCHEMANAME.Prent_table p
         ON em.id = p.id
       JOIN DOMAIN.SCHEMANAME.parent_table sp
         ON sp.somename_number_col = p.somename_number_col 
Comments