More Fun Inserting Data with R

I know what you’re thinking at this point. You’re like “How many more ways can there be to insert data into SQL Server?”

The answer is actually as many ways as you can think of.

I tend to stay mostly within SQL Server, but I do branch out to PowerShell and the computer language we’ll discuss today – R – to do everything I need. With many of today’s newer languages, there are many different ways that a person can load or view data.

But I bring up today’s way of importing data for a multitude of reasons.

1. This way is fairly easy to follow
2. It uses 2 libraries – both I tend to use those frequently anyway.
3. You don’t have to create the SQL table beforehand as it will be done for you.

To follow along with today’s lesson, you’ll need to open RStudio (or whatever R application you’re used to).

• Load the 2 additional libraries that are needed:

library(RODBC)
library(readxl)

• Connect to your database:

sqlConnect <- odbcDriverConnect('driver={SQL Server Native Client 11.0};
                                    server=.;
                                    database=RTest;
                                    rows_at_time=1024;
                                    trusted_connection=yes')

Notice that I’m connecting to my local server here – denoted as “.”. Also, my database name – in this case, RTest – should be updated to fit your requirements. And, yes, the database does have to exist before connecting to it using this method.

• Load the Excel spreadsheet into a variable within R.

myFile <- read_excel("c:\\temp\\Mock Person Data.xlsx")

Notice the double slashes (“\\”) to denote the folders instead of the normal single slash that Microsoft uses (“\”)

• Finally, save the variable into a table on the SQL Server.

sqlSave(sqlConnect, myFile, "dbo.MockPersonData", rownames = FALSE, append = TRUE, verbose = TRUE) 

Remember the point that I made earlier about the table not having to be created beforehand? This is where the magic happens! This command creates the table – dbo.MockPersonData – with the column names that are within the Excel spreadsheet and it can give us lots of information while it does it! Turn verbose = FALSE if you’re inserting large files or you don’t want to see all of the information on your screen.

• And then you can view your data within SQL Server as needed!

Is it perfect? No, but it can save you a lot of work!

With that, I call an end to my series on the myriad of ways that you can import data into SQL Server. Until next time when we focus on something equally as important.