CODEWITHSUNDEEP
sqlsql-server
cullimorer
cullimorer

Reputation: 755

SQL select a row X times and insert into new

I am trying to migrate a bunch of data from an old database to a new one, the old one used to just have the number of alarms that occurred on a single row. The new database inserts a new record for each alarm that occurs. Here is a basic version of how it might look. I want to select each row from Table 1 and insert the number of alarm values as new rows into Table 2.

Table 1: 

|  Alarm ID    |  Alarm Value   |
|--------------|----------------|
|     1        |        3       |
|     2        |        2       |

Should go into the alarm table as the below values.

Table 2:

| Alarm New ID |  Value   |
|--------------|----------|
|     1        |    1     |
|     2        |    1     |
|     3        |    1     |
|     4        |    2     |
|     5        |    2     |

I want to create a select insert script that will do this, so the select statement will bring back the number of rows that appear in the "Value" column.

Upvotes: 0

Views: 1170

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

A recursive CTE can be convenient for this:

with cte as (
      select id, alarm, 1 as n
      from t
      union all
      select id, alarm, n + 1
      from cte
      where n < alarm
     )
select row_number() over (order by id) as alarm_id, id as value
from cte
order by 1
option (maxrecursion 0);

Note: If your values do not exceed 100, then you can remove OPTION (MAXRECURSION 0).

Upvotes: 4

Thom A
Thom A

Reputation: 95830

Rather than using an rCTE, which is recursive (as the name suggests) and will fail at 100 rows, you can use a Tally table, which tend to be far faster as well:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3)
SELECT ROW_NUMBER() OVER (ORDER BY V.AlarmID,T.I) AS AlarmNewID,
       V.AlarmID
FROM (VALUES(1,3),(2,2))V(AlarmID,AlarmValue)
     JOIN Tally T ON V.AlarmValue >= T.I;

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15185

Replicate values out with a CTE. 

DECLARE @T TABLE(AlarmID INT, Value INT)
INSERT @T VALUES
(1,3),
(2,2)

;WITH ReplicateAmount AS
 (
    SELECT  AlarmID, Value FROM @T

    UNION ALL

    SELECT R.AlarmID, Value=(R.Value - 1)
    FROM ReplicateAmount R 
    INNER JOIN @T T ON R.AlarmID = T.AlarmID
    WHERE R.Value > 1
)

SELECT 
    AlarmID = ROW_NUMBER() OVER( ORDER BY AlarmID), 
    Value = AlarmID --??
FROM
    ReplicateAmount 
ORDER BY 
    AlarmID

This answers your question. I would think the query below would be more useful, however, you did not include usage context.

SELECT 
    AlarmID,
    Value 
FROM
    ReplicateAmount 
ORDER BY 
    AlarmID

Upvotes: 0

Related Questions