Showing posts with label row. Show all posts
Showing posts with label row. Show all posts

Wednesday, March 28, 2012

multi rows SELECT

helo,
i have no problems executing that query, but it only returnes the last row from each table.
how would i be able to retrive all the rows?

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,
@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

SELECT @.VolID = VolID, @.TransactionID = TransactionID FROM Transactions WHERE TransactionTime BETWEEN @.From AND @.To

SELECT * FROM Transactions WHERE TransactionID = @.TransactionID

SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID

RETURN
GO

Quote:

Originally Posted by Cshrek

helo,
i have no problems executing that query, but it only returnes the last row from each table.
how would i be able to retrive all the rows?

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,
@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

SELECT @.VolID = VolID, @.TransactionID = VolIDFROM Transactions WHERE TransactionTime BETWEEN @.From AND @.To

SELECT * FROM Transactions WHERE TransactionID = @.TransactionID

SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID

RETURN
GO


hi
I am not clear with your code. try with this code .this is not exactly suit for ur requirement but this idea will help you

[code]
declare cur1 for select VolID from Transactions WHERE TransactionTime BETWEEN @.From AND @.To
open cur1
fetch next from cur1 into @.VolID
while @.@.fetch_status=0
begin
SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID
fetch next from cur1 into @.VolID
end|||hey, thanks.
wehn i try to run that code:

----------------------------------

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,

@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

declare cur1 for select VolID from Transactions WHERE TransactionTime BETWEEN @.From AND @.To
open cur1
fetch next from cur1 into @.VolID
while @.@.fetch_status=0
begin
SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID
fetch next from cur1 into @.VolID
end

----------------------------------
it all seems to be good, but i get that error:

"Msg 156, Level 15, State 1, Procedure AccountTrakingSELECT, Line 22
Incorrect syntax near the keyword 'for'."

and i defently have no idea what is that mean? or what am i doing worng?

appriciate your help.sql

Multi row key updates?

I have been using MS Viso (the one that integrates with Visual Studio .NET)
to generate ddl script for my database. It uses the following update trigger
code to enforce the referential integrity, but will only execute the code fo
r
one row updates, it will throw an error for multi-row updates. I am wonderin
g
why it only allows the one row, as it appears (to me) that the code will wor
k
fine for multi-row updates as well.
UPDATE "ReferedTable"
SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
FROM inserted, deleted, "ReferedTable"
WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
I understand that it is updating the refered key column in the related
tables whenever the primary key column of the parent table is changed, but I
do not understand why it only allows one row at a time to be updated.
Can someone please explain this for me? And can it actually be used for
multi-row updates? If not then what would be a good way of doing it? Thanks--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Why not dispense w/ the trigger and use the ON UPDATE CASCADE and ON
DELETE CASCADE methods of a Foreign Key? E.g.:
create table t (
a char(1) primary key,
c char(2) not null
)
create table s (
a char(1) not null references t (a)
on update cascade on delete cascade,
d datetime not null
)
Whenever t.a is changed s.a will reflect the changes and all rows in s.a
will be updated.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQj4zo4echKqOuFEgEQKRgACg3GMmYjF9+Igx
UGwWwRMF3YJL5msAn0Le
4XoP+70vYIkNaJh/nfxGf6Nj
=Wx8M
--END PGP SIGNATURE--
Gary K wrote:
> I have been using MS Viso (the one that integrates with Visual Studio .NET
)
> to generate ddl script for my database. It uses the following update trigg
er
> code to enforce the referential integrity, but will only execute the code
for
> one row updates, it will throw an error for multi-row updates. I am wonder
ing
> why it only allows the one row, as it appears (to me) that the code will w
ork
> fine for multi-row updates as well.
> UPDATE "ReferedTable"
> SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
> FROM inserted, deleted, "ReferedTable"
> WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
> I understand that it is updating the refered key column in the related
> tables whenever the primary key column of the parent table is changed, but
I
> do not understand why it only allows one row at a time to be updated.
> Can someone please explain this for me? And can it actually be used for
> multi-row updates? If not then what would be a good way of doing it? Thanks[/color
]|||"MGFoster" wrote:

> Why not dispense w/ the trigger and use the ON UPDATE CASCADE and ON
> DELETE CASCADE methods of a Foreign Key? E.g.:
>
Mainly because SQL Server does not have ON UPDATE/DELETE RESTRICTED/SET
NULL/SET DEFAULT options. Also our database requirements specify before/afte
r
auditing which can only be done in INSTEAD OF triggers (due to the nature of
the tables used, which can't be changed, or at least not by me), and using
INSTEAD OF triggers precludes the use of UPDATE/DELETE foreign key
restrictions.
We have borrowed from the programming structure that Viso produces, in that
while we still create foreign key references they are disabled so we can
implement our own version of referential integrity + auditing.
Personally I would prefer to use another DB package, but unfortunately to
keep things cheap and easily integratable with our MS Office products we are
stuck with SQL Server.
Thanks for the reply MG, but sorry, it's not something we can use.|||Gary K wrote:
> "MGFoster" wrote:
>
> Mainly because SQL Server does not have ON UPDATE/DELETE RESTRICTED/SET
> NULL/SET DEFAULT options. Also our database requirements specify before/af
ter
> auditing which can only be done in INSTEAD OF triggers (due to the nature
of
> the tables used, which can't be changed, or at least not by me), and using
> INSTEAD OF triggers precludes the use of UPDATE/DELETE foreign key
> restrictions.
> We have borrowed from the programming structure that Viso produces, in tha
t
> while we still create foreign key references they are disabled so we can
> implement our own version of referential integrity + auditing.
> Personally I would prefer to use another DB package, but unfortunately to
> keep things cheap and easily integratable with our MS Office products we a
re
> stuck with SQL Server.
> Thanks for the reply MG, but sorry, it's not something we can use.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Is the Primary Key (PK) an Identity column? From BOL (Instead of Update
trigger):
"Usually, when an UPDATE statement that references a table attempts to
set the value of a computed, *identity*, or timestamp column, an error
is generated because the values for these columns must be determined by
Microsoft? SQL Server?. These columns must be included in the UPDATE
statement to meet the NOT NULL requirement of the column. However, if
the UPDATE statement references a view with an INSTEAD OF UPDATE
trigger, the logic defined in the trigger can bypass these columns and
avoid the error."
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQj5HZIechKqOuFEgEQJWDACePu5W/oh+PLuf3ysomu6DVtaT6IQAoLBB
y1D1g1dzEYdEoG6vh1+WNsx1
=IYDc
--END PGP SIGNATURE--|||"MGFoster" wrote:

> Is the Primary Key (PK) an Identity column? From BOL (Instead of Update
> trigger):
> "Usually, when an UPDATE statement that references a table attempts to
> set the value of a computed, *identity*, or timestamp column, an error
> is generated because the values for these columns must be determined by
> Microsoft? SQL Server?. These columns must be included in the UPDATE
> statement to meet the NOT NULL requirement of the column. However, if
> the UPDATE statement references a view with an INSTEAD OF UPDATE
> trigger, the logic defined in the trigger can bypass these columns and
> avoid the error."
>
Nope, I try to avoid those like the plague now. We use uniqueidentifiers as
ROWGUIDCOL columns, not only for 'bookmark' uses of such a column, but also
to make replication a lot easier.
The main point of my question was to confirm that the given code would
handle multi-row referential updates on a parent table primary key column
(which i have now done in a practical experiment), and to find out why the M
S
Viso designers would only let one row be updated at a time with the code.
I have confirmed that the code will perform multi-row updates and it appears
to work correctly, but as the famous quotes says, "Just because we can do
something, does it mean we SHOULD?" I am basically looking for any problems
that might arise from the use of the code.|||I should have included this in the last message, but here is the code I used
to test the multi-row update code.
use tempdb
go
-- these table testers are only so I can reuse the code if it needed any
changes (it did)
if objectproperty(object_id('tblb'), 'IsTable')=1
drop table tblb
go
if objectproperty(object_id('tbla'), 'IsTable')=1
drop table tbla
go
create table tbla ( -- our parent table in the relationship
-- (i couldn't be bothered typing in GUIDs so we use a tinyint pk)
ii tinyint NOT NULL PRIMARY KEY,
ll varchar(50) NULL
)
go
create table tblb ( -- our child table in the relationship
ii tinyint NOT NULL PRIMARY KEY,
ll varchar(50) NULL,
ia tinyint NOT NULL,
-- and this is our relationship contraint
CONSTRAINT FK_b FOREIGN KEY (ia) REFERENCES tbla (ii)
)
go
-- we are going to look after the integrity, so we disable the constraint
alter table tblb nocheck constraint FK_b
go
create trigger tbla_upd on tbla for update as
begin
-- our test trigger is only for the update condition, and since we control
what is
-- going to happen we can skip all the extra code we will be using.
-- this code will only be executed (for every child table) when the primary
key is updated
update tblb
set tblb.ia = inserted.ii
from inserted, deleted, tblb
where tblb.ia = deleted.ii
end
go
-- insert parent table values
insert tbla values (1, 'first')
insert tbla values (2, 'second')
insert tbla values (3, 'third')
-- insert child table values
insert tblb values (1, 'first/first', 1)
insert tblb values (2, 'first/second', 2)
insert tblb values (3, 'first/third', 3)
insert tblb values (4, 'second/third', 3)
insert tblb values (5, 'second/second', 2)
insert tblb values (6, 'third/second', 2)
insert tblb values (7, 'second/first', 1)
insert tblb values (8, 'third/third', 3)
go
-- what it looks like before we change things
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go
-- now we change the pk of the 'second' series to a new unique value
update tbla set ii=4 where ii=2
go
-- and we see what we get (works ok!)
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go
-- now we change the pk of the 'third' series to a value that is already in
use
-- (error testing, and yes it does throw an error as it is supposed to)
update tbla set ii=4 where ii=3
go
-- and then we see what we get after the change (which doesn't happen)
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go|||Gary,
The code looks wrong to me, but even so, it can't be
fixed unless there is another candidate key on the table.
First off, there is no join condition between inserted and
either of the other two tables. If 10 rows are updated, which
of the 10 inserted.PrimaryKey values will be assigned to
ReferedTable.ReferedKey? The way this proprietary
SQL Server syntax works, an arbitrary one of the 10
possibilities will be used.
But the problem is worse than that. Because the primary
key is being updated, there is no way to identify the correct
correspondence between an old row and a new row.
Suppose the update was this:
update T set
PrimaryKey =
case PrimaryKey
when 1 then 123
when 2 then 456
end
where PrimaryKey in (1,2)
Within the trigger, there is no way to distinguish
this update from a different one:
update T set
PrimaryKey =
case PrimaryKey
when 1 then 456
when 2 then 123
end
where PrimaryKey in (1,2)
At least not without it being possible to identify which
row is which on the basis of some column or columns
other than the PrimaryKey column.
If only one row is updated, the is no ambiguity.
Steve Kass
Drew University
Gary K wrote:

>I have been using MS Viso (the one that integrates with Visual Studio .NET)
>to generate ddl script for my database. It uses the following update trigge
r
>code to enforce the referential integrity, but will only execute the code f
or
>one row updates, it will throw an error for multi-row updates. I am wonderi
ng
>why it only allows the one row, as it appears (to me) that the code will wo
rk
>fine for multi-row updates as well.
>UPDATE "ReferedTable"
>SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
>FROM inserted, deleted, "ReferedTable"
>WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
>I understand that it is updating the refered key column in the related
>tables whenever the primary key column of the parent table is changed, but
I
>do not understand why it only allows one row at a time to be updated.
>Can someone please explain this for me? And can it actually be used for
>multi-row updates? If not then what would be a good way of doing it? Thanks
>|||Gary,
Check Itzik Ben-Gan's presentation & scripts on RI in SQL 7.0 and 2000 at
http://www.sql.co.il/ug/13/Thirteenth.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gary K" <GaryK@.discussions.microsoft.com> wrote in message
news:D3D0B4F4-EC82-41E6-A527-E6202A5394F7@.microsoft.com...
> I have been using MS Viso (the one that integrates with Visual Studio
.NET)
> to generate ddl script for my database. It uses the following update
trigger
> code to enforce the referential integrity, but will only execute the code
for
> one row updates, it will throw an error for multi-row updates. I am
wondering
> why it only allows the one row, as it appears (to me) that the code will
work
> fine for multi-row updates as well.
> UPDATE "ReferedTable"
> SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
> FROM inserted, deleted, "ReferedTable"
> WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
> I understand that it is updating the refered key column in the related
> tables whenever the primary key column of the parent table is changed, but
I
> do not understand why it only allows one row at a time to be updated.
> Can someone please explain this for me? And can it actually be used for
> multi-row updates? If not then what would be a good way of doing it?
Thanks

Multi Row Insert

According to the CTP3 BOL the following code should work just fine, unfortunately is giving me the error message (below the code):

create table FamilyNames (

Id int identity,

[Name] varchar(30)

);

go

insert into FamilyNames values (

('Andersson'),

('Ben-Gan'),

('Carlsson'),

('Davidsen')

);

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

Does anybody know why this code fails?

Hi,


Try

insert into FamilyNames values

('Andersson'),

('Ben-Gan'),

('Carlsson'),

('Davidsen');

Kind regards,

Wesley

|||Thanks.

Multi Page Report Help

Hello,

I need to create a report that will use a dataset as its datasource and display each row in that dataset in its own page. For example, if the dataset returned three rows, then the report would be three pages long with each page only displaying the data from one row. Any help would be greatly appreciated!!Right click the Details section, go to Section Expert and click 'New Page After'.sql

Wednesday, March 7, 2012

MSSQL_ENG020598 - The row was not found at the Subscriber when applying the replicated command.

Hello,

We are seeing error MSSQL_ENG020598 - "The row was not found at the Subscriber when applying the replicated command", when running peer to peer transactional replication. Unfortunately the error message provides the database in which the error occurs but not the table or SQL statement that causes the conflict.

Is there a way to determine exactly what query caused the conflict - or at least what table the error occurred in?

Thanks in advance.

If you run distribution agent in a job, you can set the OutputVerboseLevel of distribution agent profile to 2.

If you run distribution agent from command line, simply set parameter -OutputVerbloseLevel to 2.

It shows the exact SQL statement that distribution agent use.

Peng

|||Thanks, this looks like it helped.

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 tableHi

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@.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|||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:6fqgg1d3f7bpim4ct0bril93j6vkedhgek@.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(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)|||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(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/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.developersdex.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|||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)|||Andre (spam@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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)|||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:ipfkg118so3erd23b2sqa65kcsbosi585f@.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.|||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)

Monday, February 20, 2012

MSSQL Question

Is there a way to find out the last time a row was updated without creating a field to contain the timestamp?
Any help is greatly appreciated!
Thanks,
JeffAnyway you have to have field where it is possible to save last time of update. It could be datetime, varchar, etc. You can use trigger for saving data in this field or use sp during update.|||If this is a requirement you might look into a third party tool called Lumigent Log Explorer or Lumigent Entegra. www.lumigent.com