Review Insert/Update stored procedure I am not a DBA nor do I play one on TV.  

My application is retrieving information from Facebook and storing it into our own database.  I want to retrieve the Location Post information of Id and AuthorId.  AuthorId is a foreign key to another table.  I was told by a DBA that foreign keys should always use internal unique identifiers because you can never rely on external unique identifiers.  So I created the following table structure...

CREATE TABLE tblAuthor
(
    [MyAuthorId] INT NOT NULL PRIMARY KEY IDENTITY
    [FacebookAuthorId] BIGINT NOT NULL
    ...Additional Facebook information...
)

CREATE TABLE tblLocationPost
(
    [MyLocationPostId] INT NOT NULL PRIMARY KEY IDENTITY, 
    [FacebookLocationPostId] BIGINT NOT NULL, 
    [MyAuthorId] INT NOT NULL,
    ...Additional Facebook information...
    CONSTRAINT [FK_tblLocationPost_Author] FOREIGN KEY (MyAuthorId) REFERENCES tblAuthor(MyAuthorId)
)


The data I get back from Facebook when querying Location Post is LocationPostId and AuthorId.  So I have created the following stored procedure to insert/update the LocationPost information in the database...

CREATE PROCEDURE [dbo].[LocationPost_Save]
@facebookLocationPostId bigint,
@facebookAuthorId bigint
AS
BEGIN
     DECLARE @myAuthorId INT
     DECLARE @IdentityColumn TABLE ( IdentityId int )

     BEGIN TRANSACTION

     /* Locate out internal unique id using facebooks unique author id, insert it if necessary */
     IF EXISTS (SELECT * FROM tblAuthor WHERE FacebookAuthorId = @facebookAuthorId)
     BEGIN
         SELECT @myAuthorId = MyAuthorId FROM tblAuthor WHERE FacebookAuthorId = @facebookAuthorId
     END
     ELSE
     BEGIN
         INSERT INTO tblAuthor (FacebookAuthorId) VALUES (@facebookAuthorId)
         SELECT @myAuthorId = SCOPE_IDENTITY()
     END

     /* Insert/Update one row in LocationPost */
     MERGE tblLocationPost lp
     USING (SELECT @facebookId as FacebookId) fb
     ON (lp.FacebookId = fb.FacebookId)
     WHEN MATCHED THEN 
         UPDATE SET 
             MyAuthorId = @myAuthorId,
             ...Additional data...
     WHEN NOT MATCHED THEN
         INSERT 
             (FacebookLocationPostId, MyAuthorId, ...Additional Columns...)
         VALUES 
             (@facebookLocationPostId, @myAuthorId, ...Additional Values...)
     OUTPUT INSERTED.MyLocationPostId INTO @IdentityColumn;

     COMMIT TRANSACTION

     /* Return the identify column of Inserted/Updated row */
     SELECT IdentityId FROM @IdentityColumn
END



Is this the correct pattern to use for this scenerio or is there a better approach?
Is the MERGE command the appropriate approach to Insert/Update a record (I am using SQL Server 2008)?
Do I really need to put my own Author Unique Id in the LocationPost table, or can I simplify this by putting the Facebook Unique Id?
I'd rather use the FacebookAuthorId and FacebookLocationPostId as primary keys, because your logic ensures them to be unique. In case of your current solution you would still require (unique) indexes on these fields (in order for lookups to be efficient) so there is no reason not to use them as primary keys.
Given suggest optimization you don't need to check/insert an author record   LocationPost_Save, just use the merge to insert/update the data in the table, and create a separate Author_Save SP that will do the same job for Authors.


Having said that, assuming that you are using one of the high-level languages :) and you're developing a long-living solution I would actually recommend you to use one of the ORMs available for your language to interact with the database. It'll take a bit longer at the beginning, but you'll get a more flexible solution in a long term.