January 31, 2016 at 2:22 pm
CREATE TABLE #NEWTABLE
(ID VARCHAR(20),NAME VARCHAR(30))
INSERT INTO #NEWTABLE VALUES('100','samsonite plan')
INSERT INTO #NEWTABLE VALUES('200','Obamacare plan')
INSERT INTO #NEWTABLE VALUES('300','Cisco plan')
SELECT * FROM #NEWTABLE
--- here i am providing example values for #NewTable, but in the procedure code where i am working it will get populated in the procedure with some logic
i have a table call CorpPlans (PID varchar(20), city varchar(20), outgoingtype varchar(20))
I need to insert each ID value from #NEWTABLE to this CorpPlans, with below rows
INSERT INTO dbo.CorpPlans( PID, city, outgoingtype)
VALUES
(@id, 'NEWYORK', 'Call' ),
(@id, 'NEWARK', 'Call'),
(@id, 'Boston', 'Mail'),
(@id, 'DC', 'Mail'),
(@id, 'Dallas', 'EMail'),
(@id, 'sanjose', 'EMail')
final output i am trying is
/*
PIDcityoutgoingtype
=== ==== ============
100NEWYORKCall
100NEWARKCall
100BostonMail
100DCMail
100DallasEMail
100sanjoseEMail
200NEWYORKCall
200NEWARKCall
200BostonMail
200DCMail
200DallasEMail
200sanjoseEMail
300NEWYORKCall
300NEWARKCall
300BostonMail
300DCMail
300DallasEMail
300sanjoseEMail
*/
How can i do this in a procedure, basically one column values from one table to multiple rows in another table as above output
January 31, 2016 at 2:48 pm
Looks like all you need is a deliberate cross join... it will multiply one table by the other (all possible combinations of the two)...
Something like this:
SELECT nt.ID
, cp.City
, cp.OutgoingType
FROM #newTable nt
CROSS JOIN dbo.CorpPlans
ORDER BY nt.ID
, cp.City;
No awful cursors or loops required. You might want to read Jeff Moden's article on how to use a tally table to replace a loop[/url].
January 31, 2016 at 3:38 pm
To add one point to what pietlinden said, you can CROSS JOIN more than 2 tables and it generates a row for every possible combination of rows. For example, if you have 3 tables of 2 rows each, you'll get 8 (2 * 2 * 2) rows back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply