|
|
Posted by Erland Sommarskog on 08/22/05 01:18
Andre (spam@spam.com) writes:
> Coalesce was mentioned in previous post: it does not work
Please defined "does not work". Do you get an error message, do you
get unexpected result, does heaven fall down on your or what?
In any case, this seem to work:
CREATE TABLE andre (id int NOT NULL PRIMARY KEY,
somedata varchar(230) NOT NULL)
go
INSERT andre (id, somedata)
SELECT coalesce(MAX(id), 0) + 1, 'This is some data'
FROM andre
INSERT andre (id, somedata)
SELECT coalesce(MAX(id), 0) + 1, 'This is some other data'
FROM andre
INSERT andre (id, somedata)
SELECT coalesce(MAX(id), 0) + 1, 'This is any data'
FROM andre
go
SELECT * FROM andre ORDER BY id
go
DROP TABLE andre
> I need simple
>
> INSERT INTO table SELECT ISNULL(MAX(fieldvalue)+1,1) AS fieldvalue FROM
Since isnull() is proprietary to SQL Server, while coalesce() is
ANSI-SQL and you support other DBMS's, coalesce() would be a better
choice.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|