NimbleText

Manipulate text and data with light-weight patterns

How to Generate Insert Statements

Assuming you have some data already, perhaps as raw text, generating a SQL insert statement should be trivial, but it can end up being a lot of grunt work.

This is exactly the kind of situation where NimbleText shines. It feels great ridding yourself of this kind of grunt work, just watch!

And if you get lost for a moment don't worry. Just drink it in, let it wash over you. It'll click soon enough.

Let's say we've got this data:

First Name, Last Name, Company
Walter, Greer, Aratos Pty Ltd
Conrad, Dunbar, Lupus Tech
Mandy, Miron, Horse Lane Pottery

We want to generate a query, like this, but for every row:

Insert into Contacts (FirstName, LastName, Company) 
Values ('Walter', 'Greer', 'Aratos Pty Ltd')
animation showing use of NimbleText for generating SQL Insert statements

In NimbleText, we paste our data into the first textbox (the data textbox). Then we create a pattern like this:

try it → 
Insert into Contacts (FirstName, LastName, Company) 
Values ('$0', '$1', '$2')

This will produce the following output:

Insert into Contacts (FirstName, LastName, Company) 
Values ('First Name', 'Last Name', 'Company')
Insert into Contacts (FirstName, LastName, Company) 
Values ('Walter', 'Greer', 'Aratos Pty Ltd')
Insert into Contacts (FirstName, LastName, Company) 
Values ('Conrad', 'Dunbar', 'Lupus Tech')
Insert into Contacts (FirstName, LastName, Company) 
Values ('Mandy', 'Miron', 'Horse Lane Pottery')

NimbleText is free, by the way. You can use it online, or even better, you can download it and use it even when you're offline.

You can copy this out, being careful to avoid the first row (it contains the headers) and now you have your insert statements. Job done.

But we can do a lot better than that!

I'll show you how to skip the header row, and I'll give you a better query too.

In SQL Server 2008 onward there is a more compact syntax for inserts: we can include multiple rows in a single statement, like this: 

Insert into Contacts (FirstName, LastName, Company) 
Values
 ('Walter', 'Greer', 'Aratos Pty Ltd'),
 ('Conrad', 'Dunbar', 'Lupus Tech'),
 ('Mandy', 'Miron', 'Horse Lane Pottery')

We refine our pattern to only say 'insert' once, and then to wrap our data in braces and quotes, like this:

try it → 
$once
Insert into Contacts (FirstName, LastName, Company)
Values
$each+
 ('$0', '$1', '$2'),

The '$once' token means 'Only repeat the following bit once (rather than repeating it for each row.)

So the first part of our query is only output once:

$once

    Insert into Contacts (FirstName, LastName, Company) Values
$each+
 ('$0', '$1', '$2'),

The '$each' token means 'Repeat the following bit for each row of our raw data. More specifically, the '$each+' (each PLUS) token means "skip the header row, and then repeat this pattern for each data row.

So given that data and that pattern we get the following result.

Insert into Contacts (FirstName, LastName, Company) 
Values
 ('Walter', 'Greer', 'Aratos Pty Ltd'),
 ('Conrad', 'Dunbar', 'Lupus Tech'),
 ('Mandy', 'Miron', 'Horse Lane Pottery'),

Okay -- there's still one more refinement. The damn trailing comma.

Unfortunately, our SQL is not completely valid as long as it has that comma at the end. To get rid of it, we need to use a common little NimbleText trick.

Instead of leaving that trailing comma in our pattern, we need to replace it with a snippet that means 'only output a comma if we're not on the last row of data', here's the snippet:

<% if ($rownumOne != $numrows) {','} %>

(Note that this is such a common snippet that it's available in the 'snippets' menu in NimbleText.

Putting it all together!

try it → 
$ONCE
Insert into Contacts (FirstName, LastName, Company) 
Values
$EACH+
 ('$0', '$1', '$2')<% if ($rownumOne != $numrows) {','} %>

And there we have it, a custom insert generator, and all the wisdom you need to tailor your own custom insert generators.

Of course this is just one type of SQL query that NimbleText will help you generate.

We've also written guides on many other ad-hoc queries you can generate:

Further help

You can also get general help, help on all the symbols and keywords, or on the built-in functions, filtering with a where clause, help with the powerful command-line automation, or applying custom formats to your dates and times.

You need to purchase a license to unlock all the features in NimbleText.

If you haven't downloaded NimbleText yet, then for added power, privacy and versatility I sincerely think you should download it now.

Download NimbleText