• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

copy sql2005 table to sql2000

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?
0
wppiexperts
Asked:
wppiexperts
  • 4
  • 3
  • 2
  • +1
1 Solution
 
BrandonGalderisiCommented:
Are you trying to copy the data, the structure, or both?
0
 
wppiexpertsAuthor Commented:
both
0
 
andy232Commented:
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.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
yatin_81Commented:
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.
0
 
BrandonGalderisiCommented:
yatin: You won't be truely COPYING the table.  It will have no constraints or indexes of any kind.
0
 
wppiexpertsAuthor 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

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

select * into NewTable from SQL2005Server.database.schema.table
0
 
yatin_81Commented:
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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now