March 20, 2019 at 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
March 20, 2019 at 12:34 pm
UCDA2019 - Wednesday, March 20, 2019 11:01 AMI 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;
March 21, 2019 at 2:43 am
patrickmcginnis59 10839 - Wednesday, March 20, 2019 12:34 PMUCDA2019 - Wednesday, March 20, 2019 11:01 AMI 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 tableyou 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;
As in the database I'm transferring the data too there is already a 'Proj_No' field with more rows.
However I'm trying to update the old values within that column!
i..e The database I want to move my values from has 16998 fields whereas the one i want to update has 17050
March 21, 2019 at 6:50 am
UCDA2019 - Thursday, March 21, 2019 2:43 AMpatrickmcginnis59 10839 - Wednesday, March 20, 2019 12:34 PMUCDA2019 - Wednesday, March 20, 2019 11:01 AMI 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 tableyou 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;As in the database I'm transferring the data too there is already a 'Proj_No' field with more rows.
However I'm trying to update the old values within that column!
i..e The database I want to move my values from has 16998 fields whereas the one i want to update has 17050
Ok sounds more like an update than an insert.
In general you update one table from another often by using a common key, which looks like it might be column 'proj_no', ie., you match on that. Inserts are going to create new rows. You may have assumed that the SQL server would do the matching based on matching 'proj_no' but there is nothing in your insert statement that requests this action!
Say I have a table "TABLE1" which has a column working as a key called "KEY1" and another column with data associated with that key called "DATA1". Say you have another table with the same layout, but you want to update the first table with the values in DATA1 from the second table (call it "TABLE2"), you'll have to do this by matching on "KEY1". the code would look something like this:
UPDATE dest
SET dest.DATA1 = source.DATA1
FROM TABLE2 source
JOIN TABLE1 dest
ON source.KEY1 = dest.KEY1
Notice that I "invented" the aliases "source" and "dest".
Now in your case the tables are on two different servers, so the match might not be as efficient, in some cases I know I fetch the remote rows to a third table (maybe even a temp table) on the local server before matching, but I'm guessing with the low number of rows you have it might not be as important. Obviously this also assumes that you really do have a working functional linked server that will accomplish this, and also you'll also need to test this for the very important reason that some random joe like me MIGHT NOT KNOW WHAT HE'S TALKING ABOUT!!!!
Also I'm assuming that you mean 17000 rows, not 17000 fields, if you really meant fields then this might be over my head LOL
What happens when there is no matching key to match on between the two tables? You might have to account for that case! Some folks like to use the "MERGE" statement which handles the needed updates, inserts (and even deletes if needed) based on whether the key matches or is missing in one table or the other. Other folks like to split the job up and first update, then insert the missing rows.
Obviously I'm giving you suggestions here, you must know that you are responsible for not losing data by messing up the update! Make sure to test and verify that what you are doing works as expected! Also make sure you have backups for your important data!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply