SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert multiple rows in a table with a single select statement


Insert multiple rows in a table with a single select statement

Author
Message
sKreetz!
sKreetz!
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 104
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.
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43356 Visits: 19859
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
sKreetz!
sKreetz!
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 104
Thanks Luis! That worked just fine. I agree it is weird, but it was at the request of a user.
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43356 Visits: 19859
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
sKreetz!
sKreetz!
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 104
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.
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43356 Visits: 19859
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3650 Visits: 3149
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search