sql server (insert row for each result nested)

Question

I got 3 different tables on sql server (attrib,att_art, art). 'attrib' has a list of attributes, 'art' has a list of items and att_art should join both, like this:

ATTRIB
att1
att2
att3
...

ART
ar1
ar2
ar3
...

Both tables have an unique id that joins on att_art on id_att and id_ar

This is what I need to do:

For each item on art and for each attrib at the same time, I need to insert a new row on att_art. Like this:

att1   ar1
att2   ar1
att3   ar1
att4   ar2
att5   ar2
att6   ar2
....

How could I do this? i'm kinda new on sql and it's been overwhelming!

How will the code know which atts to associate with which arts? If it's all with all, that okay (but a but weird) ... otherwise we'd need another source of data. If you're just after an efficient way to input them "manually" we can help with that too. — Steve Lovell
– Steve Lovell, Commented Jun 2, 2017 at 11:13

Ravi · Accepted Answer · 2017-06-02 11:09:00Z

1

Use Cross apply.............

Select * from ATTRIB a
cross apply (select * from ART) b

answered Jun 2, 2017 at 11:09

Ravi

1,1721 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-06-02 11:12:41Z

1

The most typical way would be cross join with insert:

insert into att_art(attrib, art)
    select attrib.attrib, art.item
    from attrib cross join
         art;

answered Jun 2, 2017 at 11:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

sql server (insert row for each result nested)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related