Insert Excel data to Sql and Sql table data to Excel file

First execute this procedure

EXEC sp_configure ’show advanced options’, 1;

GO

RECONFIGURE;

GO

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;

GO

RECONFIGURE;

GO

After executing procedure then write the query

to insert sql table data to excel file.

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;HDR=YES;’,
‘SELECT * FROM [SheetText1$]‘)
SELECT * from TableName
_____________________________________________________________________________________

to insert excel data to sql table

Insert into dbo.tableName
select * from openrowset(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;database=C:\contact.xls’,
’select * from [Sheet1$]‘)

NOTE:
Excel columns and Table Columns should be same.

Reset or Reseed Identity on tables with identity column in sql server

Run this command to reset or reseed Identity columns of a table.

exec sp_MSforeachtable
‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1
BEGIN DBCC CHECKIDENT (”?”,RESEED,0) END’

Once you run this query on a table it will reset Identity column always.