Single snapshot for multiple CREATE OR REPLACE TABLE operations

[lawmatsuyama]

Hello,

I have a process that runs several CREATE OR REPLACE TABLE ... AS SELECT ... statements (previously there was no MERGE INTO available). For each CREATE OR REPLACE TABLE, a new snapshot is created. I’d like to know if it’s possible to produce only one snapshot for a batch of these operations.

Example

CREATE OR REPLACE TABLE mytable1 AS
SELECT id, name
FROM mysourcetable1;

CREATE OR REPLACE TABLE mytable2 AS
SELECT id, name
FROM mysourcetable2;

Today this produces two snapshots (one per table). My goal would be to have a single snapshot covering both operations in the same run/transaction. Is that possible? If yes, what’s the recommended way to achieve it?

[Alex-Monahan]

Howdy! Have you happened to try wrapping your sql queries in BEGIN TRANSACTION; ... queries! ... COMMIT; ? I think that may produce the results you are looking for!

[lawmatsuyama]

Actually I did it, but I was running those CREATE commands concurrently before and I don't know why it doesn't work. Now I ran them in the DuckDB command line sequentially, and it worked. I forgot to mention this here.

[lawmatsuyama]

It was my mistake, but now it works. Thanks!