Thursday, March 22, 2012

Copying table data from a different database

I am attempting to copy table data from our live production database to our development database. To do this, I need to specify the name of the database in addition to the table name. I'm running into some problems that I'm hoping are just syntax related. Here is a portion of the code that runs fine when I don't specify a database...

>> SELECT name,description FROM cec_cecil_permissions

And here is a portion of the code I'm trying to run that results in an error...

>> SELECT name,description FROM devsql01.cec_cecil_permissions

When I run this, I get the following error...

>> Invalid object name 'devsql01.cec_cecil_permissions'.

My first thought was that I need to specify the complete path to the table within the database, so I ran the following code...

>> SELECT name,description FROM devsql01.Databases.Intranet.Tables.cec_cecil_permi ssions

This resulted in the following error...

>> The object name 'devsql01.Databases.Intranet.Tables.cec_cecil_perm issions' contains more than the maximum number of prefixes. The maximum is 3.

I can't reduce the number of prefixes because I'd be leaving out part of the path to the table. I tried changing every "." to a "\" but that resulted in a syntax error...

>> Incorrect syntax near '\'.

What do I need to do to make this work? Thanks in advance for your help!Never mind. I realized I was putting the server name in instead of the database name.

No comments:

Post a Comment