Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert multiple rows in a table with a single select statement Expand / Collapse
Author
Message
Posted Wednesday, February 12, 2014 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:05 PM
Points: 4, Visits: 52
I've had some SQL experience, but only through a few classes in college. So I apologize for my lack of experience.
I have created a trigger that is set off every time a new item has been added to TableA.
The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.
ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement? Thanks in advance for the help.
Post #1540895
Posted Wednesday, February 12, 2014 10:26 AM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:35 AM
Points: 3,783, Visits: 8,484
I'm not sure why would you want that. It seems like a weird requirement.
As you should know, triggers use 2 pseudo-tables (Inserted & Deleted) from which you need to get the information because you might not have it in the real table yet.
You could use something like this.

INSERT INTO TableB(item, tasktype)
SELECT i.Item,
t.tasktype
FROM Inserted
CROSS APPLY (VALUES('Planning'),('Design'),('Program'),('Production')) t(tasktype)

If you have any questions on how it works, feel free to ask.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1540897
Posted Wednesday, February 12, 2014 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:05 PM
Points: 4, Visits: 52
Thanks Luis! That worked just fine. I agree it is weird, but it was at the request of a user.
Post #1540934
Posted Wednesday, February 12, 2014 12:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:35 AM
Points: 3,783, Visits: 8,484
You're welcome.
The question now is, do you understand how does it work? You should do it before implementing it into production.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1540937
Posted Wednesday, February 12, 2014 12:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:05 PM
Points: 4, Visits: 52
I did a bit of research on cross apply and I am a bit confused between outer and cross applies. I have a test environment with dummy tables and views in which I test code before implementation.
Post #1540943
Posted Wednesday, February 12, 2014 12:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:35 AM
Points: 3,783, Visits: 8,484
CROSS APPLY works similar to an [INNER] JOIN and OUTER APPLY works similar to LEFT [OUTER] JOIN. Maybe that will help you to understand it better.

This article series by Paul White might help you:
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1540952
Posted Wednesday, February 12, 2014 2:29 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 635, Visits: 2,140
Luis Cazares (2/12/2014)
CROSS APPLY works similar to an [INNER] JOIN and OUTER APPLY works similar to LEFT [OUTER] JOIN. Maybe that will help you to understand it better.

This article series by Paul White might help you:
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)


To be fair, you used it more like a cross join (exactly like one in fact), and outer apply would work the exact same when using it like this.

For clarity, it is only when you join back to the main query in the definition or function parameters that they map to inner/outer join respectively
Post #1540977
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse