Howto: Move table data between databases

Working in different environments - development, test, staging, production often makes you create data in one database and have the need to move it to another. This is actually quite easy to do with T-SQL.

-- Move users created in developmentdb to testdb
BEGIN TRANSACTION
  -- Enable identity insert if you need to insert the same PK
  SET IDENTITY_INSERT [testdb].[dbo].[user] ON


INSERT INTO [testdb].[dbo].user

    SELECT [id], [name], [password], [email] 
    FROM [developmentdb].[dbo].[user] 
    WHERE [id] IN ('753010', '753011', '753012', '753013')

SET IDENTITY_INSERT [testdb].[dbo].[user] OFF
COMMIT TRANSACTION