There are a number of options, but it all depends on you requirements.
If you are wanting this to be a regular things, then it maybe worth investigating doing this in SSIS, as advanced tools are available to Extract, Transform and Load data.
answered Jun 02, 2010 at 02:27 AM
by far the simplest is the use of SELECT INTO as Jonlee suggests. To do this you will need to create a linked server object on the 2008 instance referring to the 2005 instance. you then simply write a select statement from the 2008 database such as
This will select fname and sname columns for all the rows in the 2005 database and create a table in the 2008 database with that data in it.
If the table already exists in the 2008 database then use
It depends on whether you have a need to repeat this process, something more configurable like SSIS qould be the best solution to an ongoing requirement. Again, something Jonlee mentions.
answered Jun 02, 2010 at 04:41 AM
If you're attempting to upgrade the database from 2005 to 2008, simply take a backup of the 2005 database and then restore it on 2008 system. Upgrade the compatibility level of the database and manually update the statistics and you'll be good to go.
If you're just moving bits and pieces of the database or the data, the other guys have it well covered.
answered Jun 02, 2010 at 09:30 AM
Grant Fritchey ♦♦