Import Table Data From MS-Excel

Sample Query:

--Created by :Manikandan.R
--Created Date : nov 11 2010
--Purpose : need to read the files from Excel And need to insert into Source Table

--decalre
declare @TSQLstmt varchar(800)
declare @path nvarchar(50)
declare @filename nvarchar(50)
declare @UserName nvarchar(50)
declare @passwd nvarchar(50)
DECLARE @sheetName nvarchar(50)
DECLARE @ResultExcel table (Col1 int,Col2 nvarchar(245),Col3 nvarchar(245),Col4 int ,Col5 int,Col6 bit)

--------------------------------------------**************need to pass the Excel details by user*************------------------
SET @path='D:\'
SET @filename='sampleFile.xls'
SET @UserName=''
SET @passwd=''
SET @sheetName='Sheet1'
--------------------------------------------**************End*************-------------------------------------------------------

BEGIN try
BEGIN TRANSACTION
SET @TSQLstmt = 'SELECT * FROM OpenDataSource( ' + '''' +
'Microsoft.Jet.OLEDB.4.0' + '''' + ',' +
'''' + 'Data Source="' + rtrim(ltrim(@path)) + '\' +
rtrim(ltrim(@fileName)) + '";User ID='+ltrim(rtrim(@userName)) + ';Password=' +
ltrim(rtrim(@passwd)) + ';Extended properties=Excel 8.0' + '''' +
')...['+ rtrim(ltrim(@sheetName)) + '$]'



INSERT INTO @ResultExcel
EXEC (@TSQLstmt)

--Find Duplicates
delete e2 from @ResultExcel e1 ,@ResultExcel e2 where e1.Col2=e2.Col2 and e1.Col1


--Final Result from Excel
--SELECT Menu_Name,Url,Parent_id,Sort_order,Active FROM @ResultExcel WHERE menu_id is not null
INSERT INTO menu(col1,col2,col3,col4,col5)


SELECT col1,col2,col3,col4,col5 FROM @ResultExcel WHERE col1 is not null



IF @@ERROR != 0 --check @@ERROR variable

BEGIN
ROLLBACK TRANSACTION --RollBack Transaction if Error..
RETURN
END

ELSE
BEGIN

COMMIT TRANSACTION
END

END TRY
BEGIN CATCH
ROLLBACK TRANSACTION

END CATCH

sql pagination

Sample :

DECLARE @pageSize INT
DECLARE @pageNumber INT
--Set PageSIze
SET @pageSize=10

--Set PageNo
SET @pageNumber=1 --Dynamiclly change Your Page No


DECLARE @Employee TABLE (empid INT,EmpName NVARCHAR(50),Details NVARCHAR(50))

--For just Adding Data
insert into @Employee
SELECT 1,'Manikandan','SSE'
UNION ALL
SELECT 1,'Manikandan','SSE'
UNION ALL
SELECT 1,'Manikandan','SSE'
UNION ALL
SELECT 2,'Manikandan','SSE'
UNION ALL
SELECT 3,'Manikandan','SSE'
UNION ALL
SELECT 4,'Manikandan','SSE'
UNION ALL
SELECT 5,'Manikandan','SSE'
UNION ALL
SELECT 6,'Manikandan','SSE'
UNION ALL
SELECT 7,'Manikandan','SSE'
UNION ALL
SELECT 8,'Manikandan','SSE'
UNION ALL
SELECT 9,'Manikandan','SSE'
UNION ALL
SELECT 10,'Manikandan','SSE'
UNION ALL
SELECT 11,'Manikandan','SSE'
UNION ALL
SELECT 12,'Manikandan','SSE'
UNION ALL
SELECT 13,'Manikandan','SSE'
UNION ALL
SELECT 14,'Manikandan','SSE'
UNION ALL
SELECT 15,'Manikandan','SSE'
UNION ALL
SELECT 16,'Manikandan','SSE'


--Paging Performance

SELECT * FROM(
SELECT Row_number() over (order by empid,EmpName) as RowNumber,EmpName,Details from @Employee emp
)Finalresult WHERE Rownumber BETWEEN (@pageSize * (@pageNumber - 1) + 1) AND (@pageSize * @pageNumber)