Add incremental values in a single insert statement

Question

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 ?

shA.t · Accepted Answer · 2017-09-05 16:44:06Z

1

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.

edited Sep 5, 2017 at 16:44

answered Apr 26, 2015 at 9:34

shA.t

17k5 gold badges59 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4612290 Over a year ago

Thank you for your answer, but I got one question, if i did it this way, this means that the number after Dummy Name will not be incremental (+1) but will be equivalent to that row's number (ie. Dummy Name 3 followed by Dummy Name 5 possibly) is that true ?

shA.t Over a year ago

@user4612290 When you use PARTITION BY NameB it will not be the row number it will be the rank based on NameB ;).

ps_prakash02 · Accepted Answer · 2015-04-26 09:52:11Z

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

Thank you for your answer, but how can I bind each row of the Temp table to their data from TableB ? Each Name in Temp has a Birthdate in TableB
This would work with me too, I chose the other answer however as it will provide it with the least steps. Thanks a lot

Collectives™ on Stack Overflow

Add incremental values in a single insert statement

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related