IF condition while importing from excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

IF condition while importing from excel

Author

Topic

ricky_1605
Starting Member

30 Posts

Posted - 2011-09-02 : 01:13:10

Hey guys

i am executing the following query :
(Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\20110831_HR_HMD.xls;HDR=YES',
'SELECT * FROM [20110831_HR_HMD$]')) t2

If Employee1.PS_No = t2.PERNR

Begin

Update T1 Set t1.PS_No = t2.PERNR, t1.Name = t2.FNAME, t1.Sex = t2.Gender, t1.Birthdate = t2.DOB, t1.Grade = t2.GRADE_TEXT, t1.Department = t2.ORG_UNIT, t1.Unit = t2.PA_CODE, t1.Location = T2.PSA_TEXT, t1.Email = t2.EMAIL_ID, t1.Mobile = t2.MOBILE, t1.DateOfJoining = t2.DOJ, t1.Dept_Code = t2.CC_CODE, t1.Dept_Head_PSNo = t2.DH_PSNO, t1.Dept_Head_Name = t2.DH_ENAME
FROM Employee1 t1
JOIN (Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\20110831_HR_HMD.xls;HDR=YES', 'SELECT * FROM [20110831_HR_HMD$]')) t2
ON t1.PS_No = t2.PERNR

End

Else

Begin

INSERT INTO Employee1 (PS_No, Name, Sex, Birthdate, Grade, Department, Unit, Location, Email, Mobile, DateOfJoining, Dept_Code, Dept_Head_PSNo, Dept_Head_Name)
Select t2.PERNR, t2.FNAME, t2.Gender, t2.DOB, t2.GRADE_TEXT, t2.ORG_UNIT, t2.PA_CODE, T2.PSA_TEXT, t2.EMAIL_ID, t2.MOBILE, t2.DOJ, t2.CC_CODE, t2.DH_PSNO, t2.DH_ENAME
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\20110831_HR_HMD.xls;HDR=YES', 'SELECT * FROM [20110831_HR_HMD$]') t2

End

it is giving error near line 3. incorrect syntax near t2
please help!

Nipun Chawla

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 02:02:22

you cant use columns like below in If

If Employee1.PS_No = t2.PERNR

can i ask what exactly you're trying to check here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ricky_1605
Starting Member

30 Posts

Posted - 2011-09-02 : 02:29:38

There is a excel file in which updated data of employees is present. I want to check if the PERNR column of Excel file matches with PS_No column of Employee table then it should update the data and If it doesn't match then it should insert the data.

Nipun Chawla

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 03:07:32

then what you want is this


Update T1 Set t1.PS_No = t2.PERNR, t1.Name = t2.FNAME, t1.Sex = t2.Gender, t1.Birthdate = t2.DOB, t1.Grade = t2.GRADE_TEXT, t1.Department = t2.ORG_UNIT, t1.Unit = t2.PA_CODE, t1.Location = T2.PSA_TEXT, t1.Email = t2.EMAIL_ID, t1.Mobile = t2.MOBILE, t1.DateOfJoining = t2.DOJ, t1.Dept_Code = t2.CC_CODE, t1.Dept_Head_PSNo = t2.DH_PSNO, t1.Dept_Head_Name = t2.DH_ENAME 
FROM Employee1 t1
JOIN (Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\20110831_HR_HMD.xls;HDR=YES', 'SELECT * FROM [20110831_HR_HMD$]')) t2
ON t1.PS_No = t2.PERNR


INSERT INTO Employee1 (PS_No, Name, Sex, Birthdate, Grade, Department, Unit, Location, Email, Mobile, DateOfJoining, Dept_Code, Dept_Head_PSNo, Dept_Head_Name)
Select t2.PERNR, t2.FNAME, t2.Gender, t2.DOB, t2.GRADE_TEXT, t2.ORG_UNIT, t2.PA_CODE, T2.PSA_TEXT, t2.EMAIL_ID, t2.MOBILE, t2.DOJ, t2.CC_CODE, t2.DH_PSNO, t2.DH_ENAME 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\20110831_HR_HMD.xls;HDR=YES', 'SELECT * FROM [20110831_HR_HMD$]') t2
LEFT JOIN Employee1 t1
ON t1.PS_No = t2.PERNR
WHERE t1.PS_No IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources