Copying data from a SQL2000 db on different server to SQL2005 db

• Mary J Parkhouse

  SSC Rookie

  Points: 26

  More actions

  June 17, 2008 at 4:44 pm

  #192704

  I have the following insert statement:

  insert into Resident(Identifier_Type,Identifier,Full_Name,Dob,DL_Number,DL_State,Change_User,Change_Date,Change_PropID)

  select N'SS',Social,Full_Name,DOB,DL_Number,N'TX',Change_User,Change_Date,Change_PropID from N'haa-sql1.imisTest.dbo.Resident'

  I'm trying to copy data from a server name haa-sql1 that has a default instance of SQL 2000 installed on it. The database is named imisTest, created by the dbo and the table is named Resident.

  I tried using the SQL Import Wizard, the Copy data from one or more tables of views. This option throws an error because there is an identity field in the Resident table on the SQL 2005 database.

  I'm now trying to insert the data. I'm getting the following error:

  Incorrect syntax near 'haa-sql1.imisTest.dbo.Resident'.

  If I take the N'....' off, I get this error:

  Incorrect syntax near '-'.

  I'm going to need to do this with several tables from the SQL 2000 server, so any help on how to accomplish this would be greatly appreciated.

  Thanks

• ksullivan

  SSCrazy

  Points: 2346

  More actions

  June 17, 2008 at 7:16 pm

  #830136

  Try brackets around your servername

  insert into Resident(Identifier_Type,Identifier,Full_Name,Dob,DL_Number,DL_State,Change_User,Change_Date,Change_PropID)

  select N'SS',Social,Full_Name,DOB,DL_Number,N'TX',Change_User,Change_Date,Change_PropID from [haa-sql1].imisTest.dbo.Resident