One of the items I get asked a lot to do is to push data – usually in an Excel spreadsheet – into SQL Server. Usually, there are less than 50 rows of data. Heck, there are usually less than 5 columns as well. Today – and in a few succeeding blog posts – we’ll go over several different ways that you too can push the data into SQL Server.
First, we’ll look at the data itself. You can grab the spreadsheet that I’ll be using here.
If you review the data, there are over 1K rows of 5 columns of data about people. There’s nothing special about these people. As far as I know, they’re not even real people. The email addresses could be somebody’s email address, but it’s fairly doubtful.
Possibly the easiest way to get the data into SQL Server is to use the power of Excel.
First, you’ll create a table that will hold all of the data for these persons. There are some tricks for this, but today, I’ll give you the basic table structure for SQL Server that I’ll be using.
CREATE TABLE dbo.person
( ID integer NOT NULL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Email varchar(100) NOT NULL,
Gender varchar(50) NOT NULL)Once the table has been created, you can then go onto Step 2: Working in Excel.
In F2, copy the following formula.
=CONCATENATE("INSERT INTO dbo.Person SELECT ",A2,", '", B2, "','",C2,"','",D2,"','",E2,"'")This will create the INSERT statements that you’ll need to push your own data into SQL Server.
Copy the formulas as needed to the other rows.
Then copy all of the INSERT statements that were created to SQL Server and voila! All of the data that you’ll need is now in SQL Server!
In our next blog post, we’ll go thru each part of this and why it worked. Until then…