SQL Server 使用增量值将SQL INSERT查询重复特定次数

5ktev3wc  于 2022-12-10  发布在  其他
关注(0)|答案(1)|浏览(121)

I've got a SQL query for inserting data into a table consisting of values for StorageRowNo and StorageID . The goal is to repeat this query a specified number of times with StorageRowNo increasing by 1 every time the query is repeated. Any input would be greatly appreciated!

INSERT [dbo].[StorageRow]
    SELECT StorageRowNo, StorageID
    FROM (VALUES (1, 2)) V (StorageRowNo, StorageID)
    WHERE NOT EXISTS (SELECT 1 
                      FROM [dbo].[StorageRow] C
                      WHERE C.StorageRowNo = V.StorageRowNo 
                        AND C.StorageID = V.StorageID);

Expected output would be something like this if the specified number were 3.
| StorageRowNo | StorageID |
| ------------ | ------------ |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |

uelo1irk

uelo1irk1#

use straight sql if you can

DECLARE @numRepeats INT = 10; -- number of times to repeat the query

INSERT [dbo].[StorageRow]
 SELECT V.StorageRowNo, V.StorageID
    FROM (select row_number() over(order by (select null)) as StorageRowNo
                 ,2 as StorageID
           from master..spt_values
         ) V
    WHERE NOT EXISTS (SELECT 1 
                        FROM [dbo].[StorageRow] C
                       WHERE C.StorageRowNo = V.StorageRowNo 
                         AND C.StorageID = V.StorageID);
       AND V.StorageRowNo<=@numRepeats

相关问题