0

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

Improve this question
3
  • Microsoft SQL Server 2014 Commented Jul 27, 2016 at 19:32
  • Please look here on how to improve question:spaghettidba.com/2015/04/24/… Commented Jul 27, 2016 at 19:33
  • Oh, sorry is my first post Commented Jul 27, 2016 at 19:33

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thx, but what about the "from" do I just put both databases there?
Just the first database. The second database is specified after the JOIN - this is the one you were 'looking up' in your query.
but if i try USE BiologiaBd GO INSERT INTO CuadernoCampo (codigoCuadernoCampo,idPersonal) SELECT db1.IdCC, db2.idPersonal FROM CuadernosCampo db1 JOIN BiologiaBd db2 ON db1.nombre=db2.NomeColeCC FROM Herpetologia.dbo.CuadernosCampo I get a Syntax error near the word from
That 'FROM Herpetologia.dbo.CuadernosCampo' you have at the end, you don't need it.
1

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?

Improve this answer

1 Comment

Sorry I tried to add code; that code does't work because the table cuadernosCampo is on The herpetologia database, I need to add 2 databases on the from like this FROM Herpetologia.dbo.CuadernosCampo, BiologiaBd but I get syntax error in the FROM
1

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!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.