This page is part of the Tabulify learning guide and to be able to follow it, you should have followed the transfer step.
The transfer page should have made you familiar with the transfer concept where we have introduced the copy operation.
The copy and move operation does not modify any actual data. They just create a target data resource that is an exact copy of the source data resource.
This page introduces you to the data modification operations that modify the target data resource content. ie
They all works the same way, they accepts a source data resource that defines the data to insert, update, upsert or delete.
The warehouse table will be our target data resource for this page.
The warehouse table has one row that we can see with the data print command
tabli data print warehouse@sqlite
w_warehouse_sk w_warehouse_id w_warehouse_name w_warehouse_sq_ft w_street_number w_street_name w_street_type w_suite_number w_city w_county w_state w_zip w_country w_gmt_offset
-------------- ---------------- ------------------- ----------------- --------------- ------------- ------------- -------------- ------ ----------------- ------- ----- ------------- ------------
1 AAAAAAAABAAAAAAA Conventional childr 977787 651 6th Parkway Suite 470 Midway Williamson County TN 31904 United States -5
The upsert data operation is supported by the data upsert command.
This upsert operation:
We will:
The data to upsert is defined in the warehouse_upsert.csv Csv data resource in the howto connection.
We take a look at its content with the data print command:
tabli data print warehouse_upsert.csv@howto
w_warehouse_sk w_warehouse_id w_warehouse_name w_warehouse_sq_ft w_street_number w_street_name w_street_type w_suite_number w_city w_county w_state w_zip w_country w_gmt_offset
-------------- ---------------- ----------------------- ----------------- --------------- ------------- ------------- -------------- --------- ----------------- ------- -------- ------------- ------------
1 AAAAAAAABAAAAAAA Modified Warehouse Name 977787 651 6th Parkway Suite 470 Midway Williamson County TN 31904 United States -5
2 AAAAAAAABAAAAAAB Van Gogh Warehouse 100000 6 Museumplein Plein Amsterdam Nord-Holland NH 10171 DJ Netherlands 1
tabli data upsert warehouse_upsert.csv@howto warehouse@sqlite
Source Target Latency (ms) Row Count Error Message
-------------------------- ---------------- ------------ --------- ----- -------
warehouse_upsert.csv@howto warehouse@sqlite 24 2
tabli data print warehouse@sqlite
w_warehouse_sk w_warehouse_id w_warehouse_name w_warehouse_sq_ft w_street_number w_street_name w_street_type w_suite_number w_city w_county w_state w_zip w_country w_gmt_offset
-------------- ---------------- ----------------------- ----------------- --------------- ------------- ------------- -------------- --------- ----------------- ------- -------- ------------- ------------
1 AAAAAAAABAAAAAAA Modified Warehouse Name 977787 651 6th Parkway Suite 470 Midway Williamson County TN 31904 United States -5
2 AAAAAAAABAAAAAAB Van Gogh Warehouse 100000 6 Museumplein Plein Amsterdam Nord-Holland NH 10171 DJ Netherlands 1
Let's say for the sake of simplicity that we want to increase the square footage column w_warehouse_sq_ft of the warehouse by 10 square meter.
The source query needs to include:
Example with the warehouse_update.sql file in the howto connection
tabli data print warehouse_update.sql@howto
select
w_warehouse_sk,
w_warehouse_sq_ft+10 as w_warehouse_sq_ft, -- the name of the column match the name of the target column
w_warehouse_sq_ft as w_warehouse_sq_ft_old_value -- just to show the diff
from
warehouse w2
;
tabli data print (warehouse_update.sql@howto)@sqlite
w_warehouse_sk w_warehouse_sq_ft w_warehouse_sq_ft_old_value
-------------- ----------------- ---------------------------
1 977797 977787
2 100010 100000
tabli data update (warehouse_update.sql@howto)@sqlite warehouse@sqlite
Source Target Latency (ms) Row Count Error Message
----------------------------------- ---------------- ------------ --------- ----- -------
(warehouse_update.sql@howto)@sqlite warehouse@sqlite 15 2
tabli data print warehouse@sqlite
w_warehouse_sk w_warehouse_id w_warehouse_name w_warehouse_sq_ft w_street_number w_street_name w_street_type w_suite_number w_city w_county w_state w_zip w_country w_gmt_offset
-------------- ---------------- ----------------------- ----------------- --------------- ------------- ------------- -------------- --------- ----------------- ------- -------- ------------- ------------
1 AAAAAAAABAAAAAAA Modified Warehouse Name 977797 651 6th Parkway Suite 470 Midway Williamson County TN 31904 United States -5
2 AAAAAAAABAAAAAAB Van Gogh Warehouse 100010 6 Museumplein Plein Amsterdam Nord-Holland NH 10171 DJ Netherlands 1
The delete command takes as input a source data resource that contains the primary or unique key of a target table.
We want to delete the second record, the CSV file contains then the primary key with the value 2
tabli data print warehouse_delete.csv@howto
w_warehouse_sk
--------------
2
tabli data delete warehouse_delete.csv@howto warehouse@sqlite
Source Target Latency (ms) Row Count Error Message
-------------------------- ---------------- ------------ --------- ----- -------
warehouse_delete.csv@howto warehouse@sqlite 14 1
tabli data list -a name -a count warehouse@sqlite
name count
--------- -----
warehouse 1
tabli data print warehouse@sqlite
w_warehouse_sk w_warehouse_id w_warehouse_name w_warehouse_sq_ft w_street_number w_street_name w_street_type w_suite_number w_city w_county w_state w_zip w_country w_gmt_offset
-------------- ---------------- ----------------------- ----------------- --------------- ------------- ------------- -------------- ------ ----------------- ------- ----- ------------- ------------
1 AAAAAAAABAAAAAAA Modified Warehouse Name 977797 651 6th Parkway Suite 470 Midway Williamson County TN 31904 United States -5
With the laws on data privacy, it's now mandatory to develop data application with generated data. Tabulify has built-in a data generator that we will explore in the next page.
How to fill a resource with generated data ?