Knowledge is power. We love to share it.

News related to Mono products, services and latest developments in our community.

idrazic

Different ways of importing data into SQL Server

03/31/2012

Intro

In any project that uses an SQL Server database you will sooner or later need to import some data into it. Be it one kilobyte or one megabyte the task can be easy ( if the data can be inserted as is ) or hard ( if the data must be processed in a certain way). More often than not the file with data will be in a Microsoft Excel workbook format (.xls, .xlsx) or comma separated values (.csv) format. If it's not, the CSV format is easy enough to convert to.

For the sample data lets take some from the Northwind database...
sample-data
...and save it as Sample.CSV (by default, Excel will use ";" for the delimiter)

There are many approaches to handle the import using all the tools at hand.

Let's start with the easiest...

SQL Server Import and Export Wizard

Using Import Wizard is a great way to quickly import data into sql server with many different options and customizations.
To open it right click on a database and select > Task > Import Data...
wizard-preview
Choose "Flat file source" and then select the options you need
wizard-ffs
Preview of data should look like this
import-wizard

This aproach gives some advanced options like changing the column mapping, changing the "create table" script and append data to an existing table.
There is no automation, so you need to click and select your options every time you import a file. You also need to have the file in a readable location which could be a problem if you are in a hurry and the file is inaccessible through the network.

Using T-SQL capabilities

BULK INSERT

bulk

Now, this approach pretty much does everything a wizard does but in an automated fashion meaning you could just change the file name and be done with it.
File access and permissions could a be possible issue as with the Import Wizard method.

Hands-on approach

You can create the insert script(s) manually or programmatically.

Direct approach from Excel:

You can use CONCATENATE for the function
=CONCATENATE("INSERT INTO table VALUES(";A1;",";B1;")")

Or the more sophisticated approach using Excel VBA to generate the insert script.
I’ve changed the original sample just a bit for the VBA macro functionality (first field contains the table name):
vba

Full vba code:

   1:  ' Utility functions 
   2:  Private Function GetLastColumn() 
   3:      If WorksheetFunction.CountA(Cells) <= 0 Then 
   4:          Exit Function 
   5:      End If  
   6:      GetLastColumn = Cells.Find(What:="*", After:=[A1], _ 
   7:                              SearchOrder:=xlByColumns, _ 
   8:                              SearchDirection:=xlPrevious).Column 
   9:  End Function 
  10:  Private Function GetLastRow() 
  11:      If WorksheetFunction.CountA(Cells) <= 0 Then 
  12:          Exit Function 
  13:      End If  
  14:      GetLastRow = Cells.Find(What:="*", After:=[A1], _ 
  15:                              SearchOrder:=xlByRows, _ 
  16:                              SearchDirection:=xlPrevious).Row 
  17:  End Function 
  18:  Sub GenerateSQLInsert() 
  19:      Dim LastColumn As Integer 
  20:      Dim LastRow As Long 
  21:      Dim LastCell As Range 
  22:      Dim s, xval, outFile, table As String 
  23:      Dim i, j As Integer 
  24:      Dim startRow As Integer 
  25:      
  26:      startRow = 2 
  27:      ' output path 
  28:      outFile = "e:\Work\" 
  29:      
  30:      LastColumn = GetLastColumn() 
  31:      LastRow = GetLastRow() 
  32:      
  33:      Range(Cells(startRow, 1).Address, Cells(startRow, LastColumn).Address).Select 
  34:      
  35:      ' get the table name from the first row 
  36:      table = Cells(1, 1).Value 
  37:      
  38:      outFile = outFile & table & ".sql" 
  39:      
  40:      
  41:      f = FreeFile() 
  42:      Open outFile For Output As f 
  43:      
  44:      ' require that the second row contains header values 
  45:      s = "" 
  46:      For j = 1 To LastColumn 
  47:          s = s & "[" & Cells(startRow, j).Value & "]" 
  48:          If j < LastColumn Then 
  49:              s = s & ", " 
  50:          End If 
  51:      Next 
  52:      
  53:      Print #f, "INSERT INTO " & table 
  54:      Print #f, "    (" & s & ")" 
  55:      Print #f, "VALUES" 
  56:      
  57:      For i = 1 To LastRow - startRow 
  58:          
  59:          s = "( " 
  60:          
  61:          For j = 1 To LastColumn 
  62:              
  63:              xval = Cells(startRow + i, j).Value 
  64:              
  65:              ' throw in some custom actions like generating an id value 
  66:              If xval = "" Then 
  67:                  If Cells(startRow, j).Value = "Id" Then 
  68:                      xval = "NEWID()" 
  69:                  Else 
  70:                      xval = "NULL" 
  71:                  End If 
  72:              ' and generating "Title" lookup 
  73:              ElseIf Cells(startRow, j).Value = "Title" Then 
  74:                      xval = "(SELECT Id FROM Titles WHERE Name = '" & xval & "')" 
  75:              Else 
  76:                  xval = "'" & xval & "'" 
  77:              End If 
  78:              
  79:              ' Append 
  80:              s = s & xval 
  81:              
  82:              If j < LastColumn Then 
  83:                  s = s & ", " 
  84:              End If 
  85:          Next 
  86:          
  87:          If i < LastRow - startRow Then 
  88:              s = s & " ), " 
  89:          Else 
  90:              s = s & " ) " 
  91:          End If 
  92:          
  93:          Print #f, s 
  94:      Next 
  95:      
  96:      Close #f 
  97:      
  98:  End Sub

produced sql:
finalsql

Conclusion

Hands-on approach is pretty much our only option if we need to modify the imported data in a certain way (for example, the "Title" from the sample data must looked up from a different table) or you are required to create an sql file for deployment to another server.

Rated 1.86, 28 vote(s). 
By richard
We created a tool that will also Validate the data for you, both data type and integrity checks of the data.

You might be inerested :
http://leansoftware.net/en-us/productsdownloads/exceltodatabase.aspx