How to import data from a view in another database (in another server) into a table in SQL Server 2000?

Question

I was thinking about using bcp command to solve the user authentication, but does a bcp command capable to import to a table in my database? By the way, I am using SQL Server 2000 environment.

Here's the code I have got so far:

SET @Command = 'bcp "SELECT vwTest.* from [myserver\sql].test.dbo.vwTest" queryout dbo.Test -C ACP -c -r \n -t ";" -S myserver\sql -Umyuser -Puser1'

EXEC master.dbo.xp_cmdshell @Command

Answer 1

SURE !!

Use this command (adopt it for your needs) on your source machine:

bcp database.dbo.viewname out c:\temp\viewname.bcp

and then import the data back into your destination system using:

bcp newdatabase.dbo.importtable in c:\temp\viewname.bcp 
    -c -S(servername) -U(username) -P(password)

That should grab the contents of your "viewname" from the source server, put it in a temporary file, and insert that file back into the new database on the new server.

Typically, you would load those data rows into a new, temporary staging table, and form there, use T-SQL or other means to insert that data into your actual tables.

Check out the MSDN documentation on bcp in SQL Server 2000 for details on all those switches and their meanings.

Answer 2

Based on the comparison of BCP, BULK INSERT, OPENROWSET (infer Linked Server) here:

...the bcp utility runs out-of-process. To move data across process memory spaces, bcp must use inter-process data marshaling. Inter-process data marshaling is the process of converting parameters of a method call into a stream of bytes. This can add significant load to the processor. However, because both bcp parses the data and convert data into native storage format in the client process, they can offload parsing and data conversion from the SQL Server process.

...bcp possibly isn't the most efficient means of transferring data. You might be better off to:

1. Create a linked server instance to the other database
2. Use INSERT statements, so that the tables are populated based on records from the database exposed in the linked server instance.

Besides potentially being more efficient, you only need to setup the linked server instance once versus running BCP to create output scripts every time you want to move data.

Mind that the linked server instance is based on a user on the other database, so permissions to the other database are based on that users' permissions.