Bulk insert records from a table - with conditions

Queries and Resources
, ,
1

I am trying to Bulk insert records from a table but the column names from the table need to be inserted into columns in my MySQL db with names which are different.

The logic is below:

INSERT INTO Tbl_samples (Sample_ID, Date_Sampled, Easting_m, Northing_m, Elevation_m)
SELECT
    item.Wpt_SampleID AS Sample_ID,
    item.Date_taken AS Date_Sampled,
    item.Easting AS Easting_m,
    item.Northing AS Northing_m,
    item.Elevation_m AS Elevation_m,
  
FROM
    (SELECT * FROM {{table1.data}}) AS item
WHERE
    item.Wpt_SampleID IS NOT NULL
    AND item.Wpt_SampleID LIKE '3%';

However I am getting the error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''[object Object]', '[object Object]', '[object Object]', '[object Object]', '...' at line 10

Is there anyway to achieve what I want to do?

Thanks

2

Does changing

(SELECT * FROM {{table1.data}}) AS item

to

(SELECT * FROM {{formatDataAsArray(table1.data)}}) AS item

change the results in a meaningful way?

1 Like
3

I changed the code to:

INSERT INTO Tbl_samples (Sample_ID, Date_Sampled, Easting_m, Northing_m, Elevation_m)
SELECT
    item.Wpt_SampleID AS Sample_ID,
    item.Date_taken AS Date_Sampled,
    item.Easting AS Easting_m,
    item.Northing AS Northing_m,
    item.Elevation_m AS Elevation_m
FROM
    (SELECT * FROM {{formatDataAsArray(table1.data)}}) AS item
WHERE
    item.Wpt_SampleID IS NOT NULL AND 
    item.Wpt_SampleID LIKE '3%';

Now getting this error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AS item WHERE item.Wpt_SampleID IS NOT NULL AND item.Wpt_SampleID ...' at line 9

4

I think you need to take the ( ) out of the SELECT clause on line 9:

SELECT * FROM {{formatDataAsArray(table1.data)}} AS item

At least, that is what the error looks like it is saying. The other thing to try would be maybe to put the closing ) at the end of the whole second SELECT clause -- after the WHERE.

1 Like
5

Hi there! One note is that table.data is probably already an array, so it won't necessarily help to wrap it in formatDataAsArray(). You may still run into syntax issues beyond the table data and the parentheses :thinking:

I would recommend this approach instead:

  1. Create a Query JSON with SQL query that selects the table data and updates the column names

2. Then, run a sql update using our GUI mode & referencing this new query:

You could also solve this using a single SQL query with Javascript inside the "Array of records to insert" field:

Hope that helps!

2 Likes
6

Hi Tess,

This is great thank you this works.

I am also trying to format the date to a new format from the table. It is a string like this: 2023-07-11T02:28:44Z which I want to format to date 'yy-mm-dd'

And so I am trying to use the code below but getting the error message:"alasql.fn.STR_TO_DATE is not a function"

SELECT
    item.Wpt_SampleID AS Sample_ID,
    DATE_FORMAT(STR_TO_DATE(item.Date_taken, '%Y-%m-%dT%H:%i:%sZ'), '%Y-%m-%d') AS Date_Sampled,
    item.Easting AS Easting_m,
    item.Northing AS Northing_m,
    item.Elevation_m AS Elevation_m
FROM
    (SELECT * FROM {{ table1.data }}) AS item
WHERE
    item.Wpt_SampleID IS NOT NULL AND 
    item.Wpt_SampleID LIKE '3%';

I tried using the code below also, which correctly formats my date, but I am trying to only include records which begin with 3, and getting the error: Duplicate entry '1' for key 'Sample_ID_Tbl_Sample'

{{table1.data.map(x=>{return {'Sample_ID': x.Wpt_SampleID.startsWith('3'), 'Date_Sampled': moment(x.Date_taken).format("YYYY-MM-DD"), 'Easting_m': x.Easting, 'Northing_m': x.Northing,'Elevation_m': x.Elevation_m } })}}

The above code works fine without .startsWith('3')

Any idea how to make either of these work?

Thanks!

1 Like
7

Update: I got it all to work and do what I want with this code:

{{table1.data.filter(x => x.Wpt_SampleID.startsWith('3')).map(x=>{return {'Sample_ID': x.Wpt_SampleID, 'Date_Sampled': moment(x.Date_taken).format("YYYY-MM-DD"), 'Easting_m': x.Easting, 'Northing_m': x.Northing,'Elevation_m': x.Elevation_m } })}}

Thanks again for your help Tess

2 Likes
8

Awesome! Glad to hear :grinning:

1 Like