CODEWITHSUNDEEP
loopsgoogle-bigquery
うちはサラダ
うちはサラダ

Reputation: 41

Use declare & loop in BigQuery to insert data

I just practice using Bigquery & try to use one of my SQL query on it

declare @id int 
select @id = 1
while @id >=1 and @id <= 1000
begin
    insert into Quincy values(@id, 'Rank: ' + convert(varchar(5), @id))
end

This one run the insert 1000 times to the table, I noticed that bigquery doesn't allow declare value on it so @id doesn't work here. May i as if are there any available method to run loop like this on BigQuery?

Upvotes: 4

Views: 3352

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

It is not the best practice to use cursor-based logic when dealing with sql, rather you need to tune yourself into set-based processing. Especially with BigQuery which does not support [yet] cursor processing and procedural logic, but luckily has rich support for ARRAYs which can be used here

As your question is little abstract - below example is abstract too, but gives you an idea 

#standardSQL
INSERT INTO `project.dataset.Quincy` (id, col)
WITH array_to_loop_through AS (
  SELECT id 
  FROM UNNEST(GENERATE_ARRAY(1, 1000, 1)) id
)
SELECT id, CONCAT('Rank: ', CAST(id AS STRING))
FROM array_to_loop_through

Upvotes: 1

Related Questions