How to add multiple rows to a table at once

Let’s face it, most of the apps in the galaxy we live in are data bound. I don’t write apps that use sockets and presumably most of you don’t either, correct?

Now, in our day to day lives, we generally use a database of sorts, may it be SQL Server, MySQL, Oracle, PostgreSQL etc. all of which are relational. You have heard the famous cliche, “There are multiple ways to skin a cat”. Well, let’s not go there…suffice it to say, there are many solutions to a problem.

Without rambling too much, let’s get to the point. What I have below is a simple Entity Relational Diagram. To illustrate my point, I am attempting to add records into the Composite table from Magazine, Article and Author tables like so.

INSERT INTO Composite (Magazine, Title, PublishedDate, AuthorName)
    (SELECT 
        m.[Name], 
        a.Title,
        a.PublishDate,
        au.[Name]
    FROM
        Magazine m 
        INNER JOIN Article a ON m.MagazineId = a.MagazineId
        INNER JOIN Author au ON a.AuthorId = au.AuthorId)

As you can see without repeating Insert Into…statement multiple times just like you would typically do, one can accomplish the same by using the Select statement to select the columns belonging to one or more tables.

 

 

 

 

 

 

 

 

Output from the newly inserted records in the Composite table:

About Obi Oberoi

I am a Technology Aficionado who has passion for learning, speaking, reading, blogging and hanging out with geeks and like-minded people!
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.