CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
CodeNinja
CodeNinja

Reputation: 3278

Populate a sql table with duplicate data except for one column

I have a sql table :

Levels 

LevelId Min Product
1       x    1
2       y    1
3       z    1
4       a    1

I need to duplicate the same data into the database by changing only the product Id from 1 2,3.... 40

example 

 LevelId Min Product
    1       x    2
    2       y    2
    3       z    2
    4       a    2

I could do something like

INSERT INTO dbo.Levels SELECT top 4 * fROM dbo.Levels but that would just copy paste the data. Is there a way I can copy the data and paste it changing only the Product value?

Upvotes: 3

Views: 7257

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can generate the product id's and then load them in:

with cte as (
      select 2 as n
      union all
      select n + 1
      from cte
      where n < 40
     )
INSERT INTO dbo.Levels(`min`, product)
    SELECT `min`, cte.n as product
    fROM dbo.Levels l cross join
         cte
    where l.productId = 1;

This assumes that the LevelId is an identity column, that auto-increments on insert. If not:

with cte as (
      select 2 as n
      union all
      select n + 1
      from cte
      where n < 40
     )
INSERT INTO dbo.Levels(levelid, `min`, product)
    SELECT l.levelid+(cte.n-1)*4, `min`, cte.n as product
    fROM dbo.Levels l cross join
         cte
    where l.productId = 1;

Upvotes: 1

jdl
jdl

Reputation: 1104

You could use a CROSS JOIN against a numbers table, for example.

WITH
  L0   AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
  L1   AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L1)
SELECT
  lvl.[LevelID],
  lvl.[Min],
  num.[N]
FROM dbo.[Levels] lvl
CROSS JOIN Nums num

This would duplicate 4 times.

Upvotes: 0

Callie J
Callie J

Reputation: 31296

You're most of the way there - you just need to take one more logical step:

INSERT INTO dbo.Levels (LevelID, Min, Product)
    SELECT LevelID, Min, 2 FROM dbo.Levels WHERE Product = 1

...will duplicate your rows with a different product ID.

Also consider that WHERE Product = 1 is going to be more reliable than TOP 4. Once you have more than four rows in the table, you will not be able to guarantee that TOP 4 will return the same four rows unless you also add an ORDER BY to the select, however WHERE Product = ... will always return the same rows, and will continue to work even if you add an extra row with a product ID of 1 (where as you'd have to consider changing TOP 4 to TOP 5, and so on if extra rows are added).

Upvotes: 4

Timothy Walters
Timothy Walters

Reputation: 16874

INSERT INTO dbo.Levels (LevelId, Min, Product)
SELECT TOP 4
    LevelId,
    Min,
    2
FROM dbo.Levels

You can include expressions in the SELECT statement, either hard-coded values or something like Product + 1 or anything else.

I expect you probably wouldn't want to insert the LevelId though, but left that there to match your sample. If you don't want that just remove it from the INSERT and SELECT sections.

Upvotes: 0

Related Questions