Transferring data from table 1 to table 2

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

Transferring data from table 1 to table 2

Author

Topic

aniko
Starting Member

25 Posts

Posted - 2010-09-24 : 01:34:29

Hi guys,

Great forum! I have a question regarding two tables I have in my database. Each contains suburb, state and postcode information. The problem is table 1 has a blank postcode fields (no contents at all)

What I would like to do is perform a WHERE clause to check Table1.Suburb = Table2.Suburb AND Table1.State = Table2.State. Where there is a "match" I would like to transfer the postcodes from Table 2 into Table 1.

I've looked at various functions such as SELECT INTO but for the life of me I can't seem to figure it out.

Is someone able to please assist?!

Thanks in advance.

Devart
Posting Yak Master

102 Posts

Posted - 2010-09-24 : 01:56:58

Hi,

For example:

UPDATE t1
SET postcode=t2.postcode
FROM table_1 t1
INNER JOIN table_2 t2
ON t1.suburb=t2.suburb AND t1.state=t2.state

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

aniko
Starting Member

25 Posts

Posted - 2010-09-24 : 02:37:55

Thank you for your prompt reply!

I'm getting an error "Invalid object name 'table1'.

I don't quite get:
FROM table_1 t1
INNER JOIN table_2 t2

Is it not just "FROM t1". If t1 is the table name, what is table_1 ?

aniko
Starting Member

25 Posts

Posted - 2010-09-24 : 02:43:26

My bad. This seems to work.

You're a superstar Devart!

Devart
Posting Yak Master

102 Posts

Posted - 2010-09-24 : 02:44:32

"table_1" and "table_2" - your tables.
"t1" is alias for the table_1...

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources