重复记录与选择的SQL 2008

Question

申报@NoofRept诠释

选择ID，名称来自tbl_Name其中id = 1

id |Name 
1 |XXXXXXX 

我的要求这个 重复这个记录@NoofRept时间

id |Name 
1 |XXXXXXX 
1 |XXXXXXX 
1 |XXXXXXX 
1 |XXXXXXX 

Answer 1

如果你想重复小于最大值2047的记录，你可以使用表Master..spt_values，这是一个系统表，而类型P有序列号RS从0到2047：

DECLARE @NoofRept INT = 10 

SELECT t.ID, t.Name 
FROM tbl_Name t 
     CROSS JOIN Master..spt_Values v 
WHERE t.ID = 1 
AND  v.Type = 'P' 
AND  v.Number BETWEEN 1 AND @NoofRept 

Example on SQL Fiddle

如果你需要比这更多，那么你就需要CROSS JOIN表来获得所需的行数：

SELECT ID, Name 
FROM tbl_Name 
     CROSS JOIN 
     ( SELECT rpt = ROW_NUMBER() OVER(ORDER BY a.Number) 
      FROM Master..spt_Values a 
        CROSS JOIN Master..spt_Values b 
        --CROSS JOIN Master..spt_values c 
        --CROSS JOIN Master..spt_values d 
     ) v 
WHERE ID = 1 
AND  v.rpt BETWEEN 1 AND @NoofRept 

取消注释按要求加入

Example on SQL Fiddle

Answer 2

未经测试的伪代码，但这样的事情可能做的伎俩：

DECLARE @sql nvarchar(max); 
DELCARE @i int; 
SET @i = 1; 

SET @sql = 'select id,Name from tbl_Name where id=1' 

WHILE @i < @NoofRept 
BEGIN 
    SET @sql = @sql + 'UNION select id,Name from tbl_Name where id=1' 

    SET @i = @i + 1; 
END 

EXEC sp_executesql @sql;