Merge only Missing Data - 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 2012 Forums

Transact-SQL (2012)

Merge only Missing Data

Author

Topic

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-06 : 14:34:32

down vote favorite

I am working on an HR project that provides data to me in the form of an Excel document.

I have created a package that captures the data from the Spreadsheet and imports it into SQL. The customer then wanted to create a data connection and place the data into Pivot Tables to manipulate and run calculations on.

This brought to light a small issue that I have tried to get fixed from the source but looks like cannot be resolved on the System Side (working with an SAP backend).

What I have is information that comes into SQL from the import that is either missing the Cost Center Name or both the cost center number and the cost center name.

EXAMPLE:

EmpID EmployeeName CostCenterNo CostCenterName
001 Bob Smith 123456 Sales
010 Adam Eve 543211 Marketing
050 Thomas Adams 121111
121 James Avery

I worked with HR to get the appropriate information for these employees, I have added the information to a separate table.

What I would like to do is figure out a way to insert the missing information as the data is imported into the Staging table.

Essentially completing the data.

EmpID EmployeeName CostCenterNo CostCenterName
001 Bob Smith 123456 Sales
010 Adam Eve 543211 Marketing
050 Thomas Adams 121111 Supply Chain
121 James Avery 555316 Human Resources

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 14:48:24

Here is one approach

MERGE	dbo.Example AS tgt
USING	(
		SELECT	EmployeeID AS EmpID,
			Name AS EmployeeName,
			ccNo AS CostCenterNo,
			ccName AS CostCenterName
		FROM	dbo.Auxiliary
		WHERE	Country = 'Some'
	) AS src ON src.EmpID = tgt.EmpID
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					EmpID,
					EmployeeName,
					CostCenterNo,
					CostCenterName
				)
			VALUES	(
					src.EmpID,
					src.EmployeeName,
					src.CostCenterNo,
					src.CostCenterName
				)
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.EmployeeName =	CASE
								WHEN src.EmployeeName > '' THEN src.EmployeeName
								ELSE tgt.EmployeeName
							END,
				tgt.CostCenterNo =	CASE
								WHEN src.CostCenterNo > '' THEN src.CostCenterNo 
								ELSE tgt.CostCenterNo 
							END,
				tgt.CostCenterName =	CASE
								WHEN src.CostCenterName > '' THEN src.CostCenterName 
								ELSE tgt.CostCenterName 
							END;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-06 : 15:26:22

I wondered if it would be something similar to what you showed me last time.

I have a few questions though.

The WHERE Statement, I don't have a 'Country' column in any of my data, where does this come from?

Also, I don't need to update the employee name, it is never missing. I was just using it as a reference, do I need to use the employee name (for accuracy)?

Basically I just want to update the missing cost center number and name for these termed employees since I cant update the source data (which is the preferred method in my opinion).

Thanks again for your help.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 15:30:36

I don't have a clue about your source table. The example I posted is just that, an example.
Feel free to change and edit to suit your needs.
Remove the line not appropriate.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-06 : 15:54:33

I understand. I'll play with the script, I think this may work the best.
Thank you again.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-06 : 16:16:36

I just wanted to share my altered script. It worked like a charm.

MERGE [Staging].[HRIS_EEMaster] AS tgt
USING (
SELECT PersNo AS EmpID,
CostCenterNo AS CCNo,
CostCenterName AS CCName
FROM [dbo].[MissingTermedCC]
) AS src ON src.EmpID = tgt.PersNo
WHEN NOT MATCHED BY TARGET
THEN INSERT (
PersNo,
CostCenterNo,
CostCenterSubDiv
)
VALUES (
src.EmpID,
src.CCNo,
src.CCName
)
WHEN MATCHED
THEN UPDATE
SET tgt.CostCenterNo = CASE
WHEN src.CCNo > '' THEN src.CCNo
ELSE tgt.CostCenterNo
END,
tgt.CostCenterSubDiv = CASE
WHEN src.CCName > '' THEN src.CCName
ELSE tgt.CostCenterSubDiv
END;

I wish I could take classes from you. You're a great teacher.
(I am honestly learning a lot from these little submissions)

Thanks again for all of your help.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-06 : 16:22:43

AND, I wrote the script into my SSIS package by adding an "Execute SQL Task" to the end of my Data flow (just adding the script directly to the task instead of linking it to a stored procedure) so that it runs the script and updates the data. It's awesome!!!!!

Thank you again.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources