Sql script to use a column from one table and populate another table

Question

I have two tables Product table and Rate Table.

Product

ProductId Name

Rate

LevelId Cost ProductId

Each Product has 7 Levels and cost for each level is 100, 200.... 700.

I now need a script to take all the product Ids and Populate the Rate table , so that my end output would look like this :

Rate

LevelId Cost ProductId

1          100    1  
2          200    1  
3          300    1  
4          400    1  
5          500    1  
6          600    1  
7          700    1  
1          100    2

and so on

Currently I insert the first 7 rows manually and then run the below query for every product id

INSERT INTO dbo.Rate (LevelID, Cost, ProductId)
    SELECT LevelID, Cost, ProductId FROM dbo.Rate WHERE ProductId = 1

Can you direct me on how to fully automate my work ?

Monty Wild · Accepted Answer · 2013-10-10 23:07:48Z

2

Have a look at CTEs (Common Table Expressions):

WITH CTE AS
(SELECT 1 as LevelID , 100 as Cost
 UNION ALL
 SELECT LevelID + 1 , Cost + 100 
 FROM CTE 
 WHERE LevelID < 7)
INSERT INTO Rate
SELECT CTE.LevelID, CTE.Cost, Product.ProductID
FROM CTE CROSS JOIN Product

A CTE can create a virtual table of data using an algorithm, so there is no need to manually create a table full of predictable data. A query using six UNION ALLs is not so bad, but what about hundreds or thousands or millions? If you have more than 100 recursions and not more than 32767, you'd need to add OPTION (MAXRECURSION n) where n is the number of loops you need, or if you had more than 32767 loops, you'd need to add OPTION (MAXRECURSION 0) and be wary of a infinite loop.

edited Oct 10, 2013 at 23:07

answered Oct 10, 2013 at 22:53

Monty Wild

3,9991 gold badge26 silver badges40 bronze badges

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

1 Comment

jcwrequests Over a year ago

Thanks for reference. I have been meaning to look into this one.

jcwrequests · Accepted Answer · 2013-10-10 22:46:27Z

1

 INSERT INTO Rate
 SELECT   bs.level, bs.costs, P.productid
 FROM
     (SELECT 1 as level, 100 as cost
      UNION ALL
      SELECT 2 as level, 200 as cost
      UNION ALL
      SELECT 3 as level, 300 as cost
      UNION ALL
      SELECT 4 as level, 400 as costs
      UNION ALL
      SELECT 5 as level, 500 as costs
      UNION ALL
      SELECT 6 as level, 600 as costs
      UNION ALL
      SELECT 7 as level, 600 as costs) bs,
   Product P

answered Oct 10, 2013 at 22:46

jcwrequests

1,1301 gold badge7 silver badges13 bronze badges

Comments

mmarie · Accepted Answer · 2013-10-10 22:56:28Z

1

CREATE TABLE #temp (levelid INT)

INSERT INTO #temp (levelid) 
SELECT 1
UNION ALL
SELECT 2
UNION ALL 
SELECT 3
UNION ALL 
SELECT 4 
UNION ALL 
SELECT 5
UNION ALL  
SELECT 6
UNION ALL 
SELECT 7

insert into dbo.rate
SELECT levelid, (levelid * 100) AS cost, productid
FROM dbo.Product 
CROSS JOIN #temp

DROP TABLE #temp

I created a temp table with the 7 levels and then used that. The cross join creates a combination of each level and each product id.

edited Oct 10, 2013 at 22:56

answered Oct 10, 2013 at 22:45

mmarie

5,6041 gold badge20 silver badges34 bronze badges

Comments

sgeddes · Accepted Answer · 2013-10-10 23:03:34Z

1

Just to throw it out there, assuming you have access to the master..spt_values table, you could do this little trick:

insert into rate
select distinct s.number, s.number * 100, p.productid
from product p 
  join master..spt_values s
     on s.number > 0 and s.number < 8

SQL Fiddle Demo

answered Oct 10, 2013 at 23:03

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Collectives™ on Stack Overflow

Sql script to use a column from one table and populate another table

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related