mssql: insert into syntax

Hello
Can anyone help me translate this from access so that it can work in mssql
(i need to get next value, but cannot use identity as if row is deleted,
another must get new next column number which would be same as deleted one)
Access;
INSERT INTO table
SELECT
(IIF(code<>Null,MAX(code)+1,1) AS code,
0 AS usercode
FROM table

I tried this in mssql but will not work:
INSERT INTO table
SELECT
CASE
WHEN code IS NULL THEN 1
ELSE MAX(code)+1
END
AS code,
0 AS usercode
FROM table

Hi

You may be better of using an identity column. It is not guaranteed to be
contiguous but usually is the ordinal value that is required. This is
similar to the autoincrementing number in access. See the topic "Identity
(Property)" in books online for more information.

John


"Andre" <spam@spam.com> wrote in message  news:de9g0c$ih4$1@ss
405.t-com.hr...
> Hello
> Can anyone help me translate this from access so that it can work in mssql
> (i need to get next value, but cannot use identity as if row is deleted,
> another must get new next column number which would be same as deleted
> one)
> Access;
> INSERT INTO table
> SELECT
> (IIF(code<>Null,MAX(code)+1,1) AS code,
> 0 AS usercode
> FROM table
>
> I tried this in mssql but will not work:
> INSERT INTO table
> SELECT
> CASE
> WHEN code IS NULL THEN 1
> ELSE MAX(code)+1
> END
> AS code,
> 0 AS usercode
> FROM table
>
>

On Sun, 21 Aug 2005 11:02:42 +0200, Andre wrote:

 (snip)
>Access;
>INSERT INTO table
>SELECT
> (IIF(code<>Null,MAX(code)+1,1) AS code,
>0 AS usercode
>FROM table

Hi Andre,

As John says: Consider using IDENTITY (the SQL Server equivalent of what
Access calls "autonumber").

If there are reason's why you can't use IDENTITY, then use

SELECT COALESCE(MAX(code), 0) + 1 AS code
FROM   table

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

will try this
thx

(by the way, i mentioned I cannot use identity as it would not preserve
correct order if a middle row is deleted
and it would not allow end-user to change it)
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
 news:6fqgg1d3f7bpim4
 ct0bril93j6vkedhgek@
4ax.com...
> On Sun, 21 Aug 2005 11:02:42 +0200, Andre wrote:
>


> If there are reason's why you can't use IDENTITY, then use
>
> SELECT COALESCE(MAX(code), 0) + 1 AS code
> FROM   table
>

On Sun, 21 Aug 2005 14:19:39 +0200, Andre wrote:

>will try this
>thx
>
>(by the way, i mentioned I cannot use identity as it would not preserve
>correct order if a middle row is deleted
>and it would not allow end-user to change it)

Hi Andre,

That's a logical result of the "raison d'etre" of the IDENTITY
attribute. You should use IDENTITY only to generate a unique numeric
value that can be used in place of the "real" key in foreign key
relationships. For instance, if a Foo is identified by the combination
of FooName, FooDate and FooWeight, the tables Foo and Bar *could* look
like this:

CREATE TABLE Foo
(FooName varchar(35) NOT NULL,
FooDate datetime NOT NULL,
FooWeight numeric (15,7) NOT NULL,
-- other columns,
PRIMARY KEY (FooName, FooDate, FooWeight)
)
CREATE TABLE Bar
(BarNo int NOT NULL,
FooName varchar(35) NOT NULL,
FooDate datetime NOT NULL,
FooWeight numeric (15,7) NOT NULL,
-- other columns,
PRIMARY KEY (BarNo),
FOREIGN KEY (FooName, FooDate, FooWeight)
REFERENCES Foo (FooName, FooDate, FooWeight)
ON UPDATE CASCADE
ON DELETE NO ACTION
)

Or, you could use IDENTITY to create a surrogate key and have your
tables like this:

CREATE TABLE Foo
(FooID int NOT NULL IDENTITY,
FooName varchar(35) NOT NULL,
FooDate datetime NOT NULL,
FooWeight numeric (15,7) NOT NULL,
-- other columns,
PRIMARY KEY (FooID),
UNIQUE (FooName, FooDate, FooWeight)
)
CREATE TABLE Bar
(BarNo int NOT NULL,
FooID int NOT NULL IDENTITY,
-- other columns,
PRIMARY KEY (BarNo),
FOREIGN KEY (FooID) REFERENCES Foo (FooID)
ON DELETE NO ACTION
)

This gives Bar a smaller footprint, and will speed up te joins (but at
the expense of a higher number of required joins). Note that a Foo is
still identified by it's "real" key. Also note that you might just as
well keep the "real" key as PRIMARY KEY and declare the identity column
to be UNIQUE (that will affect how your indexes look, so this is a
choice that affects performance).

An important issue to keep in mind is that the end user never sees the
identity value in this case. The end user will only see the "real" key,
as determined when investigating the business' information needs.

Your mention of preserving order when rows are deleted makes me think
that you want to use IDENTITY to get a ranking. In that case: don't. The
only thing MS guarantees about IDENITY is that it will be a unique value
in it's table (proivided you never override the generated values or
reset the seed). If you need a rank, you can either:
a) Compute it whenever you query the data. Use a view if you don't want
to retype the same query logic over and over again, or
b) Compute and store it; recompute ranks after each modification; this
one is dangerous (one uncontrolled modification can ruin the scheme) and
can slow down modification operations - only use it if you query the
data (including the rank) much more often than you modify the data.

Your mention of end users changing the value makes me think that you
don't want a ranking after all - but if have no idea what you do want to
use it for. Can you explain the purpose of this? I'm asking partly out
of curiosity, partly because I have the feeling that you're about to
make an error that either you or your successor will regret - I might be
wrong (I hope so!), but if I'm not, you better change your plan now,
before it is too late!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

I have an accounting program which I am moving to mssql.
Now, I have tables which contain various documents (bills, inputs/outputs
etc).
Each document in its group must be in order
1,2,3....
there can be no omissions.
Now, I must permit to some users to delete documents (if these have been
entered by mistake), but also to permit
them to change their numbers. But these numbers must be unique in their
respective tables.
And, when user is creating new document, program must give him next number
(serial number if you wish).

So, since user might delete a middle row, using identity would mean that he
could, later when he creates document, give it the
number he previously deleted. I could set identity to allow change, but I
don't want to.

My programs currently run on access and mysql. I am adding mssql but didn't
expect so much trouble with sql syntax
Coalesce was mentioned in previous post: it does not work
I need simple

INSERT INTO table SELECT  ISNULL(MAX(fieldvalu
e)+1,1) AS fieldvalue FROM
table
or
INSERT INTO table SELECT MAX(fieldvalue)+1 AS fieldvalueFROM table

if this is not possible on mssql, I will have to create on insert trigger or
lock table while creating new entry and first get value, then insert it into
table (1 query, 1 insert - lock, since two users might at the same time
create new: while information fieldvalue+1 travels to first user, second
executes same query and gets same
number as first has not made insert yet)

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo)  writes:
> Your mention of preserving order when rows are deleted makes me think
> that you want to use IDENTITY to get a ranking. In that case: don't. The
> only thing MS guarantees about IDENITY is that it will be a unique value

Eh, André says he does not want to use IDENTITY, so you tell him not to
use it?

Anyway, if you say:

INSERT tbl (...)
SELECT ...
ORDER  BY ...

and tbl has an IDENTITY column, the message I have, is indeed that there
is a guarantee that the IDENTITY values will reflect the ORDER BY clause.

However, this does not apply to SELECT INTO.

In any case, it is obvious from Andre's description of his business problem
that he should stay away from IDENTITY.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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(fieldvalu
e)+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/techin.../2000/books.asp

Hi

You can try it as

INSERT INTO table
SELECT  ISNULL(MAX(code),0)+
1, 0 FROM table

Please let me know if u have any questions

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.droptable.com ***

Hi Andre

>From your description it sounds like you have to re-order everything if
you remove an intermediate document. This will not lead to a very
scalable application.

John