locked
Load my excel sheet to my sql server database!? RRS feed

  • Question

  • User273232671 posted
    Anyone knows a code to load my excel sheet to my new empty sql server database!? And also if indid insert it how could i delete all the records and make the table enpty again!?
    Wednesday, January 27, 2016 4:46 PM

All replies

  • User2103319870 posted

    to load my excel sheet to my new empty sql server database!?

    You can take a look at the below link which explains your scenario excellently with step by step explanation

    Alternatively if your requirement is to just push the data into sql server from Excel, then you can try the below options

    SQL Server

    You can use the SQL Sever Export Import Wizard to retreive and save data 

    Apart from the above option you can also use the SQL function OPENROWSET, suppose you have an Excel file in the C:\ drive of the SQL Server database, and the data is on a Worksheet called Sheet1 and your two columns are called Column1 and Column2.

    INSERT INTO [dbo].[Table1]  ( [Column1 ], [Column2] )
    
    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    
    'Excel 8.0;Database=C:\MyTable.xls',
    
    'SELECT * FROM [Sheet1$]')

    Another suggestion is to use SSIS to import data from Excel to sql server

    Wednesday, January 27, 2016 7:07 PM
  • User273232671 posted
    Thabk you, i will try these methods and see if they work
    Wednesday, January 27, 2016 7:23 PM
  • User-219423983 posted

    Hi MishMish,

    And also if indid insert it how could i delete all the records and make the table enpty again!?

    If you want to delete all the records of a table, you could use the “TRUNCATE TABLE  [TableName]” to improve the speed instead of using “Delete * from [TableName]”. For more things, you could refer to the following thread.

    http://stackoverflow.com/questions/1112395/delete-all-rows-in-table

    Besides, the above suggestion is very detailed, if the it’s helpful to you to import the Excel to the database, you could mark it as answer to close this thread to help other community members who have the similar questions.

    Best Regards,

    Weibo Zhang

    Thursday, January 28, 2016 4:41 AM