CODEWITHSUNDEEP
sqlsql-serverinsert
J.S.Orris
J.S.Orris

Reputation: 4831

Insert Duplicates Of Distinct Value In One SQL Statement

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

Upvotes: 1

Views: 74

Answers (3)

Ron Smith
Ron Smith

Reputation: 3266

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

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 1

Ottak
Ottak

Reputation: 293

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.

Upvotes: 0

Related Questions