INSERT INTO table which requires a Join

Question

Im currently trying to move data from one table to another. In a previous question I asked today I was advised to use an insert statement. While trying to run the statement i get an error from SQL Server as follows:

Cannot insert the value NULL into column 'Parent', table '*****.dbo.Product'; column does not allow nulls. INSERT fails.

This is the statement I have created,

BEGIN TRANSACTION
INSERT INTO [*****].[dbo].[Product] 
([PDate]
  ,[SDate]
  ,[CreatedBy]
  ,[CreatedDate]
  ,[UpdatedBy]
  ,[UpdatedDate])
SELECT d.[PDate]
  ,d.[SDate]
  ,d.[CreatedBy]
  ,d.[CreatedDate]
  ,d.[UpdatedBy]
  ,d.[UpdatedDate]
FROM [*****].[dbo].[ProductData] AS d
JOIN [*****].[dbo].[Product] AS t
ON d.ProductDataID = t.ProductDataID
ROLLBACK TRANSACTION

I need to ensure that the data matches up correctly

Edit: Sorry, there was an error when i was copying the script. UseDRM is in both sections of my original script. What I want to do is to copy the data in the ProductData table to the Product table.

Answer 1

I finally figured out what i was doing wrong, instead of using an INSERT INTO statement I should have been using an UPDATE statement

UPDATE [********].[dbo].[Product] 
SET [PDate] = [********].[dbo].[ProductData].[PDate]
  ,[SDate] = [********].[dbo].[ProductData].[SDate]
  ,[CreatedBy] = [********].[dbo].[ProductData].[CreatedBy]
  ,[CreatedDate] = [********].[dbo].[ProductData].[CreatedDate]
  ,[UpdatedBy] = [********].[dbo].[ProductData].[UpdatedBy]
  ,[UpdatedDate] = [********].[dbo].[ProductData].[UpdatedDate]
FROM [********].[dbo].[ProductData], [********].[dbo].[Product]
WHERE [********].[dbo].[Product].[ProductDataID] = [********].[dbo].[ProductData].[ProductDataID]

I apologise for my orginial question which is poorly written