0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
UCDA2919
  • 1
  • 3

2 Answers2

0

To transfer data from one instance to another you have 3 choices:

  1. Using Linked Servers
  2. Using SQL Server Integration Services
  3. Using SQL Import and Export Wizards
Hadi
  • 36,233
  • 13
  • 65
  • 124
0

You can link the 2 servers and use a query to insert the data.

To link the servers you can use the MSSQL procedures, like so:

EXEC sp_addlinkedserver @server='ServerName1', @srvproduct='', @provider='SQLNCLI', @datasrc='10.10.10.10'
EXEC sp_addlinkedsrvlogin 'ServerName1', 'false', NULL, 'username', 'password'
 

where ServerName1 is the name of the remote SQL server and @datasrc would be the ip address/dns name

To copy the data you can use a simple query like this one:

insert into [ServerName1].DatabaseName.dbo.TableName(column1,column2)
select Column1, null from LocalTableName

It's an example of course, since you didn't post the table setups. You would probably need to adjust it to suit your needs.

foolish_humans
  • 63
  • 6
  • I have linked the servers as above and i am onto copying the data over with the following code insert into [ucmwebservices\SQLEXPRESS].CPM.dbo.fct_Project([CreateDate2]) select [CreateDate2] FROM fct_Project; however i am getting the error: 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. – UCDA2919 Mar 20 '19 at 15:15
  • This is because the column on the local server is nullable, so it can contain null values, but the column on the remote server is not nullable, so you cannot insert null values there. You can alter the column on the remote server to make it nullable and it should do the trick.. – foolish_humans Mar 21 '19 at 09:11
  • In both tables all column are inthe same format and this error is still appearing – UCDA2919 Mar 21 '19 at 09:53
  • well then maybe some of the other columns are not nullable, when you type insert into [ucmwebservices\SQLEXPRESS].CPM.dbo.fct_Project([CreateDate2]) select [CreateDate2] FROM fct_Project it will just insert values for a column named 'CreateDate2', the other columns would be null for all new rows... you can either enter values for all of the non-nullable columns or alter them to make them nullable. – foolish_humans Mar 21 '19 at 13:17