Insert using xml in stored procedure

See more:

SQL-Server-2005

SQL-Server

This is my stored procedure.I am inserting bulk data 20000 rows using xml
And i want fast processing
I am reading data from excel and sending it to stored procedure using xml.

This works properly.
But takes long time i want fast processing

please check my stored procedure

@XML_TRANSACTIONDETAILS nText,
	@status int,
	@username int,
	@urlorcatalog nvarchar(100),
	@dataentrytype nvarchar(100)
	

AS
DECLARE @docHandle int

SET NOCOUNT ON;

EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML_TRANSACTIONDETAILS


-- CREATE TEMP TABLE

CREATE TABLE #TempTable
(
	maxid bigint  NULL,
	companyname nvarchar(100)   NULL,
	email nvarchar(100)   NULL,
	website nvarchar(100)  NULL,
	country nvarchar(100)  NULL,
	contactperson nvarchar(100) NULL,
	telphone nvarchar(100)  NULL,
	mobile nvarchar(100)  NULL,
	fax nvarchar(100)  NULL,
	region nvarchar(100)  NULL,
	status int  NULL,
	username int  NULL,
	date datetime NULL,
	category INT  NULL,
	urlorcatalog nvarchar(100)  NULL,
	dataentrytype nvarchar(100)  NULL	
) 

// here i am inserting data from xml into temp table
--	INSERT INTO TEMP TABLE DETAILS OF EMAIL 

INSERT INTO #TempTable
                (                   maxid,companyname,email,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog,dataentrytype                )

 SELECT  data.maxid,data.companyname,data.email,data.website,data.country,data.contactperson,data.telphone,data.mobile,data.fax,REGION.ID,@status,@username,GETDATE(),CATEGORY.ID,@urlorcatalog,@dataentrytype

     FROM OPENXML(@docHandle,N'master/TRANSACTION',3)

              WITH
              (
               companyname varchar(100),
				website varchar(100),
				country varchar(100),
				contactperson varchar(100),
				telphone varchar(100),
				mobile varchar(100),
				fax varchar(100),
				region varchar(100),
				status int,
				username int,
				--date datetime,
				category varchar(100),
				urlorcatalog varchar(100),
				email varchar(100),
				email1 varchar(100) ,
				email2 varchar(100),
				maxid bigint,
				dataentrytype varchar(100)
               
              ) as data
	
		LEFT OUTER  JOIN category CATEGORY ON    data.category=CATEGORY.categoryname
		LEFT OUTER  JOIN regioninfo REGION ON data.region=REGION.regionname 

//here i am updating temp table if category and region null
--**********************UPDATE TEMP TABLE IF CATEGORY AND REGION IS NULL********************

UPDATE #TempTable 
SET
category=5
WHERE  category  is NULL

UPDATE #TempTable 
SET
region=2
WHERE  region  is NULL


// this code for counting duplicates entry.

-- ******************* CODE TO COUNT  DUPLICATES EMAILS ***************************

SELECT COUNT(*) FROM
	(
			SELECT tmp.*
			FROM #TempTable tmp 
			LEFT OUTER JOIN emailinfo Email ON tmp.email=Email.email
			)AS T 				
		WHERE --T.RowNumber1>1 AND
			  T.email !=''
		AND 
		 EXISTS (SELECT  * FROM emailinfo WHERE email=T.email)


-- TRANSACTION BEGINS HERE 

BEGIN TRANSACTION

//   here updating only status  field email already existed in condition

--  UPDATE STATUS IF EMAIL ALREADY EXISTED

UPDATE companyinfo 
SET companyinfo.status=RESULT.status
FROM
(
select TEMP1.status,TEMP1.DataID
from		(	
			SELECT tmp.email,tmp.status,Email.DataID,ROW_NUMBER() OVER (PARTITION BY tmp.email ORDER BY tmp.email ) AS 'RowNumber'
			FROM #TempTable tmp 
			LEFT OUTER JOIN emailinfo Email ON tmp.email=Email.email			
										
			WHERE 
			  tmp.email !=''		
			AND 
			EXISTS (SELECT  * FROM emailinfo WHERE email=tmp.email)
		)AS TEMP1

	LEFT OUTER JOIN companyinfo COMPANY ON TEMP1.DataID=COMPANY.ID	

	where --cmp.status !=8	And
	TEMP1.RowNumber=1       AND
	COMPANY.status !=1			AND 
	COMPANY.status < 6         AND
	TEMP1.status < COMPANY.status

) AS RESULT

WHERE companyinfo.ID=RESULT.DataID

IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END
--  ************INSERT INTO PRIMARY  TABLE(companyinfo)*************

INSERT INTO companyinfo
--                (
--                   ID,companyname,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog
--                )

SELECT
						  T1.maxid,T1.companyname,T1.website,T1.country,T1.contactperson,T1.telphone,T1.mobile,T1.fax,T1.region,T1.status,T1.username,T1.date,T1.category,T1.urlorcatalog,T1.dataentrytype,null
						FROM
						(	
							SELECT tmpdata.*, ROW_NUMBER() OVER (PARTITION BY maxid ORDER BY maxid ) AS 'RowNumber'
							FROM 
							(
							
									SELECT
												*
										
										FROM
										(
												
												  SELECT tmp.*, ROW_NUMBER() OVER (PARTITION BY tmp.email ORDER BY tmp.email ) AS 'RowNumber1'
													FROM #TempTable tmp 
													LEFT OUTER JOIN emailinfo Email ON tmp.email=Email.email
										--			LEFT OUTER  JOIN category CATEGORY ON tmp.category=CATEGORY.ID 
											--		LEFT OUTER  JOIN regioninfo REGION ON tmp.region=REGION.ID 
														
										)AS T 
										
										WHERE T.RowNumber1=1
										AND  T.email !=''
										AND 
										NOT EXISTS (SELECT  * FROM emailinfo WHERE email=T.email)


							 )tmpdata
			

						)AS T1

						WHERE 
					T1.email !=''  AND

					T1.RowNumber=1



--  ************INSERT INTO SECONDARY TABLE*************


INSERT INTO emailinfo
		( 
			DataID,email
			)
SELECT
		T.maxid,T.email

FROM
(
		SELECT tmp.*, ROW_NUMBER() OVER (PARTITION BY tmp.email ORDER BY tmp.email ) AS 'RowNumber'
		FROM #TempTable tmp 
			LEFT OUTER JOIN emailinfo Email ON tmp.email=Email.email
)AS T 

WHERE T.RowNumber=1
AND  T.email !=''
AND 
NOT EXISTS (SELECT  * FROM emailinfo WHERE email=T.email)   

 IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END


COMMIT TRANSACTION

EXEC sp_xml_removedocument @docHandle

please give me suggestion for any optimization if any
Thanks ...............