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

5ktev3wc 于 2022-12-10 发布在其他

关注(0)|答案(1)|浏览(122)

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 |

sql-server

来源：https://stackoverflow.com/questions/74725865/repeating-a-sql-insert-query-a-particular-number-of-times-with-an-incremental-va

1条答案

按热度按时间

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

赞(0）回复(0）举报 2022-12-10

我来回答

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

1条答案

相关问题

热门标签

最新问答