complex inserting a number of rows based in the value of one field on other table

• luisalfonso70

  SSC-Addicted

  Points: 410

  More actions

  May 19, 2009 at 2:48 am

  #209788

  Hi, any ideas for the next :

  I have the next table orders_resume

  id orders

  1 45

  2 60

  3 5

  4 1

  and now I want to populate an other table called orders inserting rows depending of the number of the orders_resume.orders, so for the orders_resume.id=3 the inserted rows will be 5, for the id=1 45 rows, etc....:

  id order

  3 1

  3 2

  3 4

  3 5

  thanks for the tipssss!!!

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  May 19, 2009 at 3:54 am

  #996808

  First of all, for all your future posts read this article on how to post questions to getter better responses[/url]

  Read this article on What is a Tally Table and how it replaces loops?[/url]

  Here is the solution using Tally table.

  IF ( OBJECT_ID( 'tempdb..#MyHead' ) IS NOT NULL )

  DROP TABLE #MyHead

  CREATE TABLE #MyHead

  (PK INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

  Orders INT )

  INSERT INTO #MyHead

  SELECT 10 UNION ALL

  SELECT 5 UNION ALL

  SELECT 6 UNION ALL

  SELECT 45

  SELECT mh.PK,

  N

  FROM dbo.Tally t

  CROSS JOIN #MyHead mh

  WHERE N <= mh.Orders

  --Ramesh
  

  ---