May 6, 2013 at 1:53 am
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.
May 6, 2013 at 2:27 am
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.
May 6, 2013 at 3:22 am
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
May 6, 2013 at 3:36 am
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.
May 6, 2013 at 3:40 am
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/
May 6, 2013 at 3:49 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply