Insert multiple rows in a table with a single select statement

Question

Insert multiple rows in a table with a single select statement

sKreetz!

Mr or Mrs. 500

Points: 596
More actions
February 12, 2014 at 10:18 am

#285575

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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! Mr or Mrs. 500 Points: 596 More actions · Answer 2

Thanks Luis! That worked just fine. I agree it is weird, but it was at the request of a user.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

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! Mr or Mrs. 500 Points: 596 More actions · Answer 4

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 SSC Guru Points: 183698 More actions · Answer 5

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)[/url]

Understanding and Using APPLY (Part 2)[/url]

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 SSChampion Points: 14210 More actions · Answer 6

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)[/url]
Understanding and Using APPLY (Part 2)[/url]

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