Ssms: How to Import (Copy/Paste) Data from Excel

SSMS: How to import (Copy/Paste) data from excel

Please try following:

-- NB! Script do NOT write any ifo to any table
-- to perform insert please copy/paste script output to the @tbl variable and execute

declare @tbl_name   nvarchar(128)   = '#insertTable';     -- Destination Table Name + [(column names)]
declare @dlm        nvarchar(128)   = char(9);  -- Delimiter: HT = char(9) = HorizontalTab
declare @tbl        nvarchar(max)   =               -- Paste Table Values here (from excel for instance)
N'NULL    490366  NULL    NULL
NULL    490400  NULL    NULL
NULL    490402  NULL    NULL
483061  490404  10  abc1
NULL    490406  NULL    NULL
9766167 490408  3   abc2'
;

select N'insert into '
        + @tbl_name             -- table name
        + ' VALUES(' 
        + replace               -- replace 'NULL' by NULL
          ( '''' 
             + replace          -- surround values by quotes 'value1','value2'...
               (    value
               ,    @dlm        -- Delimiter: HT = char(9) = HorizontalTab
               ,    ''','''
               ) 
             + ''''
          , '''NULL'''  
          , 'NULL'      
          )                                              
        + ');' as insertquery
--into #t 
from fn_split_string            -- insert Line per Row into table
     (  replace                 -- replace CR + LF by LF  (CR - Carriage Return, LF - Line Feed)
        (   @tbl                -- Paste Table Values here (from excel for instance)
        , char(13)+char(10)     -- CR + LF
        , char(10)              -- LF
        )
     ,char(10)
     ) 
where len(value)>0              -- skip empty rows

Paste MS Excel data to SQL Server

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. (...)

Import Excel Spreadsheet Data to an EXISTING sql table?

Saudate, I ran across this looking for a different problem. You most definitely can use the Sql Server Import wizard to import data into a new table. Of course, you do not wish to leave that table in the database, so my suggesting is that you import into a new table, then script the data in query manager to insert into the existing table. You can add a line to drop the temp table created by the import wizard as the last step upon successful completion of the script.

I believe your original issue is in fact related to Sql Server 64 bit and is due to your having a 32 bit Excel and these drivers don't play well together. I did run into a very similar issue when first using 64 bit excel.

Copy- paste data from SQL to EXCEL

Try using Import-Export wizard of SSMS. Dump the data of the table/view into an Excel.

---

Related Topics