Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use MAX in insert query Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 1:53 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:01 AM
Points: 61, Visits: 276
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.
Post #1449616
Posted Monday, May 6, 2013 2:27 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:01 AM
Points: 61, Visits: 276
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.
Post #1449625
Posted Monday, May 6, 2013 3:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 43,001, Visits: 36,157
INSERT INTO TableName (ID,Value)
SELECT coalesce(MAX(ID),0) + 1, 'something' FROM TableName;




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1449629
Posted Monday, May 6, 2013 3:36 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:01 AM
Points: 61, Visits: 276
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.
Post #1449633
Posted Monday, May 6, 2013 3:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 2,122, Visits: 5,472
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/
Post #1449635
Posted Monday, May 6, 2013 3:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 43,001, Visits: 36,157
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 2008, MVP
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

Post #1449641
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse