0down votefavorite

I am trying to update a table with info from another table on the same db with same table name. I just want the info to be the same , no primary key or constraint involve just a straight replacement of records and I keep getting errors WITH THE TABLE not being recignize. below is my query: UPDATE VNDFIL SET EOBTYP =  VNDFIL.EOBTYP,  EDI_X12_835_VERSION =  VNDFIL.EDI_X12_835_VERSION FROM AGERECOVERY WHERE VNDFIL.EOBTYP = VNDFIL.EOBTYP AND VNDFIL .EDI_X12_835_VERSION = VNDFIL.EDI_X12_835_VERSION

• Edited by rotary Outsourcing Tuesday, February 24, 2015 6:21 PM

If you want just a couple of columns:

CREATE TABLE [dbo].[empl](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[fname] [varchar](25) NULL,
	[lname] [varchar](25) NULL
)

CREATE TABLE [dbo].[empl2](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[fname] [varchar](25) NULL,
	[lname] [varchar](25) NULL
)

INSERT INTO empl SELECT 'J','Smith'
INSERT INTO empl2 SELECT 'John','Smithson'

UPDATE empl
SET empl.fname = empl2.fname,
empl.lname = empl2.lname
FROM empl2
WHERE empl.id = empl2.id

SELECT * FROM empl

If you want the exact same for all fields:

drop table empl;
select * into empl from empl2;

• Edited by Ami2013 Tuesday, February 24, 2015 9:11 PM

I am going to assume that you have the same table name in different schemas... and that 'AGERECOVERY' is the source schema... and that you failed to include the table name in the FROM clause...

"FROM

AGERECOVERY.VNDFIL"

Hi,

I have used the example of Ami2013. Maybe this will help, I have written a merge statement for an update and insert in one statement.

CREATE TABLE #EMP1(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[fname] [varchar](25) NULL,
	[lname] [varchar](25) NULL
)

CREATE TABLE #EMP2(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[fname] [varchar](25) NULL,
	[lname] [varchar](25) NULL
)

INSERT INTO #EMP1 SELECT 'J','Smith'
INSERT INTO #EMP2 SELECT 'John','Smithson'

MERGE INTO #EMP1 AS TGT
USING #EMP2 AS SRC
	ON TGT.ID=SRC.ID
WHEN MATCHED THEN
	UPDATE SET
	TGT.FNAME=SRC.FNAME,
	TGT.LNAME=SRC.LNAME
WHEN NOT MATCHED THEN
	INSERT (FNAME,LNAME)
	VALUES(SRC.FNAME,SRC.LNAME);

Regards,

Reshma

Hi rotary,

If those two same named tables are in the same database then they have to be in different schemas. If you mean they are in the same server instance, then they may be in different databases, besides the "table not being recognized" error, anyway you should use the fully qualified table names, that is database.Schema.Table(If across instances, ServerName should be prefixed) to avoid the table unrecognized error.

Using Identifiers As Object Names

With the fully qualified names, your update statement can be like below.

UPDATE
db1.schema1.VNDFIL
SET EOBTYP =  srcTbl.EOBTYP,  EDI_X12_835_VERSION =  srcTbl.EDI_X12_835_VERSION
FROM
db1.schema2.VNDFIL srcTbl
WHERE
db1.schema1.VNDFIL.EOBTYP = srcTbl.VNDFIL.EOBTYP AND
db1.schema1.VNDFIL.EDI_X12_835_VERSION = srcTbl.VNDFIL.EDI_X12_835_VERSION

If you have any question, feel free to let me

Merge VNDFIL tgt
Using  AGERECOVERY src On  (tgt.EOBTYP = src.EOBTYP
       AND tgt.EDI_X12_835_VERSION = src.EDI_X12_835_VERSION) 

When Matched Then 
UPDATE 
SET EOBTYP = src.EOBTYP
,EDI_X12_835_VERSION = src.EDI_X12_835_VERSION ;

Hello all,

I need a help regarding the script. I need to generate a script where it should check the tables of a database from one server to another server(For Eg:- In server 1 database "XYZ" has 10 tables then in server 2 database "XYZ"also has 10 tables). If the tables matches then, truncate table data and insert the data from one  server 1 database XYZ to  server 2 database XYZ. If tables does not matches,(for eg;-  server 2 database "XYZ"also has 8 tables, then it should check and create that tables which are not there.

Thanks,

Sidhu

You already have 2 active and duplicate threads on this same topic.  It does no good to reply to an older and answered thread with an unrelated question. 

OH sorry about that. I am new to this so where I can continue my thread here or the old one

Hi siddu_123,

Please post a new thread with your question.