Copy data from table1.fieldA to table2.fieldB

Question

Copy data from table1.fieldA to table2.fieldB

bdorminy

Grasshopper

Points: 13
More actions
January 27, 2011 at 9:26 am

#244367

I have created a database with employee information. One table has information from an HR database, the other from our Active Directory domain. Both tables have an employee number column. The HR table has every employee's number but the AD table is missing some numbers. Is there a way I can update (copy) the AD table EmployeeNumber field with the data from the HR EmployeeNumber field?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Use BOL (Books On Line) the help file for SQL Server.. MERGE statement

In SQL Server 2008, you can perform insert, update, or delete operations in a single statement using the MERGE statement. The MERGE statement allows you to join a data source with a target table or view, and then perform multiple actions against the target based on the results of that join. For example, you can use the MERGE statement to perform the following operations:
Conditionally insert or update rows in a target table.
If the row exists in the target table, update one or more columns; otherwise, insert the data into a new row.
Synchronize two tables.
Insert, update, or delete rows in a target table based on differences with the source data.

Here is some sample code, again from BOL

-- MERGE statement with the join conditions specified correctly.

USE tempdb;

GO

BEGIN TRAN;

MERGE Target AS T

USING Source AS S

ON (T.EmployeeID = S.EmployeeID)

WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'

THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)

WHEN MATCHED

THEN UPDATE SET T.EmployeeName = S.EmployeeName

WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'

THEN DELETE

OUTPUT $action, inserted.*, deleted.*;

ROLLBACK TRAN;

GO

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

terrykzncs Ten Centuries Points: 1324 More actions · Answer 2

Hi

Just wondering if there is a way to link the 2 tables? Is EmployeeNumber the primary key? I'm sure its a fairly easy update script if the 2 tables were first joined.

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 3

Hi,

If you can join two tables with employerno. then you can easly update it by joining both table with nullify the other table its like join with null conditions.

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 4

Check this link :

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/