This post is about combining the data from two tables by adding columns, not rows. Here we think of adding data to the right side of a table in columns, not adding rows to the bottom of a table the way we would when we UNION or UNION ALL.

I will use a very simple example to show how this works. Even before reading the code, click on the screenshot below to see where we are going with this.

CREATE TABLE [dbo].[CombineName](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Score] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CombineName] ([Id], [Name], [Score]) VALUES (2, N'Matt', NULL)
GO
INSERT [dbo].[CombineName] ([Id], [Name], [Score]) VALUES (7, N'Jack', NULL)
GO
INSERT [dbo].[CombineName] ([Id], [Name], [Score]) VALUES (3, N'Sally', NULL)
GO

CREATE TABLE [dbo].[CombineNameScore](
	[Id] [int] NOT NULL,
	[Score] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CombineNameScore] ([Id], [Score]) VALUES (7, 127)
GO

Here is our script that adds the score data to the table CombineName.

UPDATE [CombineName]
SET [CombineName].Score = [CombineNameScore].Score
FROM [CombineName]
     JOIN [CombineNameScore] ON [CombineName].Id = [CombineNameScore].Id;

Below is what SSMS would look like if we ran the above scripts and inserted a few SELECT statements throughout.

Notice that we need to join on the Id. Not every person in the original table CombineName has a score in the table with the scores.

Merge

This example has nothing to do with merge. For an example of what SQL Merge is, have a look at SQLShack’s article Understanding the SQL MERGE statement.