Insert Data from another bucket using Query workbench

SQL++
1

Hi

I come from a SQL background. trying to do something like:

INSERT INTO bucket1 {select * from bucket2 }

Can’t find a good example of selecting from one bucket into another.

2

Hi @martin1, you could do this;
INSERT INTO bucket1 (key _k, value _v) SELECT META().ID _k, _v FROM bucket2 _v;

there are a couple of examples here:
Creating a copy of your bucket: http://blog.couchbase.com/2016/january/how-to-create-a-copy-of-your-Reference Guide: bucket–how-to-transform-bucket-data-with-bulk-mutations-insert-update-delete-merge-in-n1ql http://developer.couchbase.com/documentation/server/4.1/n1ql/n1ql-language-reference/insert.html

thanks
-cihan

1 Like
3

Thanks - I had seen the insert statement syntax - but there was no specific example for selecting form another bucket.

Can you please re-paste the other reference… It’s a 404 - or I can’t figure out where the url ends.

If it is this page http://blog.couchbase.com/2016/january/how-to-create-a-copy-of-your-bucket--how-to-transform-bucket-data-with-bulk-mutations-insert-update-delete-merge-in-n1ql

Ten I don’t understand how to get everything from one bucket to the other - given that select * from bucket 2 will give me data. I don’t know how to make that fit with the syntax. help :slight_smile:

INSERT INTO bucket1 {select * from bucket2 }

Kind regards
Martin

4

Use this link: How to: Create a copy of your bucket with a single statement with Couchbase Server - The Couchbase Blog

This example from @cihangirb will insert EVERYTHING from bucket2 to bucket1.

There are assumptions, of course. The target bucket should not have any keys you have in source bucket.

5

Thanks for your reply - however I got:

[
{
“code”: 5070,
“msg”: “Cannot INSERT non-string key Missing field or index _k. of type value.missingValue.”
},
{
“code”: 5070,
“msg”: “Cannot INSERT non-string key Missing field or index _k. of type value.missingValue.”
},
{
“code”: 5070,
“msg”: “Cannot INSERT non-string key Missing field or index _k. of type value.missingValue.”
},
{
“code”: 5070,
“msg”: “Cannot INSERT non-string key Missing field or index _k. of type value.missingValue.”
}
]

6

Never mind - works now.

INSERT INTO bucket1 (key _k, value _v) SELECT ID _k, _v FROM bucket2 _v;

Difference is I have my own keys - I assume that META().ID are system generted keys.

7

Note that id in META().id is case-sensitive.

1 Like
8

Corrected the original post here as well to use lowercase .id function with meta().

1 Like