Copying an SQL table from one Server to another

SQL Server Administration
,
1

Hi
I’m trying to copy a SQL table schema and data from Server A to Server B. The SQL table is just a reference table which hasn’t populated for some reason on Server B. Can anyone advise how the entire table could be copied across please on SQL Server 2000/2005.
So far we’ve tried a long-winded approach by copying the .mdb and .ldf files from Server A to Server B with a plan to then copy the table across into the Server B databse but we are having some difficulty re-attaching the database to Server B.
Please can anyone help?
Kind Regards
James

2

If you have a linked server, you can use a four-part name to reference the server for one of the tables.
Either:

--from ServerA
INSERT INTO ServerB.db_name.schema_name.table_name ( ... )
SELECT ...
FROM db_name.schema_name.table_name

or

--from ServerB
INSERT INTO db_name.schema_name.table_name ( ... )
SELECT ...
FROM ServerA.db_name.schema_name.table_name

1 Like
3

if not, you can use the generate script option to produce a script for both the table creation and data insertion, then run that on the target server.

1 Like
4

I would probably use bcp as it's fast and works great on 2000 and 2005.

bcp.exe dbname.dbo.tablename out c:\somefile.txt -Sserver1\instance1 -T -c -t, -r\r\n

bcp.exe dbname.dbo.tablename in c:\somefile.txt -Sserver2\instance1 -T -c -t, -r\r\n

You could also use DTS or the export wizard built into Enterprise Manager for 2000 or SSMS for 2005.

1 Like
5

From the Import/Export wizard you can perform this more easily. Otherwise try this:
SELECT * INTO destination FROM source

1 Like
6

If you do use SELECT/INTO, make sure that it's a very short run. While it causes only a tiny amount of schema locking when done on the same server instance, it will lock the source tables if you copy across a linked server. If you have a large amount of data, the Import/Export wizard would probably be fine. If you need to do it more than once, then the BCP method that Tara spoke of is probably the easiest to repeat and is also very fast.

7

Or, SSIS from one to the other.

8

Hi
I'm just testing out the Generate Scipts way of exporting and importing a 47MB table from a database on version
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015 12:32:14 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)
to
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor).

When I import I'm choosing from Source: Flat File Source, choose all the defaults

It starts to restore ok but during the action Executing this error appears :-

Operation stopped...

  • Initializing Data Flow Task (Success)

  • Initializing Connections (Success)

  • Setting SQL Command (Success)

  • Setting Source Connection (Success)

  • Setting Destination Connection (Success)

  • Validating (Success)

  • Prepare for Execute (Success)

  • Pre-execute (Success)
    Messages

    • Information 0x402090dc: Data Flow Task 1: The processing of file "C:\Users\Administrator\Desktop\James SQL Tables\MR_TABLE.txt" has started.
      (SQL Server Import and Export Wizard)

  • Executing (Error)
    Messages

    • Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 3" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
      (SQL Server Import and Export Wizard)

    • Error 0xc020902a: Data Flow Task 1: The "output column "Column 3" (22)" failed because truncation occurred, and the truncation row disposition on "output column "Column 3" (22)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
      (SQL Server Import and Export Wizard)

    • Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Users\Administrator\Desktop\James\MR_TABLE.txt" on data row 80.
      (SQL Server Import and Export Wizard)

    • Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - MR_TABLE_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
      (SQL Server Import and Export Wizard)

  • Copying to [dbo].[MR_TABLE] (Stopped)

  • Post-execute (Success)
    Messages

    • Information 0x402090dd: Data Flow Task 1: The processing of file "C:\Users\Administrator\Desktop\James H SQL Tables\MR_TABLE.txt" has ended.
      (SQL Server Import and Export Wizard)

    • Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "component "Destination - MR_TABLE" (86)" has started.
      (SQL Server Import and Export Wizard)

    • Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "component "Destination - MR_TABLE" (86)" has ended.
      (SQL Server Import and Export Wizard)

    • Information 0x4004300b: Data Flow Task 1: "component "Destination - MR_TABLE" (86)" wrote 0 rows.
      (SQL Server Import and Export Wizard)

Would anyone know to get around this. I'd just like to restore the date and not bothered about it restoring as the correct data type.

Cheers
James

9

Hi Tara
Does bcp work if exporting from SQL 2008 to SQL2008?

I exported using the Export option, then transferred the file to the destination server but when I run this it says I'm not logged in a SQL user

bcp.exe SUPPORT.dbo.MR_INVESTIGATIONS in MR_TABLE.txt -T -c -t -UAdministrator -Ppassword

I get error
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'TestServer\Administrator'.

Please advise - I'm still not able to transfer a table.

Cheers
James

10

It ok I think - I've tried a none administrator user which has outputted the file fine - now to check that it imports - thats the easy bit - now to import .......?

11

To answer your question regarding versions, yes it does work for SQL 2008 and even for much older versions too. Bcp.exe has been around for many, many years. It was nearly the only tool before DTS came along in 2000. So it was extensively used in 6.5 and 7.0.

You should add the -S parameter so that it is clear which SQL Server you are connecting to. Also, I use Windows authentication (-E) and not SQL authentication (-U and -P).

1 Like
12

Hi Tara
Thats brill - where've I been? - this is another command I've just come across.

So far nothing has worked to export and import a table so far with little effort. I've got a good feel about bcp so I'm going to watch a YouTube clip about it as well to get me going.

Cheers
James

1 Like
13

I sure wouldn't mess with bcp for only ~50MB. Use OPENROWSET if you don't want to create a permanent linked server.

14

One of the problems you are running into is that you are asking SQL Server to determine the data types of the table to be created.

Using the Import/Export Wizard - SQL Server will 'sample' a percentage of rows and base the columns data type on that sampling. When you run into truncation issues it generally means the sampling didn't hit the largest size on that column.

To avoid these types of issues I recommend creating the destination table yourself - making sure all data types match the source system data types. If that isn't possible then you need to insure that you are converting the source data to an appropriate data type before importing the data.

1 Like
15

For only ~50MB, I agree... especially if it's a "PULL" and not a "PUSH". That is, if the OPENROWSET is executed on the target server.