从另一个数据库和同一个数据库（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.

也许你们可以帮我一把;我有两个数据库，我需要将第二个数据库中的数据插入到第一个数据库中，但我还需要第一个数据库中的Id。两个数据库都在同一台服务器上。

The first database is BiologiaBd and contains the table personal with a column Id and a name called nombre.

第一个数据库是BiologiaBd，包含具有列Id和名称为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

第二个数据库名为Herpetologia，包含一个名为CuadernoCampo的表，该表有一个名为IdCC的ID和一个名为NomeColeCC的名称。这与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?

它不会起作用，因为我无法在“from”上添加第一个数据库，有些想法吗？

Thanks a lot guys

非常感谢你们

3 个解决方案

#1

You likely need a JOIN, as in:

你可能需要一个JOIN，如：

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.

假设在BiologiaBd中存在CuadernoCampo中每条记录的记录。

#2

You should just be able to specify the database name when referencing the table:

您应该只能在引用表时指定数据库名称：

insert into CuadernoCampo (codigoCuadernoCampo,idPersonal) 
SELECT IdCC, idPersonal 
FROM BiologiaBd.dbo.CuadernosCampo, <--BiologiaBd
where idPersonal = (select idPersonal from BiologiaBd where nombre = (Select   
NomeColeCC from Herpetologia.dbo.CuadernosCampo))

Does this not work?

这不起作用吗？

#3

Just if anyone wonders the answer was this

如果有人想知道答案是这样的话

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

Thx again!

再次！

#1