When we have a new record, we usually have two options:

1. Determine if this is a new or existing record in the application and call the necessary command or stored procedure

2. Determine if this is a new or existing record in a lengthy stored procedure (if exists…)

However SQL Server 2008 has the new Merge command, which makes this kind of process a breeze.

I am going to use AdventureWorks (restored it from SQL 2005), and HumanResources.Department table because it only has 3 columns other than the identity column.

[sourcecode language='sql']

use AdventureWorks
go

declare @Name nvarchar(50) = ‘My New Department’;
declare @GroupName nvarchar(50) = ‘My New Department for test purposes’;

merge into HumanResources.Department as dept
using (select @Name as Name, @GroupName as GroupName) as myTable
on myTable.Name = dept.Name
when matched then
update set GroupName = myTable.GroupName
when not matched then
insert (Name, GroupName) values (Name, GroupName);

[/sourcecode]

The above code will look for a match in this table and upon not finding one, it will insert the new record. Now, to test  this further, let’s run another piece of code with different values, so we see that the code updates as well as inserting a new record.

[sourcecode language='sql']

use AdventureWorks
go

declare @Name nvarchar(50) = ‘My New Department’;
declare @GroupName nvarchar(50) = ‘The latest and greates Department Name’;

declare @Name nvarchar(50) = ‘My New Department’;
declare @GroupName nvarchar(50) = ‘My New Department for test purposes’;

merge into HumanResources.Department as dept
using (select @Name as Name, @GroupName as GroupName) as myTable
on myTable.Name = dept.Name
when matched then
update set GroupName = myTable.GroupName
when not matched then
insert (Name, GroupName) values (Name, GroupName);

[/sourcecode]

You should now see that the GroupName is changed.

Now here is the kicker: We can get all sorts of information back. Check this out:

[sourcecode language='sql']

declare @ChangesTable table(Change varchar(20));

declare @Name nvarchar(50) = ‘My New Department’;
declare @GroupName nvarchar(50) = ‘The latest and greates Department Name’;

merge into HumanResources.Department as dept
using (select @Name as Name, @GroupName as GroupName) as myTable
on myTable.Name = dept.Name
when matched then
update set GroupName = myTable.GroupName
when not matched then
insert (Name, GroupName) values (Name, GroupName)
output $action into @ChangesTable ;

select * from @ChangesTable

[/sourcecode]

You can even use “Inserted.*”, “Deleted.*” just as you would in a trigger to get more information about the updated/inserted/deleted row.

The code below, would delete our new department:

[sourcecode language='sql']

declare @ChangesTable table(Change varchar(20));

declare @Name nvarchar(50) = ‘My New Department’;
declare @GroupName nvarchar(50) = ‘The latest and greates Department Name’;

merge into HumanResources.Department as dept
using (select @Name as Name, @GroupName as GroupName) as myTable
on myTable.Name = dept.Name
when matched then
delete
when not matched then
insert (Name, GroupName) values (Name, GroupName)
output $action into @ChangesTable;

select * from @ChangesTable

[/sourcecode]

For more information on MERGE, check this link:  http://technet.microsoft.com/en-us/library/bb510625.aspx

And on OUTPUT : http://technet.microsoft.com/en-us/library/ms177564.aspx

I think these two are really programming gems and they deserve more attention.

It is possible that in SQL Server Management Studio, you will see the Linked Server as only an icon, instead if getting the full list of tables as in Enterprise Manager (from SQL Server 2000).

In this case, you can use the following command to get the list of all of the tables for that Linked Server.

[sourcecode language='sql']

exec sp_tables_ex LINKED_SERVER_NAME

[/sourcecode]

This will give you a list of tables along with the schema names. Very useful.