CODEWITHSUNDEEP
c#sql-serverbcp
Ivan Kuckir
Ivan Kuckir

Reputation: 2549

Copy data between different tables using BCP (bulk insert)

My progarm will get two tables, e.g. T1(A, B, C) and T2(B, C, D) (tables are on different SQL servers). T2 has one extra column (D) and one column missing (A).

I need to copy rows from T1 into T2 as fast as possible (probalby by using BCP utility). Column A should be omitted, column D should have some default value. My steps:

  1. bcp queryout - Select B, C, xxx as D from T1, into temporary file
  2. bcp temporary file into T2

How should my Select statement look like? How can I get default value for column D? Is there any other, simpler way to do such thing?

Upvotes: 0

Views: 1276

Answers (2)

Donal
Donal

Reputation: 32713

You can do this through SQL - using linked servers. You can use 4 dot notation to refer to the table in SQL. The notation is ServerName.DatabaseName.SchemaName.ObjectName. The default Schema is dbo, so to refer to a table the syntax would be: ServerName.DatabaseName.dbo.TableName.

You can run something like this on Server1:

INSERT INTO Server2.DBName.dbo.T2 (B, C, D)
SELECT A, B, 'Default Value' FROM Server1.DBName.dbo.T1

To set up the Linked Server from Server1 to Server2, run this:

USE master;
GO
EXEC sp_addlinkedserver 
   N'Server2',
   N'SQL Server';
GO

Upvotes: 1

Dbloch
Dbloch

Reputation: 2366

Since you have marked C#, I would suggest using SQLBulkCopy. Here is a sample project on CodeProject

Upvotes: 1

Related Questions