Here I am discussing about data insertion from MS Excel into SQL Server table. SSMS provides Export Import wizard by which you can achieve same easily. Follow below steps
Suppose I have a Excel file on server as c:/fis.xlsx.
1. Go to database
2. Right Click on database select tasks
3. Select Import Data
- A SQL Server Export Import wizard appears.Click Next.
- Choose DataSource Microsoft Excel from the List and Browse the Excel File
- Click Next.
- Choose destination.
- Click Next
- Next
- Click Next->Next and Finish.
- Finally a new table named Sheet1$ created.
- Now if you want to insert these records in existing table,you can use simple insert statement and drop table.
--Sample
insert into dbo.existingtable(col1,col2,col3) values (select col1,col2,col3 from dbo.Sheet1$)
Example:
insert into dbo.FileRecord(FileNo,ProjectName,Customer_Name,PropertyCode,Status,IDate,InsertedBy,UpdateDate,UpdatedBy,remarks,location) select top 2 [FileNo,ProjectName,Customer_Name,PropertyCode,Status,IDate,InsertedBy,UpdateDate,UpdatedBy,remarks,location from dbo.Sheet1$
- Your data now successfully inserted into SQL Server table from Excel.
- You can drop the table.
- drop table dbo.Sheet1$
