So what I've got here below is a web form. It takes a source query and target table and spits out a merge statement for the standard insert/update use case. I made it for myself but you might find it useful too. I'm counting on this to be a real time saver (at least for me).
Some Neat Things
This webform doesn't post to any server. It's all javascript that gets executed in your browser. This means you don't have to worry about anyone stealing your code and I don't have to worry about this form going viral (ha ha).
SQL Snippets (eitherkind) don't quite handle the dynamic list of columns here. Otherwise, you would be reading a different post today.
The MERGE statement is a lot more versatile than what I show here. I'm just handling the most common use case.
Things to Watch For
Concurrency. If this is important to you, remember to use appropriate locks (usually UPDLOCK) on the target table.
Make sure the column list of the source query matches that of the target table
You're going to have to clean up the script if your target table has columns that are rowversion, identity, computed etc...
User input is being used to generate code here. So my SQL-injection spidey-sense starts to tingle. But it's okay in this case because I'm not running anything. I'm just displaying it. You're the one who's running this stuff so it's up to you to vouch for any generated code. Take care.
The Form
Dear RSS reader. Unfortunately, the web form didn't survive the trip through the RSS feed. Why don't you visit this post on my site to have the full MERGE experience.
Put in your own data (or just use the sample here). When you're ready, hit MERGE!
So I’ve been working on an automation project that makes frequent use of foreign key metadata. I find myself writing queries for this data but I discovered that there’s no super-easy out-of-the-box view of foreign keys for me to use. Here are the ones I considered.
INFORMATION_SCHEMA views
INFORMATION_SCHEMA views that give me foreign key information include
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE for the foreign key info
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS for the referring columns,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE for the referenced columns
The first view here gives a list of foreign keys and you have to join to the other two tables in order to find the column names. But it’s a crummy solution. First of all, if the foreign key has multiple columns, there’s no real way to match the referring columns to the referenced columns.
The second thing is that we don’t see foreign keys that point to unique Keys (as pointed out by Aaron Bertrand in his post The case against INFORMATION_SCHEMA views.
So that’s out. What else have we got in?
Microsoft SQL Server system views
These views include
sys.foreign_keys
sys.foreign_key_columns
with support from sys.columns and sys.tables
These are the views I deserve, but not the views I need right now. The joins are just too annoying to remember and type each time.
Besides, the word “parent” used here changes with context. The parent table in a foreign key relationship owns the foreign key and does the pointing. But say I’m modeling a hierarchy. In the context of the data model, children records point to their parent records. The mental effort needed to keep these straight is not difficult, but it’s annoying.
My Own Views
So I’ve created my own, the goal is to simplify typing and minimize joins. I skip the word “parent” all together and use “referrer” and “referrenced”. Feel free to use and build on these.
Update June 7, 2012: I added columns ReferrerColumnCanBeNull to each view. I found I wanted it, so I added it here.
FOREIGN_KEYS
CREATEVIEW dbo.FOREIGN_KEYSASSELECT SCHEMA_NAME(fk.schema_id)AS FKSchema ,
fk.nameAS FK ,
SCHEMA_NAME(p.schema_id)AS ReferrerSchema ,
p.nameAS Referrer ,
STUFF(CAST((SELECT','+ c.nameFROM sys.foreign_key_columns fkc
JOIN sys.columns c ON fkc.parent_object_id= c.object_idAND fkc.parent_column_id= c.column_idWHERE fkc.constraint_object_id= fk.object_idORDERBY fkc.constraint_column_idASCFOR
XML PATH('') ,
TYPE
)ASNVARCHAR(MAX)), 1, 1, '')AS ReferrerColumns ,
ISNULL((SELECTTOP11FROM sys.foreign_key_columns fkc
JOIN sys.columns c ON fkc.parent_object_id= c.object_idAND fkc.parent_column_id= c.column_idWHERE fkc.constraint_object_id= fk.object_idAND c.is_nullable=1), 0)AS ReferrerColumnsCanBeNull ,
SCHEMA_NAME(r.schema_id)AS ReferencedSchema ,
r.nameAS Referenced ,
STUFF(CAST((SELECT','+ c.nameFROM sys.foreign_key_columns fkc
JOIN sys.columns c ON fkc.referenced_object_id= c.object_idAND fkc.referenced_column_id= c.column_idWHERE fkc.constraint_object_id= fk.object_idORDERBY fkc.constraint_column_idASCFOR
XML PATH('') ,
TYPE
)ASNVARCHAR(MAX)), 1, 1, '')AS ReferencedColumns ,
fk.delete_referential_action_descAS deleteAction ,
fk.update_referential_action_descAS updateAction ,
fk.object_idAS FKId ,
p.object_idAS ReferrerId ,
r.object_idAS ReferencedId
FROM sys.foreign_keys fk
JOIN sys.tables p ON p.object_id= fk.parent_object_idJOIN sys.tables r ON r.object_id= fk.referenced_object_id
GO
CREATE VIEW dbo.FOREIGN_KEYS
AS
SELECT SCHEMA_NAME(fk.schema_id) AS FKSchema ,
fk.name AS FK ,
SCHEMA_NAME(p.schema_id) AS ReferrerSchema ,
p.name AS Referrer ,
STUFF(CAST(( SELECT ',' + c.name
FROM sys.foreign_key_columns fkc
JOIN sys.columns c ON fkc.parent_object_id = c.object_id
AND fkc.parent_column_id = c.column_id
WHERE fkc.constraint_object_id = fk.object_id
ORDER BY fkc.constraint_column_id ASC
FOR
XML PATH('') ,
TYPE
) AS NVARCHAR(MAX)), 1, 1, '') AS ReferrerColumns ,
ISNULL(( SELECT TOP 1
1
FROM sys.foreign_key_columns fkc
JOIN sys.columns c ON fkc.parent_object_id = c.object_id
AND fkc.parent_column_id = c.column_id
WHERE fkc.constraint_object_id = fk.object_id
AND c.is_nullable = 1
), 0) AS ReferrerColumnsCanBeNull ,
SCHEMA_NAME(r.schema_id) AS ReferencedSchema ,
r.name AS Referenced ,
STUFF(CAST(( SELECT ',' + c.name
FROM sys.foreign_key_columns fkc
JOIN sys.columns c ON fkc.referenced_object_id = c.object_id
AND fkc.referenced_column_id = c.column_id
WHERE fkc.constraint_object_id = fk.object_id
ORDER BY fkc.constraint_column_id ASC
FOR
XML PATH('') ,
TYPE
) AS NVARCHAR(MAX)), 1, 1, '') AS ReferencedColumns ,
fk.delete_referential_action_desc AS deleteAction ,
fk.update_referential_action_desc AS updateAction ,
fk.object_id AS FKId ,
p.object_id AS ReferrerId ,
r.object_id AS ReferencedId
FROM sys.foreign_keys fk
JOIN sys.tables p ON p.object_id = fk.parent_object_id
JOIN sys.tables r ON r.object_id = fk.referenced_object_id
GO
FOREIGN_KEY_COLUMNS
CREATEVIEW dbo.FOREIGN_KEY_COLUMNSASSELECT SCHEMA_NAME(fk.schema_id)AS FKSchema ,
fk.nameAS FK ,
SCHEMA_NAME(p.schema_id)AS ReferrerSchema ,
p.nameAS Referrer ,
pc.nameAS ReferrerColumn ,
pc.is_nullableAS ReferrerColumnCanBeNull ,
SCHEMA_NAME(r.schema_id)AS ReferencedSchema ,
r.nameAS Referenced,
rc.nameAS ReferencedColumn ,
fk.object_idAS FKId ,
fkc.constraint_column_idAS FKColumnId ,
p.object_idAS ReferrerId ,
r.object_idAS ReferencedId
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id= fk.object_idJOIN sys.tables p ON p.object_id= fk.parent_object_idJOIN sys.columns pc ON fkc.parent_object_id= pc.object_idAND fkc.parent_column_id= pc.column_idJOIN sys.tables r ON r.object_id= fk.referenced_object_idJOIN sys.columns rc ON fkc.referenced_object_id= rc.object_idAND fkc.referenced_column_id= rc.column_id
GO
CREATE VIEW dbo.FOREIGN_KEY_COLUMNS
AS
SELECT SCHEMA_NAME(fk.schema_id) AS FKSchema ,
fk.name AS FK ,
SCHEMA_NAME(p.schema_id) AS ReferrerSchema ,
p.name AS Referrer ,
pc.name AS ReferrerColumn ,
pc.is_nullable AS ReferrerColumnCanBeNull ,
SCHEMA_NAME(r.schema_id) AS ReferencedSchema ,
r.name AS Referenced,
rc.name AS ReferencedColumn ,
fk.object_id AS FKId ,
fkc.constraint_column_id AS FKColumnId ,
p.object_id AS ReferrerId ,
r.object_id AS ReferencedId
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.tables p ON p.object_id = fk.parent_object_id
JOIN sys.columns pc ON fkc.parent_object_id = pc.object_id
AND fkc.parent_column_id = pc.column_id
JOIN sys.tables r ON r.object_id = fk.referenced_object_id
JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id
AND fkc.referenced_column_id = rc.column_id
GO