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
FiveTech Support Forums
FiveWin / Harbour / xBase community
Board index FiveWin for Harbour/xHarbour COPY FILE MS SQL Server
https://stackoverflow.com/questions/360 ... -mangement
or
or
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)
SELECT * INTO NewTable FROM OldTableor
insert into newTable select * from oldTableor
insert into newTable (a,b,c) select a,b,c from oldTablethe 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)
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
Copying table from one database (catalog) to another database on the same server:
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.
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.
SELECT * INTO dbDest.dbo.NewTable FROM dbSrc.dbo.OldTableCopying 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.
#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 nilYou 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.
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India