CODEWITHSUNDEEP
sqlsql-server
user3759612
user3759612

Reputation: 21

SQL Server stored procedure to insert/update data from one source to multiple destination tables

This is my source table

Book | Employee | StartDate | EndDate
-------------------------------------
ABC     PQR       02/02/2014  06/06/2014
QWE     MNO       03/03/2014  07/07/2014

This is the DB schema, where this data should fit in...

Book table

BookID | BookName
-----------------
1        ABC
2        QWE

Employee table

EmployeeID | EmployeeName
-------------------------
1             PQR
2             MNO

BookEmployee table

BookID | EmployeeID | StartDate | EndDate
------------------------------------------
1         1           02/02/2014  06/06/2014
2         2           03/03/2014  07/07/2014

Note: if Book and Employee already exist in the Book and Employee tables, then we should not insert them, instead use their ID in the BookEmployee table

Upvotes: 0

Views: 290

Answers (1)

Matthew Haugen
Matthew Haugen

Reputation: 13296

I would just do three queries.

INSERT INTO [Book] (BookName)
    SELECT DISTINCT Book
    FROM [Source]
    WHERE Book NOT IN (SELECT BookName FROM Book)

INSERT INTO [Employee] (EmployeeName)
    SELECT DISTINCT Employee
    FROM [Source]
    WHERE Employee NOT IN (SELECT EmployeeName FROM Employee)

INSERT INTO [BookEmployee] (BookID, EmployeeID, StartDate, EndDate)
    SELECT Book.ID, Employee.ID, Source.StartDate, Source.EndDate
    FROM [Source]
    INNER JOIN Book ON Book.BookName = Source.Book
    INNER JOIN Employee ON Employee.EmployeeName = Source.Employee

You could run those in a transaction if you're doing it a lot. You could also add in some MERGE behavior, but I wouldn't bother since you don't have anything more than one column to insert. I also didn't do anything as far as merging behavior is concerned for the last query, but I'm sure this will get you enough of a start to make it work.

But yeah, you won't be able to do it all "at once," per se, and even if you could, this way is significantly more readable than that would be.

Upvotes: 1

Related Questions