INSERT/UPDATE/DELETE each row SQL

Question

I have the following data in a table TABLE1

SessionID
S1
S2
S3

i have the following data in another TABLE2

EmployeeID | Session
1          | NULL
2          | NULL
3          | NULL

What i would like to do is update/Insert each row example

UPDATE table2 SET Session= (SELECT SessionID FROM TABLE1)
INSERT INTO( COPY each row and insert 2nd row session id from table1)

The Expected Result: TABLE2

EmployeeID | Session
1          | S1
2          | S1
3          | S1
1          | S2
2          | S2
3          | S2
1          | S3
2          | S3
3          | S3

Any insight will help.

Thank you.

Raymond Wong · Accepted Answer · 2012-05-25 18:50:38Z

2

It sounds like you want a row in TABLE2 for every combination of each row currently in TABLE2 and each row in TABLE1. If so...

BEGIN TRAN

SELECT * INTO #temp FROM TABLE2

DELETE TABLE2

INSERT TABLE2
(
    EmployeeID,
    Session
)
SELECT
    temp.EmployeeID,
    TABLE1.SessionID
FROM TABLE1 CROSS JOIN #temp temp

DROP #TEMP

COMMIT TRAN

edited May 25, 2012 at 18:50

Raymond Wong

457 bronze badges

answered May 25, 2012 at 6:51

Daniel Renshaw

34.3k8 gold badges78 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raymond Wong Over a year ago

Hi Daniel ,your way is works. but i face the primary key problem . in the table2 , my employeeid is primary key .so i have tried make session and employee id as candidate key but it fail because my table 2 session is null. any suggestion?

Daniel Renshaw Over a year ago

If EmployeeID must be the primary key of TABLE2 then you simply can't do what you want since it will require duplicate values in that column. If you can remove the current key and replace it with a new composite key covering EmployeeID and Session then it will work as long as you never have duplicate Session values for the same EmployeeID. You'll be able to have one NULL value per EmployeeID but no more than that.

detale · Accepted Answer · 2012-05-25 06:56:15Z

1

I would suggest creating a separate Employee table. To insert data to your EmployeeSession (TABLE2):

INSERT INTO EmployeeSession ( EmployeeID, SessionID )
    SELECT Employee.ID, [Session].ID
    FROM Employee, [Session]

answered May 25, 2012 at 6:56

detale

13k6 gold badges44 silver badges43 bronze badges

1 Comment

Raymond Wong Over a year ago

thanks but i think daniel's answer is more suitable for my situation

dan radu · Accepted Answer · 2012-05-25 07:30:49Z

0

I think the MERGE statement helps:

MERGE INTO TABLE2 t2
    USING (SELECT t2.EmployeeID, t1.SessionID
FROM TABLE2 t2
CROSS JOIN TABLE1 t1) t
        ON t2.EmployeeID = t.EmployeeID AND t2.Session = t.SessionID
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (EmployeeID, Session) VALUES(t.EmployeeID, t.SessionID)
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE
;

answered May 25, 2012 at 7:30

dan radu

2,7824 gold badges20 silver badges23 bronze badges

Collectives™ on Stack Overflow

INSERT/UPDATE/DELETE each row SQL

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related