FiveTech Software tech support forums

Posted: **Tue Nov 20, 2018 11:01 am**

Hi,
What is the best instruction in other to copy one TABLE from one MS SQL database to another on another MS SQL SERVER ?

Thanks for your samples .

Philippe

Posted: **Tue Nov 20, 2018 1:49 pm**

https://stackoverflow.com/questions/360 ... -mangement

Code: Select all

SELECT * INTO NewTable FROM OldTable

or

Code: Select all

insert into newTable select * from oldTable

or

Code: Select all

insert into newTable (a,b,c) select a,b,c from oldTable

the first case creates a new table, in the other cases the table must already exist
the last case specifies the columns to copy, useful when one or more columns are not settable but automatic (like incremental or timestamp)

Posted: **Tue Nov 20, 2018 3:58 pm**

Thanks for this first anser .

It seem that tables are locate on the same DATABASE but how to do it with different databases that may be on different MS SQL Server .

I want to copy a table of product for one SERVER to another one .

Thanks

Philippe

Posted: **Wed Nov 21, 2018 8:07 am**

Copying table from one database (catalog) to another database on the same server:

Code: Select all

SELECT * INTO dbDest.dbo.NewTable FROM dbSrc.dbo.OldTable

Copying table from one server to another server.

Need to use OPENROWSET or LINKEDSERVER features of MS SQL. However, this is limited to cases where the source server is accessible to the destination server. (Eg. both servers located on the same network).
Please read more about the documentation on openrowset and linked servers.

Simple, though a bit slower, is to read from the source server and write to the destination server. This approach may not look sophisticated but works well and is not unduly slow.

This is a program I just tested for copying "customer" table from my local SQLEXPRESS server to a temporary table "#customer" on the cloud server provided by FWH for our test use.

Code: Select all

#include "fivewin.ch"

function Main()

   local oLocal   := FW_OpenAdoConnection( "MSSQL,SQLEXPRESS,FWH", .t. )
   local oCloud   := FW_MSSQLDB()
   local oRs, aRows, aStruct, cCol, cVal, cTable, cSql
   local lIdentityInsert := .f.
   local nBatch   := 100

   ? "Open Local Table"
   cTable   := "customer"
   oRs      := FW_OpenRecordSet( oLocal, "SELECT * FROM [" + cTable + "]" )
   aStruct  := FWAdoStruct( oRs )
   lIdentityInsert := ( AScan( aStruct, { |a| a[ 2 ] == "+" } ) > 0 )
   cCol  := "( " + FW_QuotedColSQL( FW_ArrayAsList( ArrTranspose( aStruct )[ 1 ] ) ) + " )"

   ? "Write to cloud server"
   FWAdoCreateTable( "#" + cTable, aStruct, oCloud )
   cSql  := "INSERT INTO [#" + cTable + "] " + cCol + " VALUES "
   if lIdentityInsert
      oCloud:Execute( "SET IDENTITY_INSERT [#" + cTable + "] ON" )
   endif

   oRs:MoveFirst()
   do while !oRs:Eof()
      aRows    := oRs:GetRows( nBatch )
      if HarbourNew()
         aRows    := ArrTransposeQ( aRows )
      endif
      cVal     := FW_ValToSQL( aRows )
      cVal     := SubStr( cVal, 2, Len( cVal ) - 2 )
      oCloud:Execute( cSql + cVal )
      SysRefresh()
   enddo
   oRs:MoveFirst()
   oRs:Close()
   if lIdentityInsert
      oCloud:Execute( "SET IDENTITY_INSERT [#" + cTable + "] OFF" )
   endif

   ? "Verify"
   oRs      := FW_OpenRecordSet( oCloud, "SELECT * FROM [#" + cTable + "]" )
   XBROWSER oRs
   oRs:Close()

   oCloud:Close()
   oLocal:Close()

return nil

You can test by changing oLocal to your local server and cTable to your table and test this program as it is.
You can also use your destination server by changing oCloud to your destination server and run the same program.

This is the logic I use when required and find the performance to be satisfactory in general.

FiveTech Software tech support forums

COPY FILE MS SQL Server

COPY FILE MS SQL Server

Re: COPY FILE MS SQL Server

Re: COPY FILE MS SQL Server

Re: COPY FILE MS SQL Server