CODEWITHSUNDEEP
sql-server
user4612290
user4612290

Reputation: 321

Add incremental values in a single insert statement

The query below is not my actual query but a minimized version of it, as I cannot post the original one. However this explains what I want to do exactly:

Insert into TableA (BirthdateA, NameA)
Select BirthdateB,  (case when NameB is not null then NameB else "Desired Value" End) 
From TableB

"Desired Value" should be a dummy name in addition to an incremental value, for example "Dummy Name 1", "Dummy Name 2" So the final result would look like

TableB.Name     TableA.Name
John Smith       John Smith
Null             Dummy Name 1
Adam James       Adam James
John K.          John K.
Null             Dummy Name 2

Is that possible ?

Upvotes: 0

Views: 55

Answers (2)

shA.t
shA.t

Reputation: 16958

You can try this:

INSERT INTO TableA
SELECT 
    BirthDateB,
    CASE WHEN NameB IS NULL THEN 
        ('Dummy Name ' + CAST(DT.rn As varchar(10))) 
    ELSE NameB END As NameA
FROM (
    SELECT 
        *, ROW_NUMBER() OVER (PARTITION BY NameB ORDER BY NameB) As rn
    FROM 
        TableB) DT

Note :
I use ORDER BY NameB because I don't know what is your PK, you can change NameB to your PK.

Upvotes: 1

ps_prakash02
ps_prakash02

Reputation: 583

Can you try this

CREATE TABLE #Temp (No INT IDENTITY(1,1), BirthDate varchar(100), Name varchar(250))

INSERT INTO #Temp (Name) 
SELECT BirthDate, 'Dummy Name'
FROM TableB
WHERE Name IS NULL


SELECT BirthDate, Name
FROM
FROM TableB
WHERE Name IS NOT NULL
UNION
SELECT BirthDate, Name + CAST(No VARCHAR(100))
FROM
FROM #Temp

Upvotes: 1

Related Questions