best way to import data from SQL Server 7.0 to 2005

Question

I have a table in a SQL Server 7.0 database with columns like:

Column_1      varchar(10)     null
Column_2      int             null
Column_3      varchar(15)     null
Column_4      float(53)       null
Column_5      float(53)       null
Column_6      text(16)        null
Column_7      text(16)        null
Column_8      varchar(25)     null
Column_9      datetime        null

and I need to move it to a table in SQL Server 2005. What is the quickest and easiest way? I would generally just INSERT (cols...) SELECT cols.. FROM OtherDatabase.dbo.table but I can't do that because it is SQL Server 7.0.

Answer 1

Although I haven't tried this (don't have a SQL 7.0 install handy :-)) you should be able to do something like below to setup a linked server and just get the data from a four part name query.

http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/7b24b699-f8b9-48e8-ba78-b037fd3b1d21/