none
How to copy\paste multiple records of a field from Excel to SQL server table? RRS feed

  • Question

  • How to copy\paste multiple records of a field from Excel to SQL server table?

     

    In the screenshot below, I wanted to copy\paste the value of the “name” field to the “E2” field in SQL Server table. How this can be performed?

     

    Thank you

     

    Jamal


    jamal

    Thursday, July 4, 2019 9:27 PM

Answers

All replies

  • You can import data from the excel file to the table. Right click on the database and select Tasks and than Import Data ...

    A Fan of SSIS, SSRS and SSAS

    Thursday, July 4, 2019 9:31 PM
  • You can import data from the excel file to the table. Right click on the database and select Tasks and than Import Data ...

    A Fan of SSIS, SSRS and SSAS

    i got the error below:


    jamal

    Thursday, July 4, 2019 9:43 PM
  • Hi jamal,

    Please use below T-SQL to  find the details of providers installed on your machine.

    EXECUTE MASTER.dbo.xp_enum_oledb_providers
     

    If it dose not exist, you can try to install the following from this link to resolve your error.

    Please check the version of your Excel is right. 

    The problem may also occurred when you right clicking on a database and clicking on import and since SSMS is a 32-bit program it will launch 32-bit processes. The Excel office is 64 bit. So try explicitly running the Import/Export Wizard (64-bit) by clicking on Start->Program Files->Microsoft SQL Server->Import and Export Data (64-bit) to import your data from a 64-bit data source.

    Please refer to SQL SERVER – FIX – Export Error – ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine to get more information.

    Hope this could help you.

    Best regards,
    Cathy Ji


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 5, 2019 3:03 AM
  • Hi jamal,

    Please use below T-SQL to  find the details of providers installed on your machine.

    EXECUTE MASTER.dbo.xp_enum_oledb_providers
     

    If it dose not exist, you can try to install the following from this link to resolve your error.

    Please check the version of your Excel is right. 

    The problem may also occurred when you right clicking on a database and clicking on import and since SSMS is a 32-bit program it will launch 32-bit processes. The Excel office is 64 bit. So try explicitly running the Import/Export Wizard (64-bit) by clicking on Start->Program Files->Microsoft SQL Server->Import and Export Data (64-bit) to import your data from a 64-bit data source.

    Please refer to SQL SERVER – FIX – Export Error – ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine to get more information.

    Hope this could help you.

    Best regards,
    Cathy Ji


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thanks Cathy for the help

     

    1. I installed the Microsoft Access Database Engine 2010 Redistributable _AccessDatabaseEngine_X64.exe
    2. I tried it from form the SQL Server 2017 RC1 Import and Export Data (32-bit) but never worked

     

    What could resolve the issue?


    jamal

    Friday, July 5, 2019 7:00 AM
  • Hi jamal,

    I find you have installed the 64-bit version “Microsoft.ACE.OLEDB.12.0”, and tried Import/Export Wizard (32-bit). If I misunderstood, please let me know.

    I suggested you try run the Import/Export Wizard (64-bit) that mentioned above, please try again. Then share us the result. 

    Hope this could help you.

    Best regards,
    Cathy Ji



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 5, 2019 7:37 AM
  • Hi jamal,

    I find you have installed the 64-bit version “Microsoft.ACE.OLEDB.12.0”, and tried Import/Export Wizard (32-bit). If I misunderstood, please let me know.

    I suggested you try run the Import/Export Wizard (64-bit) that mentioned above, please try again. Then share us the result. 

    Hope this could help you.

    Best regards,
    Cathy Ji



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    it works fine with the 64 bit. Now, how to proceed? which destination i need to choose? i ogt the message below


    jamal

    Friday, July 5, 2019 3:14 PM
  • Select "SQL Server Native Client 11" as your destination if you want to import data to the database table.

    A Fan of SSIS, SSRS and SSAS

    Friday, July 5, 2019 3:20 PM
  • You can use a Excel formular in your spreadsheet and execute the commands in a query window.

    =CONCAT("INSERT T7 SELECT NULL, ";"'";A2;"'";";")

    Then, copy column B to a new query, execute it

    Finally, you get this


    Saturday, July 6, 2019 4:42 AM
  • How to copy\paste multiple records of a field from Excel to SQL server table?

     

    In the screenshot below, I wanted to copy\paste the value of the “name” field to the “E2” field in SQL Server table. How this can be performed?

     

    Thank you

     

    Jamal


    jamal

    Many thanks for you all.

     

    I could found the direct solution here:

    Import Excel data into SQL Server using copy and paste


    jamal

    Saturday, July 6, 2019 7:58 PM
  • Hi Jamal,

    I am so glad to hear that you have resolved your issue. Please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 8, 2019 1:39 AM