Tuesday, December 24, 2013

Move data quickly using linked server

I have lots of testing data on several virtual servers. Being lazy, I did not want to have to install the sample program (which then installs the sample data) on my local SQL Server. To save time I used a linked server + INSERT INTO tableName SELECT Col1, Col2 FROM [linkedservername].[databasename].[tablename]

Here's how to do it:
1. I had to add a pointer in the hosts file to the virtual machine's IP address.
2. In SSMS > Server Objects > Linked Servers
3. Right-click, New Linked Server
4.
Linked server: <server\instance>
Change server type to SQL Server
Click on Security, choose whatever for local login, then a valid login in Remote User / Remote Password
Click on Server Options, make sure Data Access = true
5. OK
6. Test Connection

Now you can start moving data quickly from the other server to your local instance. For example, this is moving records from the Dynamics GP SOP Payments Line table.

INSERT INTO TWO..SOP10103
SELECT [SOPTYPE]
      ,[SOPNUMBE]
      ,[SEQNUMBR]
      ,[PYMTTYPE]
      ,[DOCNUMBR]
      ,[RMDTYPAL]
      ,[CHEKBKID]
      ,[CHEKNMBR]
      ,[CARDNAME]
      ,[RCTNCCRD]
      ,[AUTHCODE]
      ,[AMNTPAID]
      ,[OAMTPAID]
      ,[AMNTREMA]
      ,[OAMNTREM]
      ,[DOCDATE]
      ,[EXPNDATE]
      ,[CURNCYID]
      ,[CURRNIDX]
      ,[TRXSORCE]
      ,[DEPSTATS]
      ,[DELETE1]
      ,[GLPOSTDT]
      ,[CASHINDEX]
      ,[DEPINDEX]
      ,[EFTFLAG]
  FROM [NEWHURA\GP2010].[GPDAT].[dbo].[SOP10103]

No comments:

Post a Comment

There was an error in this gadget