inserting data from another project table in BigQuery

I’ve created two table , “conversion_log_” on project1 and “test_table” on project2 in Google bigQuery. I have been trying to select and insert data from conversion_log_ to test_table . I want to transfer orderid(STRING) of conversion_log_ whose pgid and luid matches pgid and luid in test_table but I got this error “Unrecognized name: hitobito_test at [6:10]” . I’m sure my tablename is correct. I can’t get the cause of this error . Could anyone tell me ?? sorry I’m beginner at BigQuery so if I oversight something , Please let me know .

insert into hitobito_test.test_table(orderid) 
select orderid 
from 
`kuzen-198289.conversion_log.conversion_log_` as p
where 
p.pgid = hitobito_test.test_table.pgid
AND
p.luid = hitobito_test.test_table.luid

test_table

pgid | luid | cv_date | orderid
4587 | U2300 | null | null
4444 | U7777 | null | null

conversion_log_

pgid | luid | cv_date | orderid | 
3232 | U5454 | 2020-08-01 | xcdf23
9786 | U3745 | 2020-08-02 | fgtd43
4587 | U2300 | 2020-08-02 | aaav3 ⬅︎ I need to send this orderid to the first line in test_table

If I add prijectname like below , I got this message “Syntax error: Expected end of input but got identifier “hitobito_test” at [6:33]”

insert into galvanic-ripsaw-281806.hitobito_test.test_table(orderid) 
select orderid 
from 
`kuzen-198289.conversion_log.conversion_log_` as p
where 
p.pgid = galvanic-ripsaw-281806.hitobito_test.test_table.pgid
AND
p.luid = hitobito_test.test_table.luid
`

Answer

Please try this:

INSERT INTO `galvanic-ripsaw-281806.hitobito_test.test_table`(orderid)
SELECT
  orderid
FROM
  `kuzen-198289.conversion_log.conversion_log_` AS p
WHERE
  EXISTS (
  SELECT 1
  FROM
    `galvanic-ripsaw-281806.hitobito_test.test_table` h
  WHERE
    p.pgid = h.pgid AND p.luid = h.luid)

Advertisement

Answer