Insert OR Update - How? - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Insert OR Update - How?

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-04-19 : 13:36:28

How would I add to this code to Update if the record exists and only Insert if it doesn't? EmpID is unique.

INSERT INTO Employee
(
	 EmpID
 	,EmpFullName
	,AU
	,EmpEmail
	,EmpPhone
	,MgrEmpID
	,MgrFullName
	,MgrEmail
)
SELECT
	 [dbo].[HR_Import].EMPLID
	,[dbo].[HR_Import].NAME
	,[dbo].[HR_Import].AU
	,[dbo].[HR_Import].EMAIL_ID
	,[dbo].[HR_Import].WORK_PHONE
	,[dbo].[HR_Import].REPORTS_TO_MGR_EMPLID
	,[dbo].[HR_Import].REPORTS_TO_MGR_NAME
	,[dbo].[HR_Import].REPORTS_TO_MGR_EMAIL
FROM [dbo].[HR_Import]

Duane

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-19 : 13:43:50

try MERGE

quote:

Originally posted by duanecwilson

How would I add to this code to Update if the record exists and only Insert if it doesn't? EmpID is unique.

INSERT INTO Employee
(
	 EmpID
 	,EmpFullName
	,AU
	,EmpEmail
	,EmpPhone
	,MgrEmpID
	,MgrFullName
	,MgrEmail
)
SELECT
	 [dbo].[HR_Import].EMPLID
	,[dbo].[HR_Import].NAME
	,[dbo].[HR_Import].AU
	,[dbo].[HR_Import].EMAIL_ID
	,[dbo].[HR_Import].WORK_PHONE
	,[dbo].[HR_Import].REPORTS_TO_MGR_EMPLID
	,[dbo].[HR_Import].REPORTS_TO_MGR_NAME
	,[dbo].[HR_Import].REPORTS_TO_MGR_EMAIL
FROM [dbo].[HR_Import]

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-04-19 : 17:29:56

Thank you. I ended up using a merge and it seemed to work.

Duane

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-04-20 : 12:06:33

As an aside, you need to clean up the table; you have Personnel (entity) and Organizational structure (relationship) in the same table. That is a fundamental design error.

Also the SQL Server version of MERGE has some nice extensions from the Standard. It is worth a quick study to learn them.

--CELKO--
Joe Celko, SQL Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources