Select with MAX

[ddeegan]

Hello

I am trying to insert a record that contains an incrementing #. The # to start with comes from another table (APP_NEXT_NUMBER in the example below).


Current way that works but is ugly...

select
DCT.CUSTOMER_ID as 'master_customer_id',
identity(int,2464262,1) as sequence_no
into USR_COMPANY_HISTORY_TEMP
from DCT_CUS_COMPANY_HISTORY DCT

Incorrect syntax but shows what I am trying to do

select
DCT.CUSTOMER_ID as 'master_customer_id',
identity(int,(select max(SEQUENCE_NO) from usr_company_history),1) as sequence_no
into USR_COMPANY_HISTORY_TEMP
from DCT_CUS_COMPANY_HISTORY DCT

Can someone help with the syntax?

Thanks in advance
Dave

 

[vongrunt]

Either dynamic SQL or...

SELECT INTO with identity (1, 1). Add new column. Update that column with identity value + seed. Drop identity column.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[GShen]

You could perform the Select Max first and set it to a variable. then perform the insert using the variable you created in the prior select.

eg.
DECLARE @MAXID AS INT

SELECT @maxid = MAX(SEQUENCE_NO)
FROM usr_company_history

INSERT INTO USR_COMPANY_HISTORY_TEMP
(DCT.CUSTOMER_ID)
SELECT @MAXID
FROM DCT_CUS_COMPANY_HISTORY DCT


Not sure if I have all your field names correctly. It is a little hard to read your code. reply back if you need more info.


Remember when... everything worked and there was a reason for it?

 

[ddeegan]

Thanks for the replies

GShen, I will use what you posted and will be more conscience of my code examples.

I was looking at vongrunts reply and looking at dynamic SQL - does the reply from Gshen be considered dynamic SQL?

I always called everything Transact SQL - if anyone wants to explain or post a good link, it would be appreciated.

Thanks again
Dave

 

[GShen]

ddeegan,
Transact-SQL works for me. Not sure what you mean by dynamic SQL, maybe vongrunt can help there. Also I neglected to add 1 to the MAXID to bump it up by 1 after rereading your question.

Remember when... everything worked and there was a reason for it?

 

[jbenson001]

Dynamic SQL means you are creating the sql on the fly with a string and then executing it. This is used when you need to use variables in the script for say column names or dbnames.. etc.

There are several posts here that will show you..


Jim

 

[vongrunt]

What if source table has more than one record? Then all inserted rows will get the same @MAXID...


------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[ddeegan]

Hello again, sorry, I'm still not getting this right.

Here is a shortened example

DECLARE @MAXID AS INT

SELECT @maxid = MAX(SEQUENCE_NO)
FROM usr_company_history

select
CUSTOMER_ID,
identity(int,@maxid,1) as sequence_no
into NewTable
from OldTable OT

but I'm getting "Line 8: Incorrect syntax near '@maxid'."

Thanks
Dave

 

[vongrunt]

IDENTITY() does not accept variable for any of it's argument's. Won't work.

Are you trying to insert one or many records?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[ddeegan]

I'm trying to insert many records.

 

[GShen]

ddeegan,
vongrunt is correct. Getting the max_id will insert the same ID for each record if you have multiple records which are trying to insert.

Remember when... everything worked and there was a reason for it?

 

[vongrunt]

Instead of SELECT INTO...

How about creating empty table with identity column, doing DBCC CHECKIDENT with RESEED and then INSERT INTO... SELECT? No dynamic SQL, no ALTER TABLE stuff...

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[ddeegan]

Then I will use the suggestion from his he earlier post...

"SELECT INTO with identity (1, 1). Add new column. Update that column with identity value + seed. Drop identity column. "

Thanks to everyone - I know more now than I did.