SQL Generator

One helpful thing is making SQL data with the CSV Template Merge tool. Some may call this SQL generation or data import depending on how they get the data.

Introduction

It is best to gather all the information you need and put it into a CSV file. Make sure to include the column names as the first row. For example, here's a simplified user listing.

name,dob,active
John,2000-01-01,true
Jane,2001-01-12,false
Bob,2002-01-19,true

You can customize the template and I want to show some options for each column.

insert into users (name, dob, active)
values ('${name}', '${dob}', ${active === 'true' ? 1 : 0});

Is the standard I like to use that includes some simple replacement on active. It would show up as

insert into users (name, dob, active)
values ('John', '2000-01-01', 1);

insert into users (name, dob, active)
values ('Jane', '2001-01-12', 0);

insert into users (name, dob, active)
values ('Bob', '2002-01-19', 1);

Ternary results

Now let's break things down a little more and show what each item would return. I'm just using 2 records here because there are many transformations done.

name,dob,active
John,2000-01-01,true
Jane,2001-01-12,false

Replacement based on a value

${active === 'true' ? 1 : 0}

This results in

1

0

Each row returns just a single value, and it is based on a string of 'true' or not.

Transforming to uppercase

name,dob,active
John,2000-01-01,true
Jane,2001-01-12,false

${name.toUpperCase()}

This results in

JOHN

JANE

It's just using a built in JavaScript function to do the transformations. You can do a lot more with JavaScript than this.

Date Changes

name,dob,active
John,2000-01-01,true
Jane,2001-01-12,false

Dates use the JavaScript rules and can be a little complex to manipulate. Note that the addition of T00:00:00Z sets the time and the timezone to UTC. Formatting dates based on locales. Timezone of UTC has to be set so it doesn't use the timezone on your computer.

${new Date(Date.parse(dob + 'T00:00:00Z')).toUTCString()}
${new Date(Date.parse(dob + 'T00:00:00Z')).toLocaleDateString('en-US', { timeZone: 'UTC'})}
${new Date(Date.parse(dob + 'T00:00:00Z')).toLocaleDateString('en-GB', { timeZone: 'UTC'})}

This results in

Sat, 01 Jan 2000 00:00:00 GMT
1/1/2000
01/01/2000

Fri, 12 Jan 2001 00:00:00 GMT
1/12/2001
12/01/2001

Note the different order of months and days depending on which locale is used.

Conditionally including the data with a ternary operation

name,dob,active
John,2000-01-01,true
Jane,2001-01-12,false

${ active == 'true' ?
`insert into users (name, dob, active)
values ('${name}', '${dob}', ${active === 'true' ? 1 : 0});`:`-- Skipped row`
}

This results in

insert into users (name, dob, active)
values ('John', '2000-01-01', 1);

-- Skipped row

Argument Mapping

If you needed to do some advanced items against all columns, start with something like this.

${ Array.from(arguments).map((x, i) => `${x} is in index ${i}`).join('\n') }

Which would produce

John is in index 0
2000-01-01 is in index 1
true is in index 2

Jane is in index 0
2001-01-12 is in index 1
false is in index 2

Thank you.
Ben Maddox