More Fun Inserting Data using Values

Last time, we went over one of my favorite ways to push data into a database table. If you’ve forgotten – or somehow missed it – please sit back and enjoy the fun of Inserting the data. This time, we’ll do it in a slightly different way that can be useful as well.

First, we’ll take our handy dandy spreadsheet found here. If you remember last time, you’ll know that we used the CONCATENATE function of Excel to push the data into our database table. Well, this time I don’t feel like typing as much, so we’ll do a slightly different formula within the CONCATENATE.

This time, I’m only going to use the following in my CONCATENATE function:

=CONCATENATE("(",A2, ", '", B2, "','", SUBSTITUTE(C2,"'","''"), "','", D2, "','", E2, "'),")

Now, I know what you’re thinking – “Why did you make us type all of that stuff last time if we’re going to significantly lessen it this time!?!?!?!?!?!?!?!?!”

This time, you’ll see another function in the CONCATENATE function – SUBSTITUTE. We have to use this because we have someone with a last name of O’Hagan (and quite a few other O’ names). The SUBSTITUTE function changes the last name to O”Hagan so that SQL Server can insert it into the database with no problems.

Then you type into SSMS – or your SQL tool of choice, the following:

INSERT INTO dbo.Person VALUES

Copy/Paste all of the data that you just created in Excel into your SQL tool of choice so that it looks like this:

You will then delete the final comma from your data. Otherwise, SQL will think there is more data to come!

And now we run that!

So, we get this error only if there are more than 1000 rows of data to be inserted. If we’re over 1000 rows, then we have to do a tiny bit of finagling to get this to work.

1. Change the “INSERT INTO dbo.Person” to “INSERT INTO dbo.Person SELECT * FROM (VALUES”
2. After the final row, add the following “) AS sub (ID, FirstName, LastName, Email, Gender)”
   1. Yes, the Field Headings do have to change to match the number of columns you enter (not necessarily the name though)

Now we run our 1 INSERT statement and it works perfectly.

With that, we have another great way to get data into our relational databases. Until next time, when we’ll present yet another way!