How to write query insert data to generaltypes increased by one based on max number on typeid ?

Question

0.00/5 (No votes)

See more:

I work on sql server 2012 I have table name GeneralTypes

as follwoing

create table GeneralTypes

(

TypeId int,

TypeName nvarchar(50)

)

TypeId is primary key but not identity

I have temp table as #tempGeneral

create table #tempGeneral

(

TypeId int,

TypeName nvarchar(50)

)

I need to insert data on GeneralTypes Based on temp table #tempGeneral

so that i will check max value exist on table GeneralTypes

then i will increase it by one when insert

and if temp table #tempGeneral Have values on TypeName not exist on table

then get max and increase one

so that what i need is to do write query insert data to table Generaltypes from #tempGeneral

#tempGeneral have following :

Mercedes

Ibm

Mazda

Toyota

after insert data on table GeneralTypes data expected will be

typeid typename

1 Mercedes

2 Ibm

3 Mazda

4 Toyota

suppose i add new values to #tempGeneral

Mercedes

Ibm

Mazda

Toyota

oppel

Honda

Hundai

then i will check TypeName on table GeneralTypes

if typename on #tempGeneral exist on table GeneralTypes then i will not insert data

if #tempGeneral have Typename not exist on table #tempGeneral then add typename not exist on table GeneralTypes

so that I will add these typename on second time

typeid typename

5 oppel

6 Honda

7 Hundai

so that i need to write one query insert data exist on #tempgeneral on table GeneralTypes increased by one

and if not exist get max value on type id on table GeneralTypes and added increased by one

What I have tried:

insert into GeneralTypes select TypeName from #tempGeneral

Posted 11-Dec-19 15:32pm

ahmed_sa

Updated 11-Dec-19 17:22pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2019-12-11T17:22:00

You could try fetching the max of the existing type id and add a row number to that. Something like

SQL

insert into GeneralTypes (TypeId, TypeName)
select Row_Number() over (order by TypeName)
       + coalesce((select max(b.TypeId)
                   from   GeneralTypes b), 0),
       TypeName
from #tempGeneral a
where not exists (select 1
                  from   GeneralTypes b
                  where  b.TypeName = a.TypeName)

However, I would reconsider using an identity or a sequence for the type id since this would eliminate the need to generate unique keys. Another things you must keep in mind:
- If a row is deleted, that id is gone and the number will not be reused. In other words you have a hole in your numbering
- Depending on other facctors of the design you may have key collisions if two or more sessions ad to the table add the same time.