Tri Tri - 4 months ago 9
SQL Question

Insert to a table from another database AND the same database (SQL Server)

Maybe you guys can give me a hand; I have two databases and I need to insert data from the second database into the first one but I also need an Id from the first database. Both databases are on the same server.

The first database is

BiologiaBd
and contains the table
personal
with a column
Id
and a name called
nombre
.

The second database is called
Herpetologia
and contains a table called
CuadernoCampo
, this table has an id called
IdCC
and a name called
NomeColeCC
. This is the same name that the table
personal
in
BiologiaBd


So, I have something like this

USE BiologiaBd
GO

INSERT INTO CuadernoCampo (codigoCuadernoCampo, idPersonal)
SELECT
IdCC, idPersonal
FROM
Herpetologia.dbo.CuadernosCampo, <--BiologiaBd
WHERE
idPersonal = (SELECT idPersonal
FROM BiologiaBd
WHERE nombre = (SELECT NomeColeCC
FROM Herpetologia.dbo.CuadernosCampo))


It won't work because I can't add the first database on the "from", some ideas?

Thanks a lot guys

Answer

You likely need a JOIN, as in:

INSERT INTO CuadernoCampo (codigoCuadernoCampo,idPersonal) 
  SELECT db1.IdCC, db2.idPersonal FROM CuadernosCampo db1
    JOIN BiologiaBd db2 ON db1.nombre=db2.NomeColeCC

Assuming that there is a record in BiologiaBd for each record in CuadernoCampo.