patrickmcginnis59 10839 - Wednesday, March 20, 2019 12:34 PM

UCDA2019 - Wednesday, March 20, 2019 11:01 AM

I need to copy data from a particular column in a table and transfer to another table however, this involves moving from one SQL Server instance to another instance.

Does anyone have any tips for achieving this?

So far i have tried the following in [localhost/SQLEXPRESS]

EXEC sp_addlinkedserver [abcwebservices\SQLEXPRESS]', @srvproduct='', @provider='SQLNCLI', @datasrc='abcwebservices\SQLEXPRESS'

Followed by the script below to transfer the cloumn data from one table to the next;

INSERT INTO [ABCWEBSERVICES\SQLEXPRESS].CPM.dbo.fct_Project([Proj_No],[CreateDate2])
SELECT [CreateDate2] FROM [localhost\SQLEXPRESS].CPM.dbo.fct_Project; 

However this produces the following error message:

The statement has been terminated.
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Proj_No', table 'CPM.dbo.fct_Project'; column does not allow nulls. INSERT fails.

Why is this occuring when all i intend to do is copy createdate2 in one table to createdate2 in another table 

you haven't specified a value for destination column 'fct_Project'. If you don't care what goes into the destination, you can always just use a literal but honestly why aren't you carrying along the value of the source column in 'fct_project?' ie., 

INSERT INTO [ABCWEBSERVICES\SQLEXPRESS].CPM.dbo.fct_Project([Proj_No],[CreateDate2])
SELECT [Proj_No],[CreateDate2] FROM [localhost\SQLEXPRESS].CPM.dbo.fct_Project;