mssql: insert into syntax

[Andre]

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

[John Bell]

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" <sp**@spam.com> wrote in message news:de**********@ss405.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

[Hugo Kornelis]

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)

[Andre]

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:6f********************************@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

[Hugo Kornelis]

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)

[Andre]

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(fieldvalue)+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)

[Erland Sommarskog]

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, es****@sommarskog.se

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

[Erland Sommarskog]

Andre (sp**@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, es****@sommarskog.se

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

[Chandra]

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.developersdex.com ***

[John Bell]

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

[Andre]

Thank you all for help.

Problem was somewhere else:

I tried
INSERT INTO table x AS fieldx, y AS fieldy FROM table
while correct (for MSSQL obviously) is:
INSERT table (fieldx,fieldy) SELECT x,y FROM table

again, thank you for your time
p.s.: I wonder why are there such differences between sql syntax for various
databases (as in: what is the point of standard which is ignored)

[Erland Sommarskog]

Andre (sp**@spam.com) writes:

Thank you all for help.

Problem was somewhere else:

I tried
INSERT INTO table x AS fieldx, y AS fieldy FROM table
while correct (for MSSQL obviously) is:
INSERT table (fieldx,fieldy) SELECT x,y FROM table
As far as I know the latter is also compliant with ANSI standards.
(Save for the fact that ANSI mandates INTO, while this is optional in
MS SQL Server.) The first syntax is something I've never seen before.
Does it work anywhere?
p.s.: I wonder why are there such differences between sql syntax for
various databases (as in: what is the point of standard which is
ignored)

Indeed, just because it is the standard, does not mean that it is
implemnented everywhere. However, the basics of a regular INSERT
statement is something I would expect to work everywhere.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Hugo Kornelis]

On Sun, 21 Aug 2005 23:34:40 +0200, Andre wrote:

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). (snip)

Hi Andre,

There are some conflicting requirements. If there may be no omissions,
than you must either not allow users to delete a document (or rather:
don't allow them to delete A ROW - if the corresponding document is
deleted, keep the row but set a column to indicate that the document is
deleted) - or you must renumber all documents each time a document is
deleted to make sure that there never are gaps. Of course, if these
numbers are visible to the user and used to identify the documents, then
renumbering them will wreak havoc to the ability to relate rows in the
database to the actual documents. And if the users don't see the
numbers, then why bother with trying to keep them without omissions?

Also, if you allow users to change the document number, you are actually
guaranteed to get omissions. Sooner or later, someone will type the
number 42 because he's a Douglas Adams fan.

If the actual requirement is to use a number that is PREFERABLY
increasing and without gaps, and that users can optionally change to
reuse the number of a deleted document, I'd say: do a SELECT MAX(..)
query in the front end; increase by one and prepopulate the number field
in your frontend with that number. The user can either accept this
default or type a different number. The number that is in the field when
the user submits his data entry is sent to the database in an INSERT ...
VALUES statement.

(snip)I could set identity to allow change, but I
don't want to.
So instead, you try to create your own solution that behaves exactly as
IDENTITY after setting it to allow change, but less scalable?

(snip)Coalesce was mentioned in previous post: it does not work
I need simple

INSERT INTO table SELECT ISNULL(MAX(fieldvalue)+1,1) AS fieldvalue FROM
table

This should work. If you replace ISNULL with COALESCE, it should still
work. If it doesn't then please provide more information.

Best, Hugo
--

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

[Andre]

Actually, it is only important to fetch new table entry as CODE+1
Gaps are OK if user makes them. Skipping order number of CODE by user is
also OK.
But program must always return CODE+1 when new row is added.
User may change the number to any he wishes (except existing one) and the
next new row will be +1
This, of course, is not my idea. I would forbid deleting documents (even law
requires them to be void, not deleted - we are talking about accounting
program)
but then nobody would purchase my program.

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:ip********************************@4ax.com...

On Sun, 21 Aug 2005 23:34:40 +0200, Andre wrote:

There are some conflicting requirements. If there may be no omissions,
than you must either not allow users to delete a document (or rather:
don't allow them to delete A ROW - if the corresponding document is
deleted, keep the row but set a column to indicate that the document is
deleted) - or you must renumber all documents each time a document is
deleted to make sure that there never are gaps. Of course, if these
numbers are visible to the user and used to identify the documents, then
renumbering them will wreak havoc to the ability to relate rows in the
database to the actual documents. And if the users don't see the
numbers, then why bother with trying to keep them without omissions?

Also, if you allow users to change the document number, you are actually
guaranteed to get omissions. Sooner or later, someone will type the
number 42 because he's a Douglas Adams fan.

If the actual requirement is to use a number that is PREFERABLY
increasing and without gaps, and that users can optionally change to
reuse the number of a deleted document, I'd say: do a SELECT MAX(..)
query in the front end; increase by one and prepopulate the number field
in your frontend with that number. The user can either accept this
default or type a different number. The number that is in the field when
the user submits his data entry is sent to the database in an INSERT ...
VALUES statement.

[Hugo Kornelis]

On Tue, 23 Aug 2005 13:28:57 +0200, Andre wrote:

Actually, it is only important to fetch new table entry as CODE+1
Gaps are OK if user makes them. Skipping order number of CODE by user is
also OK.
But program must always return CODE+1 when new row is added.
User may change the number to any he wishes (except existing one) and the
next new row will be +1
This, of course, is not my idea. I would forbid deleting documents (even law
requires them to be void, not deleted - we are talking about accounting
program)
but then nobody would purchase my program.

Hi Andre,

I stick with my previous recommendation.

1. Fetch MAX(code)+1 with a non-locking query when opening the screen.
Either display it as default value in the code field, or keep the code
field blank.

2. When details are entered, attempt to insert the row, with code as
entered on the screen; if no value is entered, use the MAX(code)+1 from
the previous call.

3. If a row with the chosen key value exists, further action is decided
by the front end:

3a. If key was entered by user: error message.
3b. If user didn't override the default, re-insert row with MAX(code)+1
as new code; show warning message that code has been changed.

Best, Hugo
--

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