The following is a sample INSERT statement:
INSERT INTO
Foo (c1,c2)
VALUES
(a,1)
,(b,2)
,(c,3)
How do I insert these values to show the following result without redundant insert statements?:
c1 | c2
-------
a |1
a |1
a |1
a |1
a |1
a |1
b |2
b |2
b |2
b |2
b |2
b |2
c |3
c |3
c |3
c |3
c |3
c |3
You can use dynamic sql to replicate your insert x times:
declare @sql nvarchar(max)
select @sql = replicate('
INSERT INTO
Foo (c1,c2)
VALUES
(''a'',1)
,(''b'',2)
,(''c'',3)',6)
exec(@sql)
select * from Foo order by c1,c2
Alternatively, you can loop until you have the number of desired inserts:
while (select count(*) from Foo where c1 = 'a') < 6
begin
INSERT INTO
Foo (c1,c2)
VALUES
('a',1)
,('b',2)
,('c',3)
end
select * from Foo order by c1,c2
And yet another option would be:
INSERT INTO
Foo (c1,c2)
VALUES
('a',1)
,('b',2)
,('c',3)
GO 6
After inserting those values use Recursive CTE to do this
;with cte as
(
select c1,c2,1 as id from foo
union all
select c1,c2,id+1 from cte where id<5
)
Insert into Foo (c1,c2)
select c1,c2 from cte
Or do a Cross Join with numbers table. If you don't have numbers table use master.dbo.spt_values table
Insert into Foo(c1,c2)
SELECT c1, c2
FROM Foo
CROSS JOIN (SELECT number
FROM master.dbo.spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 5) T
Impossible as designed! Two columns will not suffice. You need a child table or a third column to hold your "count". The number of rows for "a", "b", "c" cannot be inferred by two columns and less than the actual number of rows for each.
