如何将数据从一个表复制到SQL Server中的另一个表

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

我们有2个表TableA和TableB。我们在两个表中都有用户名作为公共列。我需要将表B中的数据导入表A,其中用户名相同

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

表A具有用户名,用户ID,用户全名,用户电子邮件列

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

表B具有用户名,用户ID,用户全名,用户电子邮件列。

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.

表B中有超过10000条记录,包含所有详细信息,但我们在表A中只有用户名列。我需要通过从表B复制表A中的剩余列(用户ID,用户全名,电子邮件)。

How can we do this? Please help

我们应该怎么做?请帮忙

2 个解决方案

#1

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', 'jd@Gmail.com'),
    (2, 'John Doe', 'John_doe@yahoo.com'),
    (3, 'Someone Else', 's.Else@hotmail.com');

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

#2

Another SQL tool you could use is MERGE

您可以使用的另一个SQL工具是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','AAA@YAHOO.COM'),
    ('BBB','ID2','BILLY BOB','BBB@YAHOO.COM'),
    ('CCC','ID3','TOM HANKS','CCC@GMAIL.COM'),
    ('DDD','ID4','CONNOR MCGREGOR','DDD@GMAIL.COM'),
    ('EEE','ID5','FLOYD MAYWEATHER','EEE@GMAIL.COM')
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         | AAA@YAHOO.COM  |
|  BBB     |  ID2   |  BILLY BOB        | BBB@YAHOO.COM  |
|  CCC     |  ID3   |  TOM HANKS        | CCC@GMAIL.COM  |
|  DDD     |  ID4   |  CONNOR MCGREGOR  | DDD@GMAIL.COM  |
|  EEE     |  ID5   |  FLOYD MAYWEATHER | EEE@GMAIL.COM  |
+----------+--------+-------------------+----------------+

#1