SQL SERVER Insert Addition?

Question

So this seems like something that should be easy. But say I had an insert:

insert into TABLE VALUES ('OP','OP_DETAIL','OP_X')

and I wanted X to go from 1-100. (Knowing there are some of those numbers that already exist, so if the insert fails I want it to keep going)

how would I do such a thing?

You could do this in a loop, checking for the existence of 'OP_X' before inserting a record. There's also a more elegant way to do this with a common table expression (if you're version of SQL Server supports it). — Dave Mason, Mar 10 '14 at 15:10
The Merge Statement will insert or update: http://stackoverflow.com/a/243670/93161 — Robert Love, Mar 10 '14 at 15:13

score 2 · Accepted Answer · answered Mar 10 '14 at 15:34

Here's a slightly faster way

-- techniques from Jeff Moden and Itzik Ben-Gan:
;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
      E02(N) AS (SELECT 1 FROM E00 a, E00 b),
      E04(N) AS (SELECT 1 FROM E02 a, E02 b),
      E08(N) AS (SELECT 1 FROM E04 a, E04 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08)
INSERT INTO yourTable
SELECT 'OP','OP_DETAIL','OP_' + CAST(N AS varchar)
FROM   cteTally
WHERE  N <= 100

score 1 · Answer 2 · answered Mar 10 '14 at 15:45

No need for loops. Set-based methods FTW!

This is a prime example where you should use a numbers table. Other answerers have created the equivalent on the fly but you can't beat a good, old-fashioned table if you ask me!

Use your best Google-Fu to find a script or alternatively here's one I made earlier

INSERT INTO your_table (you_should, always_list, your_columns)
SELECT 'OP'
     , 'OP_DETAIL'
     , 'OP_' + Cast(number As varchar(11))
FROM   dbo.numbers
WHERE  number BETWEEN 1 AND 100
AND    NOT EXISTS (
         SELECT your_columns
         FROM   your_table
         WHERE  your_columns = 'OP_' + Cast(numbers.number As varchar(11))
       )
;

score 0 · Answer 3 · answered Mar 10 '14 at 15:21

CREATE TABLE #Temp (
    OPCol VARCHAR(128),
    OPDetailCol VARCHAR(128),
    OPXCol VARCHAR(128)
)

--Add sample data, representing some random OPX values that may already exist.
INSERT INTO #Temp (OPCol, OPDetailCol, OPXCol)
VALUES 
    ('OP','OP_DETAIL','OP_4'),
    ('OP','OP_DETAIL','OP_22'),
    ('OP','OP_DETAIL','OP_39'),
    ('OP','OP_DETAIL','OP_70')

--Common table expression with recursion.
;WITH CTE AS
(
    SELECT 1 AS OPCounter
    UNION ALL
    SELECT OPCounter + 1
    FROM CTE
    WHERE OPCounter < 100
)
INSERT INTO #Temp (OPCol, OPDetailCol, OPXCol)
SELECT 'OP','OP_DETAIL','OP_' + CAST(OPCounter AS VARCHAR(3))
FROM CTE
WHERE NOT EXISTS (
    SELECT 1
    FROM #Temp t
    WHERE t.[OPXCol] = 'OP_' + CAST(OPCounter AS VARCHAR(3))
)

--Verify results
SELECT * FROM #Temp

SQL SERVER Insert Addition?

3 Answers3