June 17, 2010 at 7:57 am
Hello Everyone;
I have 2 SQL Server (one is 2008 the other 2005 - both Standard Versions)
I need to update a table in a DB on the 2005 instance with records from a table on the 2008 instance.
This is what I have:
IF EXISTS (select * from tempdb.sys.objects where name = '#temp_VerificationUpdate')
DROP TABLE [dbo].[#temp_VerificationUpdate]
-------------------
create table #temp_VerificationUpdate (EnrollmentID varchar(100), VerificationID nvarchar(50))
-------------------
insert into #temp_VerificationUpdate
(EnrollmentID, VerificationID)
select EnrollmentID, VerificationID from DB..IDENTITY_CARD
-------------------
update [adm-v-sql\sql2005].[Gas_Test].[dbo].[Verify]
set [adm-v-sql\sql2005].[Gas_Test].[dbo].[Verify].[verificationID] = #temp_VerificationUpdate.VerificationID
from
#temp_VerificationUpdate
inner join
[adm-v-sql\sql2005].[Gas_Test].[dbo].[Verify]
on
#temp_VerificationUpdate.EnrollmentID = [adm-v-sql\sql2005].[Gas_Test].[dbo].Verify.EnrollmentID
--------------------
I get the message:
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "adm-v-sql\sql2005.Gas_Test.dbo.Verify.EnrollmentID" could not be bound.
--Just wanted to add that the LINKED SERVER works as I do an insert with another script from the same 2008 server to the 2005 server - same databases.
Any help is appreciated
Cheers
Ian
June 17, 2010 at 8:46 am
Thanks so much for your reply 🙂
I was just coming back to say I did it this way and it worked 🙂
update a
set a.[verificationID] = #temp_VerificationUpdate.VerificationID
from
#temp_VerificationUpdate
inner join
[adm-v-sql\sql2005].[Gas_Test].[dbo].[Verify] a
on
#temp_VerificationUpdate.EnrollmentID = a.[EnrollmentID]
Again thanks so very much
Cheers
Ian
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply