CODEWITHSUNDEEP
sqlsql-servert-sql
user475353
user475353

Reputation:

SQL SERVER Insert Addition?

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?

Upvotes: 0

Views: 89

Answers (3)

gvee
gvee

Reputation: 17161

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))
       )
;

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56735

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

Upvotes: 2

Dave Mason
Dave Mason

Reputation: 4936

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

Upvotes: 0

Related Questions