Sql Server 2008 : Update The Table With Primary Key

I modified the table from the development database and want to update the same table on production database with new data from the development server. Please let me explain this...

Solution 1:

Assuming that the two databases are on the same server (or linked server). You could do an MERGE query. If not you could update the values with an UPDATE query.

MERGEINTO ProductionDB.Schema.ParentTable A
USING DevelopmentDB.Schema.ParentTable B
ON A.ParentID = B.ParentID
WHEN MATCHED THENUPDATESET A.Name = B.Name, A.Value = B.Value;

Of course replace your server/database names. But this an rough example of what you're looking for.

Solution 2:

I'd probably try something like:

declare@parentOffsetintbegin transaction

select@parentOffset=max(ParentID) from Prod.dbo.ParentTable (holdlock)

set identity_insert Prod.dbo.ParentTable oninsertinto Prod.dbo.ParentTable ( ParentID , Name , Value )
select Dev.dbo.ParentTable.ParentID +@parentOffset , Dev.dbo.ParentTable.Name , Dev.dbo.ParentTable.Value
from Dev.dbo.ParentTable.ParentTable

set identity_insert Prod.dbo.ParentTable off

insertinto Prod.dbo.ChildTable ( ParentID , SomeOtherField )
select Dev.dbo.ChildTable.ParentID +@parentOffset , Dev.dbo.ChildTable.SomeOtherField

commit transaction

Obviously, test it out against an offline copy of things before you pull the trigger in production, and would put in some error handling, but this should get the job done. It won't test to see if there are already existing values in the table, but you could handle that with a slight modification & going through a temp table. It's unclear if that's your situation, or whether you just want to add.