Use MAX in insert query

Question

Post reply

Use MAX in insert query

masoudk1990

SSCommitted

Points: 1651
More actions
May 6, 2013 at 1:53 am

#394722

Hi, is it possible to use MAX function in an insert query?
I try to write a query similar to this:
INSERT INTO TableName (ID,Value)
VALUES (SELECT coalesce(MAX(ID),0) + 1 FROM TableName, 'something')
I get error, Incorrect syntax near the keyword 'SELECT'.
Thank you for help.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

masoudk1990 SSCommitted Points: 1651 More actions · Answer 1

Well, found a solution

Declare @i int;

Select @i = coalesce(MAX(ID),0) + 1 FROM TableName;

INSERT INTO TableName (ID,Value)

VALUES (@i, 'something')

If you think there would be any better way to do this please share it with me.

Thank you every one.

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

INSERT INTO TableName (ID,Value)

SELECT coalesce(MAX(ID),0) + 1, 'something' FROM TableName;

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

masoudk1990 SSCommitted Points: 1651 More actions · Answer 3

GilaMonster (5/6/2013)
INSERT INTO TableName (ID,Value)
SELECT coalesce(MAX(ID),0) + 1, 'something' FROM TableName;

Thank you, hence this statement is not allowed:

INSERT INTO TableName (Value1,value2)

values(

select value1 from table1,

select value2 from table2

)

Instead you must join table1 and table2.

Much appreciated

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

You can still do it without joining the tables:

INSERT INTO TableName (Value1,value2)

select

(select value1 from table1),

(select value2 from table2)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

masoudk1990 (5/6/2013)
GilaMonster (5/6/2013)
INSERT INTO TableName (ID,Value)
SELECT coalesce(MAX(ID),0) + 1, 'something' FROM TableName;
Thank you, hence this statement is not allowed:
INSERT INTO TableName (Value1,value2)
values(
select value1 from table1,
select value2 from table2
)
Instead you must join table1 and table2.
Much appreciated

Join them, maybe, depends what you're doing. You can't mix the INSERT ... VALUES form of the insert statement and the INSERT ... SELECT form. It's either insert with a values clause and a list of values or insert with a select statement.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass