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...
...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...
Choose "Flat file source" and then select the options you need
Preview of data should look like this
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
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):
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:
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.