CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
CodeNinja
CodeNinja

Reputation: 3278

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

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 ?

Upvotes: 1

Views: 1026

Answers (4)

Monty Wild
Monty Wild

Reputation: 3991

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.

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

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

Upvotes: 1

mmarie
mmarie

Reputation: 5638

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.

Upvotes: 1

jcwrequests
jcwrequests

Reputation: 1130

 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

Upvotes: 1

Related Questions