How to update data from temp table into real table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to update data from temp table into real table

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-05 : 11:12:09

How to update data from temp table to real table.
I tried this but get error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "#temp.ID" could not be bound.

Create table tbl1
(ID INT,
email varchar(255),
domain_name varchar(50),
email_name varchar(50)
)
go
INSERT INTO tbl1
SELECT 1, 'Aa Bb <abb@xyz.com>; Pr, Eda <peda@abc.com>; R Man <rman@xyz.com>; Kim Park <kpark@mmm.com',NULL,NULL

INSERT INTO tbl1
SELECT 2, 'Ra, Ja <rja@cc.com>; Rr Gg <rgg@cc.com>',NULL,NULL

INSERT INTO tbl1
SELECT 3, '<nimnn@mmm.com>; <myjob@mycompany.com>',NULL,NULL
go

Create table #temp
(ID INT,
domain_name varchar(50),
email_name varchar(50)
)
go
INSERT INTO #temp
SELECT 1, 'xyz.com;abc.com;mm.com','Aa Bb;Pr;Eda;R Man; Kim Park'

INSERT INTO #temp
SELECT 2, 'cc.com','Ra;Ja;Rr Gg'

INSERT INTO #temp
SELECT 3, 'mmm.com',NULL
go

Update Mydb.dbo.tbl1 SET domain_name=#temp.domain_name, email_name=#temp.email_name WHERE ID=#temp.ID
go
Drop table tbl1
Drop table #temp

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-03-05 : 11:39:10

You were missing a FROM clause. I also added a table prefix to tbl1 in your WHERE clause because the ID column was ambiguous:

UPDATE tbl1
SET domain_name=#temp.domain_name, email_name=#temp.email_name
FROM tbl1, #temp
WHERE tbl1.ID=#temp.ID

Some days you're the dog, and some days you're the fire hydrant.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-05 : 11:43:18

thankx perfect job

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-03-05 : 11:51:11

No problem :)

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources