We help IT Professionals succeed at work.

copy sql2005 table to sql2000

wppiexperts
wppiexperts asked
on
Medium Priority
309 Views
Last Modified: 2012-05-06
I need to copy a table from a sql2005 database to sql2000. My guess is that I will need to do this from the 2005 management studio and not from 2000. How can I move this table?
Comment
Watch Question

Are you trying to copy the data, the structure, or both?

Author

Commented:
both

Commented:
There are actually a lot of ways to do this, and you can use SQL 2000 or 2005 to do this.  Personally, out of old habit, I would use SQL 2000 Import Export Wizard.  It's fast, time-tested and quick.  But SSIS, Bulk-Insert copy, openquery (using linked server), bcp & scripting are also all  valid ways.

Commented:
Use this statement:
select *  into servername.databasename.dbo.table1 from table2
where table1 is your sql2005 table and table2 is your sql2000 table.
Write this statement in SQL2005 Management Studio.
yatin: You won't be truely COPYING the table.  It will have no constraints or indexes of any kind.

Author

Commented:
I think the command:
select *  into servername.databasename.dbo.table1 from table2

would do the trick, but I'm getting the error:
the object name 'servername.databasename.dbo.table1' contains more than the max number of prefixes...the maximum is 2

not sure how else to define the other server/db/table

You have to have the server pre-created as a linked server.

Author

Commented:
by pre-created....do you mean linked (and listed within the object explorer)? if so...i've done that
You may need to execute that from the 2000 server.

select * into NewTable from SQL2005Server.database.schema.table
Commented:
You may do it from any of the SQL Server 2005 or 2000, but 2005 would be better because it has backward compatibility. And as the BrandonGalderisi said you wont be able to copy the contraint, relationship and index. Thats true. Only the structure and data will be copied.
You might not have linked the servers properly. Can you see the servername in Object Explorer-> <ServerName> -> Server Objects -> Linked Servers ? If not then right click on Linked Server and click on "New Linked Server". Post the results.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.