Copying items from one Sitecore database into another using SQL.

On one of the recent projects I had to a lot of SQL data manipulations in Sitecore databases, so I figured I would share a script I used. This particular script is for migrating of media items from Sitecore to Sitecore.

declare @ancestorId varchar(56)
set @ancestorId = '[puth the root ID of the items here]'

INSERT INTO [atcc_Master].[dbo].[Items]
SELECT distinct i.ID, i.Name, i.TemplateID, i.MasterID, i.ParentID, i.Created, i.Updated
  FROM [ATCCWeb].[dbo].[Items] i
    join [ATCCWeb].[dbo].[Descendants] d on d.Descendant = i.ID
left join [atcc_Master].[dbo].[Items] i2 on i.ID = i2.ID
where i2.ID is null and d.Ancestor = @ancestorId
GO

INSERT INTO [atcc_Master].[dbo].[VersionedFields]
SELECT distinct vf.[Id]
      ,vf.[ItemId]
      ,vf.[Language]
      ,1
      ,vf.[FieldId]
      ,vf.[Value]
      ,vf.[Created]
      ,vf.[Updated]
  FROM [ATCCWeb].[dbo].[VersionedFields] vf
  join [ATCCWeb].[dbo].[Descendants] d on d.Descendant = vf.ItemId
  left join [atcc_Master].[dbo].[VersionedFields] vf2 on vf2.ItemId = vf.ItemId and vf2.FieldId = vf.FieldId
where vf2.ID is null and
d.Ancestor = @ancestorId

GO

INSERT INTO [atcc_Master].[dbo].[UnversionedFields]
SELECT distinct vf.[Id]
      ,vf.[ItemId]
      ,vf.[Language]
      ,vf.[FieldId]
      ,vf.[Value]
      ,vf.[Created]
      ,vf.[Updated]
  FROM [ATCCWeb].[dbo].[UnversionedFields] vf
  join [ATCCWeb].[dbo].[Descendants] d on d.Descendant = vf.ItemId
  left join [atcc_Master].[dbo].[UnversionedFields] vf2 on vf2.ItemId = vf.ItemId and vf2.FieldId = vf.FieldId
where vf2.ID is null and
d.Ancestor = @ancestorId

GO