March 18, 2006 at 11:08 am
I need to make a script in SQL 2005 to import data from an Excel sheet into a SQL table.
I am using the wizard to import now.
Import from Excel 2000. First row of the excel sheet has column names.
Excel file name is: EXL.xls, sheet name is: Sheet1
Destination sql database name is: NM, table name is: Sht1
I use SQL Server Authentication to access the database.
User name: ABC and password: DEF
Database name is: DB
I am using the following setting when importing now:
- Delete rows in destination table
- Enable identity insert
Jim
March 20, 2006 at 3:31 am
use the following procedure to import Excel Sheet data into SQL table
right click on database name in SQL Server Management Studio, you can see Tasks-->Import Data. Click on Import Data you can see import/export wizard click next. Choose a data source Microsoft Excel. Browse the path for your execl file and click next. Choose a destination 'Microsoft OLE DB Provider for SQL Server'. select SQL Server Authentication and put username and password, select database and click next. keep the default selection and click next. select 'Sheet1$' from source tab and select [database Name].[dbo].[sht1] from destination tab click next. keep the default settings and click next and click on Finish. This will insert the data from excel sheet into database table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply