CODEWITHSUNDEEP
sqlsql-serversql-server-2008
rimboche
rimboche

Reputation: 87

Copy data on one table to another

I need to transfer tbl_products from system 1 to system 2 that means both system have same DB and same tbl_products.Need to get the same data in system 1's database to system 2's tbl_products.

I had used COPY method in PostgreSQL to export and import data as csv.

In system 1

Copy (select * from tbl_products) to 'D:\AppFolder\DataTran\tbl_products).csv' with csv headers;

transferring tbl_product.csv via internet and

In system 2

Delete from tbl_products:
copy tbl_products from 'D:\AppFolder\DataTran\tbl_products).csv' with csv headers;

Now I need to implement this same method using SQL Server

Note: Both computer is not connected.

Upvotes: 0

Views: 272

Answers (3)

gofr1
gofr1

Reputation: 15997

We use OLE Automation Object to write in csv:

DECLARE @Var as xml --or nvarchar(max)
SELECT @Var= --some content yoy want to write in file

DECLARE @FSO int
DECLARE @hr int
DECLARE @src varchar(255)
DECLARE @desc varchar(255)
DECLARE @oFile int
DECLARE @filename nvarchar(max)='D:\Reports\our.csv';

-- Create OLE Automation Object
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT 
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END 

-- Create the file
EXEC @hr = sp_OAMethod @FSO, 'CreateTextFile', @oFile OUT, @filename, 8 , True
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT 
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END
--Here we put content in file
EXEC @hr = sp_OAMethod @oFile, 'Write', NULL, @Var
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT 
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

-- Clear used objects
EXEC @hr = sp_OADestroy @FSO
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT 
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END
EXEC @hr = sp_OADestroy @oFile
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @oFile, @src OUT, @desc OUT 
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
        RETURN
END

So you just need to put all the table in @Var, or write a while loop with taking every string and putting it into file, or... whatever you want.

To load csv-data on server you can use:

INSERT INTO dbo.YourTable
SELECT a.* FROM OPENROWSET( BULK 'D:\our.csv', FORMATFILE = 'D:\our.fmt') AS a;

The sample of our.fmt (it's file that describes the fields in csv)

9.0
4
1  SQLCHAR  0  50 ";"        1  Field1                SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  50 ";"        2  Field2                SQL_Latin1_General_Cp437_BIN
3  SQLCHAR  0  50 ";"        3  Field3                SQL_Latin1_General_Cp437_BIN
4  SQLCHAR  0  500 "\r\n"      4  Field4        SQL_Latin1_General_Cp437_BIN

You can find description of *.fmt here .

Upvotes: 1

Anuj Tripathi
Anuj Tripathi

Reputation: 2281

Though, Gordon answer is valid for your question as it is one of the fastest method to copy table from one server to another. But you can also try an alternate approach to do the same. in a SSIS (SQL Server Integration Services) package, you can transfer data between two server via data flow task without establishing linked server connection.

If you are new to SSIS than I recommend you to just use import-export wizard in SSMS to transfer data from one server to another. You can also generate SSIS package, in case, if it is repetitive task. You will see below step while working on import export wizard

enter image description here

TSQL solution

You can use OPENROWSET or OPENDATASOURCE to transfer data between two servers without creating any linked server. This may require your to provide username and password.

You can use it via Windows authentication

 SELECT *
INTO YourDbName.SchemaName.TableName
FROM    OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;Integrated Security=SSPI').RemoteDbName.RemoteSchemaName.RemoteTableName

or via SQL authentication

SELECT  *
INTO YourDbName.SchemaName.TableName
FROM    OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;user id=username;password=password).RemoteDbName.RemoteSchemaName.RemoteTableName

As a side note, OPENROWSET supports bulk operation.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

In SQL Server, you would more likely set up a db link between the two servers (this is easy, although the documentation is rather long).

Then, you simply do:

select t.*
into <local_table>
from <remote_link>.<database>.<schema>.<table>;

There is no need to go through CSV for this purpose.

Upvotes: 5

Related Questions