Need multiple copies of one resultset in sql without using loop

Question

Following is the sample data. I need to make 3 copies of this data in t sql without using loop and return as one resultset. This is sample data not real.

42  South Yorkshire
43  Lancashire
44  Norfolk

Edit: I need multiple copies and I have no idea in advance that how many copies I need I have to decide this on the basis of dates. Date might be 1st jan to 3rd Jan OR 1st jan to 8th Jan.

Thanks.

Answer 1

This may not be the most efficient way of doing it, but it should work.

(select ....)
union all
(select ....)
union all
(select ....)

Answer 2

Assume the table is named CountyPopulation:

SELECT * FROM CountyPopulation
UNION ALL
SELECT * FROM CountyPopulation
UNION ALL
SELECT * FROM CountyPopulation

Share and enjoy.

Answer 3

Don't know about better but this is definatley more creative! you can use a CROSS JOIN.
EDIT: put some code in to generate a date range, you can change the date range, the rows in the #date are your multiplier.

  declare @startdate datetime
, @enddate datetime
create table #data1 ([id] int , [name] nvarchar(100))
create table #dates ([date] datetime)


INSERT #data1 SELECT 42,  'South Yorkshire'
INSERT #data1 SELECT 43,  'Lancashire'
INSERT #data1 SELECT 44,  'Norfolk'


set @startdate = '1Jan2010'
set @enddate = '3Jan2010'

WHILE (@startdate <= @enddate)
BEGIN   
INSERT #dates SELECT @startdate
set @startdate=@startdate+1
END


SELECT [id] , [name] from #data1 cross join #dates

drop table #data1
drop table #dates

Answer 4

You could always use a CTE to do the dirty work

Replace the WHERE Counter < 4 with the amount of duplicates you need.

CREATE TABLE City (ID INTEGER PRIMARY KEY, Name VARCHAR(32))

INSERT INTO City VALUES (42, 'South Yorkshire')
INSERT INTO City VALUES (43, 'Lancashire')
INSERT INTO City VALUES (44, 'Norfolk')

/*
  The CTE duplicates every row from CTE for the amount
  specified by Counter
*/
;WITH CityCTE (ID, Name, Counter) AS 
(
  SELECT  c.ID, c.Name, 0 AS Counter
  FROM    City c
  UNION ALL 
  SELECT  c.ID, c.Name, Counter + 1
  FROM    City c  
          INNER JOIN CityCTE cte ON cte.ID = c.ID
  WHERE   Counter < 4
)
SELECT  ID, Name
FROM    CityCTE
ORDER BY 1, 2

DROP TABLE City

Answer 5

There is no need to use a cursor. The set-based approach would be to use a Calendar table. So first we make our calendar table which need only be done once and be somewhat permanent:

Create Table dbo.Calendar ( Date datetime not null Primary Key Clustered )
GO
; With Numbers As
(
Select ROW_NUMBER() OVER( ORDER BY S1.object_id ) As [Counter]
From sys.columns As s1
 Cross Join sys.columns As s2
)
Insert dbo.Calendar([Date])
Select DateAdd(d, [Counter], '19000101')
From Numbers
Where [Counter] <= 100000
GO

I populated it with a 100K dates which goes into 2300. Obviously you can always expand it. Next we generate our test data:

Create Table dbo.Data(Id int not null, [Name] nvarchar(20) not null)
GO
Insert dbo.Data(Id, [Name]) Values(42,'South Yorkshire')
Insert dbo.Data(Id, [Name]) Values(43, 'Lancashire')
Insert dbo.Data(Id, [Name]) Values(44, 'Norfolk')
GO

Now the problem becomes trivial:

Declare @Start datetime
Declare @End datetime
Set @Start = '2010-01-01'
Set @End = '2010-01-03'
Select Dates.[Date], Id, [Name]
From dbo.Data
 Cross Join (
              Select [Date]
              From dbo.Calendar
              Where [Date] >= @Start
              And [Date] <= @End
             ) As Dates

Answer 6

By far the best solution is CROSS JOIN. Most natural.

See my answer here: How to retrieve rows multiple times in SQL Server?

If you have a Numbers table lying around, it's even easier. You can DATEDIFF the dates to give you the filter on the Numbers table