How to copy data from one table to another table in SQL Server

Question

We have 2 tables TableA and TableB. We have user name as common column in both the tables. I need to import data from Table B to Table A where the user name is same

Table A have user name, user id, user full name, user email columns
Table B have user name, user id , user full name, user email columns.

We have over 10000 records in Table B with all the details but we only have user name column in Table A. I need to update the remaining columns (user id, user full name, email) in Table A by copying it from table B.

How can we do this? Please help

Can you please post your table structure? It's easier to help and understand your request than reading paragraphs. — Paul Karam, Jul 11 '17 at 21:30
You should share whatever you have done till now. Dont expect others to work for you please. — MKR, Jul 11 '17 at 21:31
Possible duplicate of [Update a table using JOIN in SQL Server?](https://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server) — jmoerdyk, Jul 11 '17 at 21:36

score 1 · Answer 1 · answered Jul 11 '17 at 21:48

This should get you what you're looking for...

IF OBJECT_ID('tempdb..#TableA', 'U') IS NOT NULL 
DROP TABLE #TableA;

CREATE TABLE #TableA (
     UserID INT, 
     FullName VARCHAR(20), 
     Email VARCHAR(30)
    );

IF OBJECT_ID('tempdb..#TableB', 'U') IS NOT NULL 
DROP TABLE #TableB;

CREATE TABLE #TableB (
     UserID INT, 
     FullName VARCHAR(20), 
     Email VARCHAR(30)
    );

INSERT #TableA ( UserID, FullName, Email ) VALUES
    (1, 'Jane Doe', '[email protected]'),
    (2, 'John Doe', '[email protected]'),
    (3, 'Someone Else', '[email protected]');

INSERT #TableB (UserID) VALUES
    (1), (2), (3);

-- check the begin values in both tables...
SELECT * FROM #TableA ta;
SELECT * FROM #TableB tb;

--============================================

UPDATE tb SET 
    tb.UserID = ta.UserID, 
    tb.FullName = ta.FullName, 
    tb.Email = ta.Email
FROM
    #TableB tb
    JOIN #TableA ta
        ON tb.UserID = ta.UserID;


-- check the end values in both tables...
SELECT * FROM #TableA ta;
SELECT * FROM #TableB tb;

HTH, Jason

score 0 · Answer 2 · answered Jul 11 '17 at 22:07

Another SQL tool you could use is MERGE

--Create first table
CREATE TABLE #aa(
    username varchar(50),
    userid varchar(50),
    userfullname varchar(50),
    useremail varchar(50)
)
GO
--Create second table
CREATE TABLE #bb(
    username varchar(50),
    userid varchar(50),
    userfullname varchar(50),
    useremail varchar(50)
)
GO
--Insert all data into temp table #aa
INSERT #aa (username,userid,userfullname,useremail)
VALUES
    ('AAA','ID1','JOHN DOE','[email protected]'),
    ('BBB','ID2','BILLY BOB','[email protected]'),
    ('CCC','ID3','TOM HANKS','[email protected]'),
    ('DDD','ID4','CONNOR MCGREGOR','[email protected]'),
    ('EEE','ID5','FLOYD MAYWEATHER','[email protected]')
GO
--Insert only the username in temp table #bb
INSERT #bb (username)
VALUES
    ('AAA'),
    ('BBB'),
    ('CCC'),
    ('DDD'),
    ('EEE')
GO
--See #bb before
SELECT * FROM #bb

MERGE #bb AS TARGET
USING #aa AS SOURCE
ON TARGET.username = SOURCE.username
WHEN MATCHED
THEN UPDATE SET
    TARGET.userid = SOURCE.userid,
    TARGET.userfullname = SOURCE.userfullname,
    TARGET.useremail = SOURCE.useremail;
GO

--See #bb after
SELECT * FROM #bb

DROP TABLE #aa
DROP TABLE #bb

Before Merge

+----------+--------+--------------+-----------+
| username | userid | userfullname | useremail |
+----------+--------+--------------+-----------+
|  AAA     |  NULL  |    NULL      |   NULL    |
|  BBB     |  NULL  |    NULL      |   NULL    |
|  CCC     |  NULL  |    NULL      |   NULL    |
|  DDD     |  NULL  |    NULL      |   NULL    |
|  EEE     |  NULL  |    NULL      |   NULL    |
+----------+--------+--------------+-----------+

After Merge

+----------+--------+-------------------+----------------+
| username | userid |  userfullname     | useremail      |
+----------+--------+-------------------+----------------+
|  AAA     |  ID1   |  JOHN DOE         | [email protected]  |
|  BBB     |  ID2   |  BILLY BOB        | [email protected]  |
|  CCC     |  ID3   |  TOM HANKS        | [email protected]  |
|  DDD     |  ID4   |  CONNOR MCGREGOR  | [email protected]  |
|  EEE     |  ID5   |  FLOYD MAYWEATHER | [email protected]  |
+----------+--------+-------------------+----------------+

Hi We have over 10,000 records ...We cannot manually insert all the data — machman134, Jul 12 '17 at 17:20
@machman134 You will only need to use the block of code beginning with `MERGE` and ending with `GO`. You can copy and past the entire script to see an example of how it works. When you need to use it for real, `#bb` and `#aa` with your actual table names. Hope this helps. — Jason, Jul 12 '17 at 17:49
@machman134 the insert was only for the temp tables in the example. You shouldn't be inserting to do the actual `MERGE` — Jason, Jul 12 '17 at 17:50

How to copy data from one table to another table in SQL Server

2 Answers2