Paste MS Excel data to SQL Server

Question

I have a bunch of rows in Excel that I want to paste into a new table in MS SQL. Is there a simple way ?

this doesn't appear to be programming-related - are you trying to do this with VBA or something? — warren, Nov 25 '08 at 10:59
Nope, just large amounts of data arriving adhoc as excel or easily, 'excellable' — Bajji, Nov 25 '08 at 12:46
This http://dotnetstories.wordpress.com/2008/05/24/copy-paste-data-from-excel-to-sql-server-2005/ worked for me — Jaco Briers, Jul 01 '11 at 09:14

bzlm · Answer 1 · 2019-08-03T20:33:50.957

85

If you have SQL Server Management Studio, you can just Copy from Excel and Paste into the table in Management Studio, using your mouse. Just

Go to the table you want to paste into.
Select "Edit Top 200 Rows".
Right-click anywhere and select Paste.

Before you do this, you must match the columns between Excel and Management Studio. Also, you must place any non-editable columns last (right-most) using the Table Designer in Management Studio.

The whole procedure takes seconds (to set-up and start - not necessarily to execute) and doesn't require any SQL statements.

Regarding empty database tables and SSMS v18.1+.

edited Aug 03 '19 at 20:33

answered Oct 13 '09 at 12:54

bzlm

9,626
6
65
92

4

+1, but you don't have to select select "Edit top 1000 rows"(where is that option in SSMS 2005?). You only need to open a table and paste it into an empty row. By the way, it simple and works if the columns names match each other, but it takes 45 minutes(and not seconds) to insert 40.000 rows with this method on my server(with an insert-trigger active). Hence it's simple but not flexible and fast. – Tim Schmelter Nov 07 '11 at 12:37
@TimSchmelter, sorry, I meant that it's very fast to set-up and start, compared to the other methods. The execution time could well be 45 minutes, yeah. I've updated the answer. – bzlm Feb 24 '12 at 13:54
8

See http://www.mssqltips.com/sqlservertip/1430/import-excel-data-into-sql-server-using-copy-and-paste/ for pictures – Lars Truijens May 21 '12 at 18:53
8

Just a note for anyone doing this in SSMS 2005, you need to right-click on the blank row tag on the left, rather than the cell itself, and select paste, otherwise it pastes the last value into the current row. – Oli Stockman Apr 21 '13 at 13:32
2

At least in 2012, you need to right-click specifically on one of the blank, grey "header" areas to the left of the first column on a blank row. If you right-click in a cell, it tries to copy to that one cell. If you right-click on the blank, grey "header" block at the upper-lefthand corner, just before any rows or columns, it just doesn't provide you an option to paste. Although I'm not technically sure what'll happen if you click on a header area associated with a column, but not with a row. – Panzercrisis Dec 11 '14 at 14:36
4

If the first column is an identity column then make sure your first column in Excel is an empty column, and include that too when you copy from Excel. (You don't have to move it to the end or so) And as others have pointed out, in Management Studio make sure you select the "new row" by clicking its selector. – Magnus May 20 '16 at 12:58
This worked fine with about 1000 rows. Very quick and efficient. The only problem I had was with dates. Without any explanation, datetime columns such as '01/10/2016 00:00:00' were interpreted as NULLs. I simply removed the 00:00:00 part from the dates in Excel to fix this. Also I would recommend testing with only a few rows at first. Otherwise, if there are any problems in your data, you will find yourself hitting Esc hundreds of times. This should be the accepted answer. Before I always constructed SQL insert statements as per the first answer, which is messier and more time consuming. – DAB Oct 11 '18 at 08:48
@Magnus that was what I needed when I searched for this question. You should edit the answer to include this information. – undrline - Reinstate Monica Oct 22 '18 at 15:25
1

Doesn't quite work as described when the table is empty (at least in SSMS v18.1). When the table is empty (and only when empty it seems!), you have to highlight the blank row by *left* clicking the blank row header *before* right clicking + paste. Failure to select the row first seems to just paste a blank value into the first cell. Again, this is only when the table is empty. – AaronHolland Jul 22 '19 at 01:11
1

Watch out for tab characters in the data. They caused Management Studio to emit errors about column validation failures, such as NULLs that weren't, at some distance from the data in question. Presumably it threw out the column alignment. – David Pierson Jul 05 '22 at 08:11

score 40 · Accepted Answer · edited Jun 20 '20 at 09:12

40

I have used this technique successfully in the past:

Using Excel to generate Inserts for SQL Server

(...) Skip a column (or use it for notes) and then type something like the following formula in it:

="insert into tblyourtablename (yourkeyID_pk, intmine, strval) values ("&A4&", "&B4&", N'"&C4&"')"

Now you’ve got your insert statement for a table with your primary key (PK), an integer and a unicode string. (...)

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 25 '08 at 11:06

Galwegian

41,475
16
112
158

3

bzlm's answer is more appropriate for the question. This is a neat little discovery but most cases, bzlm's right click and copy past works! – ThinkCode Jan 23 '14 at 18:19
1

This is faster and simpler: http://www.mssqltips.com/sqlservertip/1430/import-excel-data-into-sql-server-using-copy-and-paste/ – Ricky Helgesson Dec 16 '14 at 10:05
2

Copy and paste didn't work for me, nor did the `OPENROWSET` command. The only way was Galwegian's answer! – Dan Rayson May 13 '15 at 15:37
2

For copy and paste to work you need to make sure you haven't clicked in one of the field cells in SQL Server Management Studio - click in one of the grey borders instead. – Rich Mar 21 '16 at 14:25

score 34 · Answer 3 · answered Nov 17 '17 at 01:54

Excel

In Excel, highlight and copy the data you want to paste into SQL.

SQL

Create the table with the desired column names and give you table a name. *Make sure Identity Specification is Yes, so it will auto increment your Identity column.
Find your table, and right click on it and choose Edit Top 200 Rows from the dialog-box.
Right click on the empty row with the * sign and select paste from the dialog-box

score 22 · Answer 4 · edited Feb 12 '21 at 01:14

For future references:

You can copy-paste data from en excel-sheet to an SQL-table by doing so:

Select the data in Excel and press Ctrl + C
In SQL Server Management Studio right click the table and choose Edit Top 200 Rows
Scroll to the bottom and select the entire empty row by clicking on the row header
Paste the data by pressing Ctrl + V

Note: Often tables have a first column which is an ID-column with an auto generated/incremented ID. When you paste your data it will start inserting the leftmost selected column in Excel into the leftmost column in SSMS thus inserting data into the ID-column. To avoid that keep an empty column at the leftmost part of your selection in order to skip that column in SSMS. That will result in SSMS inserting the default data which is the auto generated ID. Furthermore you can skip other columns by having empty columns at the same ordinal positions in the Excel sheet selection as those columns to be skipped. That will make SSMS insert the default value (or NULL where no default value is specified).

It's only letting me do this one row at a time. The minute I select more than one row in Excel, doing a paste in SSMS does exactly... nothing. I've tried Ctrl+V, I've tried right-click+paste, I've tried putting it in Notepad first and deleting the extra blank line from the end, I've tried putting in a blank column at the beginning (like you used to have to do in Enterprise Manager)... nothing works. — Martha, Jun 20 '14 at 01:38
Many answers forget to mention step 3 if you're adding rows to a table that already has data. — jcs, Sep 29 '20 at 19:29

score 4 · Answer 5 · edited Mar 15 '23 at 00:22

I have developed an Excel VBA Macro for cutting and pasting any selection from Excel into SQL Server, creating a new table. The macro is great for quick and dirty table creations up to a few thousand rows and multiple columns (It can theoretically manage up to 200 columns). The macro attempts to automatically detect header names and assign the most appropriate datatype to each column (it handles varchar columns upto 1000 chars).

Recommended Setup procedure:

Make sure Excel is enabled to run macros. (File->Options->Trust Center->Trust Center Settings->Macro Settings->Enable all macros..)
Copy the VBA code below to the module associated with your personal workbook (So that the Macro will be available for all worksheets)
Assign an appropriate keystroke to the macro ( I have assigned Ctrl Shift X)
Save your personal workbook

Use of Macro

Select the cells in Excel (including column headers if they exist) to be transferred to SQL
Press the assigned keyword combination that you have assigned to run the macro
Follow the prompts. (Default table name is ##Table)
Paste the clipboard contents into a SSMS window and run the generated SQL code. BriFri 238

VBA Code:

'------------------------------------------------------------------------------

Public Sub TransferToSQL()
' TransferToSQL Macro
'
' This macro prepares data for pasting into SQL Server and posts it to the clipboard for inserting into SSMS.
' It attempts to automatically detect header rows and does a basic analysis of the first 15 rows to determine
' the most appropriate datatype to use handling text entries up to 1000 chars.
'
'
' Use of Macro
'
' 1. Select the cells in Excel (including column headers if they exist) to be transferred to SQL
' 2. Press the assigned keyword combination that you have assigned to run the macro
' 3. Follow the prompts. (Default table name is ##Table)
' 4. Paste the clipboard contents into a SSMS window and run the generated SQL code.
'
' Max Number of Columns: 200
'
' Created by BriFri238 - https://stackoverflow.com/a/26219806/1898524
'
' Keyboard Shortcut: Ctrl+Shift+X
'
' ver   Date    Reason
' ===   ====    ======
' 1.7   07/2018 Added prompt for "Append to existing table?" so SELECT INTO is not used.
'               Added strInsertHeader to hold the list of columns which are used with the INSERT (field1, field2, ...)
'               to support IDENTITY_INSERT.
' 1.6   06/2012 Fixed bug that prevented auto exit if no selection made / auto exit if blank Tablename entered or 'cancel' button pressed
' 1.5   02/2012 made use of function fn_ColLetter to retrieve the Column Letter for a specified column
' 1.4   02/2012 Replaces any Tabs in text data to spaces to prevent Double quotes being output in final results
' 1.3   02/2012 Place the 'drop table if already exists' code into a separate batch to prevent errors when inserting new table with same name but different shape and > 100 rows
' 1.2   01/2012 If null dates encountered code to cast it as Null rather than '00-Jan-1900'
' 1.1   10/2011 Code to drop the table if already exists
' 1.0   03/2011 Created

    Dim intLastRow       As Long
    Dim intlastColumn    As Integer
    Dim intRow           As Long
    Dim intDataStartRow  As Long
    Dim intColumn        As Integer
    Dim strKeyWord       As String
    Dim intPos           As Integer
    Dim strDataTypeLevel(4) As String
    Dim strColumnHeader(200) As String
    Dim strDataType(200) As String
    Dim intRowCheck      As Integer
    Dim strFormula(20)   As String
    Dim intHasHeaderRow  As Integer
    Dim strCellRef       As String
    Dim intFormulaCount  As Integer
    Dim strSQLTableName  As String
    Dim strSQLTableName_Encap As String
    Dim intdataTypelevel As Integer
    Const strConstHeaderKeyword As String = "ID,URN,name,Title,Job,Company,Contact,Address,Post,Town,Email,Tele,phone,Area,Region,Business,Total,Month,Week,Year,"
    Const intConstMaxBatchSize As Integer = 100
    Const intConstNumberRowsToAnalyse As Integer = 100
    Dim strInsertHeader  As String
    Dim i                As Integer
    Dim bolAppendToSQLTable As Boolean    ' True if the table exists and you want to append to it.

    On Error GoTo ErrorHandler

    intHasHeaderRow = vbNo

    strDataTypeLevel(1) = "VARCHAR(1000)"
    strDataTypeLevel(2) = "FLOAT"
    strDataTypeLevel(3) = "INTEGER"
    strDataTypeLevel(4) = "DATETIME"

    ' Use current selection and paste to new temp worksheet

    Selection.Copy
    Workbooks.Add       ' add temp 'Working' Workbook
    ' Paste "Values Only" back into new temp workbook
    Range("A3").Select  ' Goto 3rd Row
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False    ' Copy Format of Selection
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False  ' Copy Values of Selection
    ActiveCell.SpecialCells(xlLastCell).Select  ' Goto last cell
    intLastRow = ActiveCell.row
    intlastColumn = ActiveCell.Column


    ' Check to make sure that there are cells which are selected
    If intLastRow = 3 And intlastColumn = 1 Then
        Application.DisplayAlerts = False       ' Temporarily switch off Display Alerts
        ActiveWindow.Close                      ' Delete newly created worksheet
        Application.DisplayAlerts = True        ' Switch display alerts back on
        MsgBox "*** Please Make selection before running macro - Terminating ***", vbOKOnly, "Transfer Data to SQL Server"
        Exit Sub
    End If

    ' Prompt user for Name of SQL Server table
    strSQLTableName = InputBox("SQL Server Table Name?", "Transfer Excel Data To SQL", "##Table")

    ' if blank table name entered or 'Cancel' selected then exit
    If strSQLTableName = "" Then
        Application.DisplayAlerts = False       ' Temporarily switch off Display Alerts
        ActiveWindow.Close                      ' Delete newly created worksheet
        Application.DisplayAlerts = True        ' Switch display alerts back on
        Exit Sub
    End If

    Application.ScreenUpdating = False

    ' encapsulate tablename with square brackets if user has not already done so
    strSQLTableName_Encap = Replace(Replace(Replace("[" & Replace(strSQLTableName, ".", "].[") & "]", "[]", ""), "[[", "["), "]]", "]")

    ' Try to determine if the First Row is a header row or contains data and if a header load names of Columns
    Range("A3").Select
    For intColumn = 1 To intlastColumn
        ' first check to see if the first row contains any pure numbers or pure dates
        If IsNumeric(ActiveCell.Value) Or IsDate(ActiveCell.Value) Then
            intHasHeaderRow = vbNo
            intDataStartRow = 3
            Exit For
        Else
            strColumnHeader(intColumn) = ActiveCell.Value
            ActiveCell.Offset(1, 0).Range("A1").Select  ' go to the row below
            If IsNumeric(ActiveCell.Value) Or IsDate(ActiveCell.Value) Then
                intHasHeaderRow = vbYes
                intDataStartRow = 4
            End If
            ActiveCell.Offset(-1, 0).Range("A1").Select  ' go back up to the first row
            If intHasHeaderRow = vbNo Then     ' if still not determined if header exists: Look for header using keywords
                intPos = 1
                While intPos < Len(strConstHeaderKeyword) And intHasHeaderRow = vbNo
                    strKeyWord = Mid$(strConstHeaderKeyword, intPos, InStr(intPos, strConstHeaderKeyword, ",") - intPos)
                    If InStr(1, ActiveCell.Value, strKeyWord) > 0 Then
                        intHasHeaderRow = vbYes
                        intDataStartRow = 4
                    End If
                    intPos = InStr(intPos, strConstHeaderKeyword, ",") + 1
                Wend
            End If
        End If
        ActiveCell.Offset(0, 1).Range("A1").Select  ' Goto next column
    Next intColumn

    ' If auto header row detection has failed ask the user to manually select
    If intHasHeaderRow = vbNo Then
        intHasHeaderRow = MsgBox("Does current selection have a header row?", vbYesNo + vbQuestion, "Auto header row detection failure")
        If intHasHeaderRow = vbYes Then
            intDataStartRow = 4
        Else
            intDataStartRow = 3
        End If

    End If


    ' *** Determine the Data Type of each Column ***

    ' Go thru each Column to find Data types
    If intLastRow < intConstNumberRowsToAnalyse Then              ' Check the first intConstNumberRowsToAnalyse  rows or to end of selection whichever is less
        intRowCheck = intLastRow
    Else
        intRowCheck = intConstNumberRowsToAnalyse
    End If

    For intColumn = 1 To intlastColumn
        intdataTypelevel = 5

        For intRow = intDataStartRow To intRowCheck
            Application.GoTo Reference:="R" & CStr(intRow) & "C" & CStr(intColumn)
            If ActiveCell.Value = "" Then   ' ignore blank (null) values
            ElseIf IsDate(ActiveCell.Value) = True And Len(ActiveCell.Value) >= 8 Then
                If intdataTypelevel > 4 Then intdataTypelevel = 4
            ElseIf IsNumeric(ActiveCell.Value) = True And InStr(1, CStr(ActiveCell.Value), ".") = 0 And (Left(CStr(ActiveCell.Value), 1) <> "0" Or ActiveCell.Value = "0") And Len(ActiveCell.Value) < 10 Then
                If intdataTypelevel > 3 Then intdataTypelevel = 3
            ElseIf IsNumeric(ActiveCell.Value) = True And InStr(1, CStr(ActiveCell.Value), ".") >= 1 Then
                If intdataTypelevel > 2 Then intdataTypelevel = 2
            Else
                intdataTypelevel = 1
                Exit For
            End If
        Next intRow
        If intdataTypelevel = 5 Then intdataTypelevel = 1
        strDataType(intColumn) = strDataTypeLevel(intdataTypelevel)

        If intHasHeaderRow = vbYes Then
            ' Build a string of the column headings to be used by the INSERT (field1, field2, ...)
            strInsertHeader = strInsertHeader & ", [" & strColumnHeader(intColumn) & "]"
        End If
    Next intColumn
    If intHasHeaderRow = vbYes Then
        strInsertHeader = Mid(strInsertHeader, 3)    ' Remove prefix

        ' Ask user if they want to Append to an existing table or DROP and CREATE a new table.  BS 7/19/2018
        bolAppendToSQLTable = MsgBox("Do you want to APPEND to this table?", vbYesNo + vbQuestion, "Append to " & strSQLTableName) = vbYes
    End If

    ' *** Build up the SQL
    intFormulaCount = 1
    If intHasHeaderRow = vbYes Then     ' *** Header Row ***
        Application.GoTo Reference:="R4" & "C" & CStr(intlastColumn + 1)    ' Goto next column in first data row of selection
        strFormula(intFormulaCount) = "= ""SELECT "
        For intColumn = 1 To intlastColumn
            If strDataType(intColumn) = "DATETIME" Then         ' Code to take Excel Dates back to text
                strCellRef = "Text(" & fn_ColLetter(intColumn) & "4,""dd-mmm-yyyy hh:mm:ss"")"
            ElseIf strDataType(intColumn) = "VARCHAR(1000)" Then
                strCellRef = "SUBSTITUTE(" & fn_ColLetter(intColumn) & "4,""'"",""''"")"    ' Convert any single ' to double ''
            Else
                strCellRef = fn_ColLetter(intColumn) & "4"
            End If

            strFormula(intFormulaCount) = strFormula(intFormulaCount) & "CAST('""& " & strCellRef & " & ""' AS " & strDataType(intColumn) & ") AS [" & strColumnHeader(intColumn) & "]"
            If intColumn < intlastColumn Then
                strFormula(intFormulaCount) = strFormula(intFormulaCount) + ", "
            Else
                strFormula(intFormulaCount) = strFormula(intFormulaCount) + " UNION ALL """
            End If
            ' since each cell can only hold a maximum no. of chars if Formula string gets too big continue formula in adjacent cell
            If Len(strFormula(intFormulaCount)) > 700 And intColumn < intlastColumn Then
                strFormula(intFormulaCount) = strFormula(intFormulaCount) + """"
                intFormulaCount = intFormulaCount + 1
                strFormula(intFormulaCount) = "= """
            End If
        Next intColumn

        ' Assign the formula to the cell(s) just right of the selection
        For intColumn = 1 To intFormulaCount
            ActiveCell.Value = strFormula(intColumn)
            If intColumn < intFormulaCount Then ActiveCell.Offset(0, 1).Range("A1").Select  ' Goto next column
        Next intColumn

        ' Auto Fill the formula for the full length of the selection
        ActiveCell.Offset(0, -intFormulaCount + 1).Range("A1:" & fn_ColLetter(intFormulaCount) & "1").Select
        If intLastRow > 4 Then Selection.AutoFill Destination:=Range(fn_ColLetter(intlastColumn + 1) & "4:" & fn_ColLetter(intlastColumn + intFormulaCount) & CStr(intLastRow)), Type:=xlFillDefault

        ' Go to start row of data selection to add 'Select into' code

        If bolAppendToSQLTable = True Then
            ' When appending to an existing table, use this syntax
            ActiveCell.Value = "INSERT " & strSQLTableName_Encap & " ( " & strInsertHeader & " )" & " SELECT * FROM (" & ActiveCell.Value
            ActiveCell.Offset(-1, 0).Range("A1").Select  ' go to the row above
            ActiveCell.Value = "SET IDENTITY_INSERT " & strSQLTableName_Encap & " ON;"
            ActiveCell.Offset(-1, 0).Range("A1").Select  ' go to the row above
            ActiveCell.Value = "--BEGIN TRANSACTION;   COMMIT; SET IDENTITY_INSERT " & strSQLTableName_Encap & " OFF;"
        Else
            ' If creating a new table, use this syntax
            ActiveCell.Value = "SELECT * INTO " & strSQLTableName_Encap & " FROM (" & ActiveCell.Value

            ' Go to cells above data to insert code for deleting old table with the same name in separate SQL batch
            ActiveCell.Offset(-1, 0).Range("A1").Select  ' go to the row above
            ActiveCell.Value = "GO"
            ActiveCell.Offset(-1, 0).Range("A1").Select  ' go to the row above
            If Left(strSQLTableName, 1) = "#" Then      ' temp table
                ActiveCell.Value = "IF OBJECT_ID('tempdb.." & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
            Else
                ActiveCell.Value = "IF OBJECT_ID('" & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
            End If
        End If
        ' For Big selections (i.e. several 100 or 1000 rows) SQL Server takes a very long time to do a multiple union - Split up the table creation into many inserts
        intRow = intConstMaxBatchSize + 4   ' add 4 to make sure 1st batch = Max Batch Size
        While intRow < intLastRow
            Application.GoTo Reference:="R" & CStr(intRow - 1) & "C" & CStr(intlastColumn + intFormulaCount)  ' Goto Row before intRow and the last column in formula selection
            ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a")    ' Remove last 'UNION ALL'

            Application.GoTo Reference:="R" & CStr(intRow) & "C" & CStr(intlastColumn + 1)    ' Goto intRow and the first column in formula selection

            ' BS 7/19/2018:  Updated to include the list of fields so that IDENTITY_INSERT can be used.
            ActiveCell.Value = "INSERT " & strSQLTableName_Encap & " ( " & strInsertHeader & " )" & " SELECT * FROM (" & ActiveCell.Value
            ' ActiveCell.Value = "INSERT " & strSQLTableName_Encap & " SELECT * FROM (" & ActiveCell.Value

            intRow = intRow + intConstMaxBatchSize   ' increment intRow by intConstMaxBatchSize
        Wend

        ' Delete the last 'UNION AlL' replacing it with brackets to mark the end of the last insert
        Application.GoTo Reference:="R" & CStr(intLastRow) & "C" & CStr(intlastColumn + intFormulaCount)
        ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a")

        ' Select all the formula cells
        ActiveCell.Offset(-intLastRow + 2, 1 - intFormulaCount).Range("A1:" & fn_ColLetter(intFormulaCount + 1) & CStr(intLastRow - 1)).Select

    Else    ' *** No Header Row ***
        Application.GoTo Reference:="R3" & "C" & CStr(intlastColumn + 1)    ' Goto next column in first data row of selection
        strFormula(intFormulaCount) = "= ""SELECT "

        For intColumn = 1 To intlastColumn
            If strDataType(intColumn) = "DATETIME" Then
                strCellRef = "Text(" & fn_ColLetter(intColumn) & "3,""dd-mmm-yyyy hh:mm:ss"")"   ' Format Excel dates into a text Date format that SQL will pick up
            ElseIf strDataType(intColumn) = "VARCHAR(1000)" Then
                strCellRef = "SUBSTITUTE(" & fn_ColLetter(intColumn) & "3,""'"",""''"")"         ' Change all single ' to double ''
            Else
                strCellRef = fn_ColLetter(intColumn) & "3"
            End If

            ' Since no column headers: Name each column "Column001",Column002"..
            strFormula(intFormulaCount) = strFormula(intFormulaCount) & "CAST('""& " & strCellRef & " & ""' AS " & strDataType(intColumn) & ") AS [Column" & CStr(intColumn) & "]"
            If intColumn < intlastColumn Then
                strFormula(intFormulaCount) = strFormula(intFormulaCount) + ", "
            Else
                strFormula(intFormulaCount) = strFormula(intFormulaCount) + " UNION ALL """
            End If

            ' since each cell can only hold a maximum no. of chars if Formula string gets too big continue formula in adjacent cell
            If Len(strFormula(intFormulaCount)) > 700 And intColumn < intlastColumn Then
                strFormula(intFormulaCount) = strFormula(intFormulaCount) + """"
                intFormulaCount = intFormulaCount + 1
                strFormula(intFormulaCount) = "= """
            End If
        Next intColumn

        ' Assign the formula to the cell(s) just right of the selection
        For intColumn = 1 To intFormulaCount
            ActiveCell.Value = strFormula(intColumn)
            If intColumn < intFormulaCount Then ActiveCell.Offset(0, 1).Range("A1").Select  ' Goto next column
        Next intColumn

        ' Auto Fill the formula for the full length of the selection
        ActiveCell.Offset(0, -intFormulaCount + 1).Range("A1:" & fn_ColLetter(intFormulaCount) & "1").Select
        If intLastRow > 4 Then Selection.AutoFill Destination:=Range(fn_ColLetter(intlastColumn + 1) & "3:" & fn_ColLetter(intlastColumn + intFormulaCount) & CStr(intLastRow)), Type:=xlFillDefault

        ' Go to start row of data selection to add 'Select into' code
        ActiveCell.Value = "SELECT * INTO " & strSQLTableName_Encap & " FROM (" & ActiveCell.Value

        ' Go to cells above data to insert code for deleting old table with the same name in separate SQL batch
        ActiveCell.Offset(-1, 0).Range("A1").Select  ' go to the row above
        ActiveCell.Value = "GO"
        ActiveCell.Offset(-1, 0).Range("A1").Select  ' go to the row above
        If Left(strSQLTableName, 1) = "#" Then      ' temp table
            ActiveCell.Value = "IF OBJECT_ID('tempdb.." & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
        Else
            ActiveCell.Value = "IF OBJECT_ID('" & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
        End If

        ' For Big selections (i.e. serveral 100 or 1000 rows) SQL Server takes a very long time to do a multiple union - Split up the table creation into many inserts
        intRow = intConstMaxBatchSize + 3        ' add 3 to make sure 1st batch = Max Batch Size
        While intRow < intLastRow
            Application.GoTo Reference:="R" & CStr(intRow - 1) & "C" & CStr(intlastColumn + intFormulaCount)  ' Goto Row before intRow and the last column in formula selection
            ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a")    ' Remove last 'UNION ALL'

            Application.GoTo Reference:="R" & CStr(intRow) & "C" & CStr(intlastColumn + 1)    ' Goto intRow and the first column in formula selection
            ActiveCell.Value = "INSERT " & strSQLTableName_Encap & " SELECT * FROM (" & ActiveCell.Value
            intRow = intRow + intConstMaxBatchSize   ' increment intRow by intConstMaxBatchSize
        Wend

        ' Delete the last 'UNION AlL'
        Application.GoTo Reference:="R" & CStr(intLastRow) & "C" & CStr(intlastColumn + intFormulaCount)
        ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a")

        ' Select all the formula cells
        ActiveCell.Offset(-intLastRow + 1, 1 - intFormulaCount).Range("A1:" & fn_ColLetter(intFormulaCount + 1) & CStr(intLastRow)).Select
    End If


    ' Final Selection to clipboard and Cleaning of data
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False              ' Repaste "Values Only" back into cells
    Selection.Replace What:="CAST('' AS", Replacement:="CAST(NULL AS", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False  ' convert all blank cells to NULL
    Selection.Replace What:="'00-Jan-1900 00:00:00'", Replacement:="NULL", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False  ' convert all blank Date cells to NULL
    Selection.Replace What:="'NULL'", Replacement:="NULL", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False  ' convert all 'NULL' cells to NULL
    Selection.Replace What:=vbTab, Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False        ' Replace all Tabs in cells to Space to prevent Double Quotes occuring in the final paste text
    Selection.Copy

    Application.GoTo Reference:="R1" & "C" & CStr(intlastColumn + 1), Scroll:=True    ' Goto next column in first data row of selection

    Application.ScreenUpdating = True

    If MsgBox("SQL Code has been added to clipboard - Please Paste into SSMS window." _
              & vbCrLf & vbCrLf & "Do you want to close this temporary sheet?", vbYesNo + vbQuestion, "Transfer to SQL") = vbYes Then

        Application.DisplayAlerts = False       ' Temporarily switch off Display Alerts
        ActiveWindow.Close                      ' Delete newly created worksheet
        Application.DisplayAlerts = True        ' Switch display alerts back on
    End If

Exit_Sub:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:
    MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure TransferToSQL of basMisc"
    GoTo Exit_Sub
    Resume Next
    Resume

End Sub





Function fn_ColLetter(Col As Integer) As String

Dim strColLetter As String

If Col > 26 Then
    ' double letter columns
    strColLetter = Chr(Int((Col - 1) / 26) + 64) & _
        Chr(((Col - 1) Mod 26) + 65)
Else
    ' single letter columns
    strColLetter = Chr(Col + 64)
End If
fn_ColLetter = strColLetter
End Function

This is the type of solution I was looking for. This generates SQL Insert statements that execute much faster than pasting into the "Edit Top 200 Rows" grid. This functions assumes you are creating a new table. I'd like it to INSERT by explicitly naming the columns, which is necessary when using `SET IDENTITY_INSERT [tablename] ON;` to an existing table. If you have an update of this, please share or PM me. — Ben, Jul 19 '18 at 14:14
@Ben did you ever figure out how to insert by naming the columns rather than creating a new table? — Aidan Hakimian, Mar 09 '23 at 20:50
@AidanHakimian today I posted updated code as v1.7 and it now supports appending to an existing table. My edits won't be visible until the code is approved by a moderator. — Ben, Mar 10 '23 at 23:01

score 3 · Answer 6 · answered Nov 25 '08 at 10:57

The simplest way is to create a computed column in XLS that would generate the syntax of the insert statement. Then copy these insert into a text file and then execute on the SQL. The other alternatives are to buy database connectivity add-on's for Excel and write VBA code to accomplish the same.

score 2 · Answer 7 · answered Nov 25 '08 at 10:59

I'd think some datbases can import data from CSV (comma separated values) files, wich you can export from exel. Or at least it's quite easy to use a csv parser (find one for your language, don't try to create one yourself - it's harder than it looks) to import it to the database.

I'm not familiar with MS SQL but it wouldn't suprise me if it does support it directly.

In any case I think the requrement must be that the structure in the Exel sheet and the database table is similar.

score 0 · Answer 8 · answered Nov 25 '08 at 10:58

0

If the interface works the way it did last I used it, you can select the region in Excel, copy it, open SQL Server and paste the data into the table as you would with Access.

Or you could setup an ODBC link between Excel and SQL Server.

answered Nov 25 '08 at 10:58

warren

32,620
21
85
124

score 0 · Answer 9 · answered Nov 25 '08 at 11:14

0

why not just use export/import wizard in SSMS?

answered Nov 25 '08 at 11:14

Mladen Prajdic

15,457
2
43
51

score -2 · Answer 10 · answered Aug 06 '13 at 10:56

-2

Can't you use VBA code to do the copy from excel and paste into SSMS operations?

answered Aug 06 '13 at 10:56

Daniel

7

This is not an answer and it merely a comment. Answers should be answers not another question. – ashveli Nov 28 '19 at 09:17

Paste MS Excel data to SQL Server

10 Answers10