IF exists UPDATE ELSE INSERT problem

Hi,

I have a 'Products' table (with: 'uid' and 'CatName' columns) and 'ProductCategory' table (with: 'uid', 'ProductID', 'CategoryID' columns).

I got stored procedure below to update or insert new row to 'ProductCategory' table whenever 'Products' table has been updated or new products has been added to it.

Update part works just fine but when new row has been added to 'Products' this storedProc dosn't insert it into 'ProductCategory' table, it does that only when 'ProductCategory' table is empty, I'm afraid it's because first column 'uid' in 'ProductCategory' table is an Identity column... I'm not sure how should I go about that problem. This is my stored procedure:

DECLARE @.CatNo INT, @.CatName varchar(10)
SET @.CatNo = 2
SET @.CatName = 'bracket'
IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @.CatName )
 BEGIN
 UPDATE ProductCategory SET CategoryID = @.CatNo
 FROM Products WHERE Products.CatName = @.CatName and ProductCategory.ProductID = Products.uid
 END
ELSE
 BEGIN
 INSERT INTO ProductCategory ( ProductID, CategoryID)
 SELECT uid, @.CatNo FROM Products
 WHERE Products.CatName = @.CatName
 END
SET @.CatNo = 3
SET @.CatName = 'cable'
IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @.CatName )
 BEGIN
 UPDATE ProductCategory SET CategoryID = @.CatNo
 FROM Products WHERE Products.CatName = @.CatName and ProductCategory.ProductID = Products.uid
 END
ELSE
 BEGIN
 INSERT INTO ProductCategory ( ProductID, CategoryID)
 SELECT uid, @.CatNo FROM Products
 WHERE Products.CatName = @.CatName
 END
(... Goes for another 37 categories)

Thank you for help.

KoobaWhat kind of error message returned?
Check if there are unique index? or referencial integratiies ?

If you insert Identity column, make sure turn on IDENTITY_INSERT.|||Do you actually have that exact same code 39 times? That's not good. You can cut out all of that excess code with a few smart statements and a table with your category names.

Example:

Create a table:
Cat (CatNo, CatName)

Data:
(2, 'bracket')
(3, 'cable')
etc...

with your 39 cats. Then you can get rid of all those tedious repeated SQL statements with just 2 SQL statements:

update ProductCategory
 set CategoryID = Cat.CatNo
 from Products
 join ProductCategory
 on ProductCategory.ProductID = Products.uid
 join Cat
 on Products.CatName = Cat.CatName
insert into ProductCategory
 (ProductID,
 CategoryID)
 select uid,
 Cat.CatNo
 from Products
 join Cat
 on Products.CatName = Cat.CatName
 left join ProductCategory
 on ProductCategory.ProductID = Products.uid
 where ProductCategory.ProductID is null

This 2 statements will do exactly the same as your 78.|||Looking at your question again I'm pretty sure your DB is not normalised. From your vague description, I believe your tables should be:

ProductCategory (ProductID, CategoryID)
Category (CategoryID, CategoryName) -- CategoryID should be auto-increment identity
Product (ProductID, CategoryID) --ProductID should be auto-increment identity

I also understand from your question and your existing stored proc that you link all categories into all products, in which case the ProdutCategory table becomes redundant unless it stores another column or two.

IF EXISTS statement in my perl program

I am having trouble finishing my query.

This is what I have:

IF EXISTS(Select ApplicationID from Application Where Application = '&_')
Insert Into PCApp(ApplicationID, SystemNetName)
Values( , $HoH->{Host}{SystemNetName})

I am not sure what to put in the blank within the Values parenthesis. I need to obtain the ApplicationID that is checked in the IF EXISTS section. But I cannot put a select statement into the Values() section.

Any suggestions would be appreciated.

Thanks,
LauraUse a SELECT statement instead of the VALUES clause.

-PatP|||And using a select statement will still insert the values into the table?|||In an INSERT statement, you can use the VALUES clause for a list of constants, or a SELECT clause for a list of expressions. The SELECT can include multiple rows and/or contain UNION operators to create multiple row inserts using just one INSERT statement. The SELECT buys you the ability to use expressions (including function calls), generate multiple rows, etc.

-PatP|||Ok I think I got it. Off the subject, can the IF...ELSE Contain an embedded IF...ELSE? Is it ok to have two inserts with the if section?

For example,

IF NOT EXISTS (Select ApplicationID from Application Where (Application = '$_' ))
INSERT INTO Application(Application)
Values('$_')

INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')

ELSE
INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";

Thanks for your help.
-Laura|||I think what you meant was:IF NOT EXISTS (Select ApplicationID
FROM Application Where (Application = '$_' ))
BEGIN
INSERT INTO Application(Application)
Values('$_')

INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_'
AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')
END
ELSE
INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_'
AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";Note the addition of the BEGIN...END (in red) to your code.

-PatP|||CROSS JOIN?

And what's with the double quote on the end?|||Ok, so the cross join isn't the way that I'd approach it, but it would work... Kind of like the way you had to construct joins using the pre-SQL-89 syntax. Ugly, but adequate to the job!

The double quote hanging off of the end is actually because Laura is taking this SQL out of the middle of her Perl code. It isn't really part of the SQL syntax at all.

-PatP|||Really...OK

Laura...start writting stored procedures and execute them instead....

Never did learn Pearl...though we did use it for an Oracle project once...

I gotta find a Rexx interpreter for Windoze....|||Originally, I used a left join but when I tested my query in sql server's enterprise manager it automatically changed it to cross join.

The begin and end worked. Thanks for the help.

-Laura|||I gotta find a Rexx interpreter for Windoze....I've never tried it, but I've heard that Reginald (http://www.borg.com/~jglatt/rexx/win32/rxusrw32.htm) isn't too bad.

-PatP|||Yeah, I am pretty new at SQL Server. Just started learning it last month because I am going to be the database administrator. So, I have not begun using stored procedures yet, but I will.

Thanks,
Laura|||Hold the phone...Enterprise Manager?

Do you mean Query Analyzer?

And Pat....RxSocks....Not that I'll find a practical application for it...(Well maybe I could replace DTS), but I bet I can get it to talk to SQL Server...

Very cool

Thanks|||Enterprise Manager -> Opened the table view to see the records that were stored -> on the top toolbar there is an sql button that I tested my statements. It probably isn't the best way, but I wanted to have quick access to my table design and query results.

I am aware of Query Analyzer but I have not used it much.

-Laurasql

If Else question URGENT

I want to run a query that will insert rows into another table.

I also want to do some calculations on a couple of the columns:


SELECT
KEYCODESTRINGDESCRIPTION,
STRING,
Mailed,
Sales,
Orders,
CATALOGTITLE,
Response = Orders / Mailed,
[Average Invoice] = Sales / Orders,
SMP = (Sales / Mailed) * 1000

INTO TP_GA_REPORT
FROM TP_GA_REPORT_TEMP

I have a condition where some of the colums might have a 0 in them, which of course causes a "Divide by 0" error. What I would like to do is put an IF statement in the query to deal with this 0.

i.e.

if orders = 0 then response=0
else response = Response = Orders / Mailed

Hope this makes sense!

Thanks KenTry using SQL CASE:

SELECT ...,
CASE WHEN Mailed = 0 THEN 0 ELSE Orders / Mailed END,
...
FROM ...|||Thank you!!! So very much, worked like a charm!

If conditional problem in T-Sql

I encounter a T-Sql problem related to if conditional processing:

The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.

Could anyone offer some advice?

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

Were you trying to do this for entire column or for each row in the column? Col_length will always return the size as defined in the DDL. So your IF statement will always return true.

|||

Well the code that you have written is fine it should work perfectly.

Can you provide the code that you are using for droping the column of the table ?

|||

Alternatively if you want to check for existence of a column you could query the syscoumns table:

IF
EXISTS(Select*fromsyscolumnswhere [Name]='ReportPreferences'and Id=Object_Id('ReportTitle'))
Begin
 --Do your insert
End

|||

Hi,

Thanks for your alternative way of querying system table for column existence.

However the problem still persists: even though the EXISTS clause is evaluated to be false, the query engine is still trying to insert statement, resulting in an error:

Server: Msg 207, Level 16, State 3, Line 6
Invalid column name 'ReportTitle'.

This is a very strange phenomena.

- Yubo

|||Can you repost your new query and the error message pls?|||

From your earlier which I am copy pasting here:

**************************************************

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

***************************************************

It shows that ReportPreferences is the name of your table while the column name isReportTitle

While if you have just copy pasted the querry from ndinakar which is :

***************************************************

IF

EXISTS (Select * from syscolumns where [Name] = 'ReportPreferences' and Id = Object_Id('ReportTitle'))

Begin

-- Do your insert

End

***************************************************

The sequence of the name of the table is wrong.

Please try this instead and I am sure your problem would be solved :) .

If EXISTS (Select * from syscolumns where [Name] = 'ReportTitle' and id = Object_Id('ReportPreferences'))
Begin
Print ('yes')
End
Else
Begin
Print ('no')
End

And if this post does answer your question please dont hesitate to mark it as Answer.

Regards,

sql

if condition with stored procedure

i am trying to use INSERT statement based on some condition not WHERE but using IF condition on the database. something like
INSERT INTO table1 if id = @.id or order = @.fdfdfd.

i think it will return no of rows affected with insert statement. am i right?
any help will be appreciated.First of all, i am not clear with your question.

If you want to insert based on some condition, then specify the insert statment inside an if statement.

Regards
Ravi

IDs of updated records

I have to update a table, and after I update I need to insert a record
for each updated record in some other table.
I need to know the IDs of the records which were updated in the first
table, so that when I insert records in the second table then I can put
that ID in a field.
How would I acheive this?
Thanks in advance.With a trigger I suppose.
CREATE TRIGGER dbo.UpdateBaseTableName
ON dbo.BaseTableName
FOR UPDATE
AS
IF @.@.ROWCOUNT > 0
INSERT AuditTable(id_column) SELECT id_column FROM inserted;
GO
See the topic "CREATE TRIGGER" in Books Online for more details.
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1138209391.561493.167780@.g43g2000cwa.googlegroups.com...
>I have to update a table, and after I update I need to insert a record
> for each updated record in some other table.
> I need to know the IDs of the records which were updated in the first
> table, so that when I insert records in the second table then I can put
> that ID in a field.
> How would I acheive this?
> Thanks in advance.
>|||On 2005, your the OUPUT option of the UPDATE command. If earlier version, do
a SELECT first based on
the WHERE condition to know the ID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1138209391.561493.167780@.g43g2000cwa.googlegroups.com...
>I have to update a table, and after I update I need to insert a record
> for each updated record in some other table.
> I need to know the IDs of the records which were updated in the first
> table, so that when I insert records in the second table then I can put
> that ID in a field.
> How would I acheive this?
> Thanks in advance.
>sql

IDENTITY_INSERT Problem

Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
2.8) in that I cannot insert a specific value to an identity field. (lines
below with >>> are code lines. I am using Python, but the syntax should be
about the same as VBScript)

First, I create an ADO Connection and create my table.

>>> c = win32com.client.Dispatch('ADODB.Connection')

>>> dsn = 'DRIVER=SQL
Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
Access Components;SERVER=SERVER\INSTANCE;"'

>>> c.Open(dsn)

>>> sql = 'CREATE TABLE Table_Name ('

>>> sql += 'ID_Field INTEGER PRIMARY KEY IDENTITY(1,1), '

>>> sql += 'Field_2 nchar(50) NOT NULL, '

>>> sql += 'Field_3 FLOAT DEFAULT 0.0)'

>>> c.Execute(sql)

This works fine. Then, I attempt to allow insertion into the ID_Field.

>>> c.Execute("SET IDENTITY_INSERT Table_Name ON")

This seems to work in that it does not throw an error and gives a return
of -1. Then I open a Recordset

>>> r = win32com.client.Dispatch('ADODB.Recordset')

>>> r.Open('Table_Name', c, 2, 4)

Last, I am attempt to add a record to the recordset with an explicit ID,

>>> r.AddNew()

>>> r.Fields.Item('ID_Field').Value = 45

but this fails with the error of

"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done."

Even worse, if I now try to set the identity field to allow inserts again,
Updating() causes an error that I must use an explicit value for ID_Field,
but if I try to give it one, it fails with the above error. I have to
destroy the recordset object at this point to get any further.

I am told that SET IDENTITY_INSERT only remains active for one statement and
thus must be combined with the insert, but I do not know how to do this.

There is a similar sounding bug w/ SQL 7
(http://support.microsoft.com/defaul...b;EN-US;253157), but there
is no indication that it affects newer versions of the DB. Does anyone have
any suggestions or ideas?

Thanks for any help,

-d"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in message news:<10gas90gh9n8j44@.corp.supernews.com>...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8) in that I cannot insert a specific value to an identity field. (lines
> below with >>> are code lines. I am using Python, but the syntax should be
> about the same as VBScript)

<snip
I haven't done much ADO programming, so I can't really say much about
the specific error, except to note that this KB article suggests
reviewing the connection string:

http://support.microsoft.com/defaul...kb;EN-US;269495

You might want to try connecting like this instead, to see if it makes
a difference:

c.Provider = 'sqloledb'
dsn = 'Server=MyServer;Database=MyDB;Trusted_Connection= Yes'
c.Open(dsn)

Apart from that, SET IDENTITY_INSERT remains on for your session until
you turn it off, and it can only be on for one table at a time. So I'm
not sure what you mean by putting it together with the INSERT.

One option to consider is to encapsulate your INSERT in a stored
procedure, then call the stored procedure rather than updating the
recordset directly. I don't know how well this fits with what you're
trying to do, but using stored procedures is good practice anyway:

create proc dbo.MyProc
@.ID_Field int,
@.Field_2 nchar(50),
@.Field_3 float
as
set nocount on
begin
set identity_insert dbo.Table_Name on
insert into dbo.Table_Name
(ID_Field, Field_2, Field_3)
values (@.ID_Field, @.Field_2, @.Field_3)
set identity_insert dbo.Table_Name off
end

Simon

IDENTITY_INSERT persistency

Hi all, quick question:

Is the IDENTITY_INSERT persistent, or only for a single transaction. I'm of course trying to insert into a database that has Idenity, and was wondering if I can just have a stored procedure run at startup to loop through all tables with identity fields and set IDENTITY_INSERT to on.

If not, I'll just have code up scripts to restructure the tables.

Thanks,

CooperThe IDENTITY_INSERT setting is persistant how ever what you want to do won't work. From BOL...

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.sql

Identity_Insert OFF

I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
What i should do ?
thank you
This is sample code pasted from SQL Books Online from the SET
IDENTITY_INSERT property:
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO
You can find the answers to almost every question about syntax in BOL.
HTH,
Mary
On Wed, 14 Apr 2004 23:06:03 -0700, George
<anonymous@.discussions.microsoft.com> wrote:

>I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
>What i should do ?
>thank you

IDENTITY_INSERT is set to OFF

I am trying to insert a new record to a table in my application created by VWD Express. I get beack the responce "Cannot insert explicit value for identity column in table 'Tradersa' when IDENTITY_INSERT is set to OFF" . I have a key record in the table which I would like to increment automatically as I add records so I have set the is identity value to true and both the identity seed and increment to 1.

I have done a fair bit or searching but do not know how to set the table value of IDENTITY_INSERT to ON. Is this as the table is set up or as the record is about to be added? I beleive I should set this when I add the record, but do not know how to in VWD.

Any help would be most welcome. Many thanks in advance

Looks like you are trying to insert a value into a column that has been defined as IDENTITY column? Is that right?|||You need to change the Identity Insert mode. Have a read ofthis article it should explain what is going on.|||

Yes the column is set as IDENTITY. After a bit more reading I think that the issue is with the explicit naming of the identity column. I don't believe that I am explicity defining the field just as @.Trader_ID.

|||Thanks for this, I would like to insert the field without speciying it so that it will increment automatically. The solution in the doc seems to specify the record to be added to the identity field.

IDENTITY_INSERT

dear jeff johnson ,
thanks for ur response.
I have one table name fixtures in client machine .In that table one column
is identity type .now i am insert one Row from server database to that clien
t
database
now i have to on the identity_insert in client machine from server machine
give some suggestion.
sangeetha-server machine
sankar--client machine
i am running this query from my machine my machine name is sangeeta
EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
"set identity_insert scoremate.dbo.fixtures on "'
insert into openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select * from scoremate.dbo.fixtures')
select compcode,seasonid,matchid,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select compcode,seasonid,matchid=15,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
scoremate.dbo.fixtures where matchid=13')Hi
Everything seems to point to the client machine?
You may be better off connecting to the server where you are doing the
inserts and not using your OPENROWSET as the destination of the insert.
John
"MOHAMED NASEER" wrote:

> dear jeff johnson ,
> thanks for ur response.
> I have one table name fixtures in client machine .In that table one colum
n
> is identity type .now i am insert one Row from server database to that cli
ent
> database
> now i have to on the identity_insert in client machine from server machine
> give some suggestion.
> sangeetha-server machine
> sankar--client machine
> i am running this query from my machine my machine name is sangeeta
> EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
> "set identity_insert scoremate.dbo.fixtures on "'
> insert into openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select * from scoremate.dbo.fixtures')
> select compcode,seasonid,matchid,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select compcode,seasonid,matchid=15,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> scoremate.dbo.fixtures where matchid=13')sql

IDENTITY values in a stored procedure

Hi All,
This is my stored procedure

CREATE PROCEDURE testProc AS
BEGIN
CREATE TABLE #tblTest(ID INT NOT NULL IDENTITY, Col1 INT)
INSERT INTO #tblTest(Col1)
SELECT colA FROM tableA ORDER BY colA

END

This is my simple procedure, I wanted to know whether the IDENTITY values created in #tblTest will always be consistent, I mean without losing any number in between. i.e. ID column will have values 1,2,3,4,5....
or is there any chance of ID column having values like 1,2, 4, 6,7,8...

Please reply...
qaAs long as you don't do any deletes from your temp table, your identity column should remain sequential with no gaps.|||Thanks for your quick response.sql

IDENTITY value copy in INSERT statement

Hello,
I would like to insert the value of an identity column into an other field
during the same insert statement and not by using a trigger.
Sample Table
CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
Sample statements NOT working but to indicate what I would like to do
INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
Is this possible and if yes, could you please inform me how?
Thanks in advance,
RemcoWithout using a trigger (error handling omitted):
CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
DECLARE @.ID int
BEGIN TRAN -- these next two data operations should be atomic
INSERT INTO Test(DocumentParentID) VALUES (NULL)
SET @.ID = @.@.IDENTITY
UPDATE Test SET DocumentParentID = @.ID WHERE DocumentID=@.ID
COMMIT
INSERT INTO Test(DocumentParentID) VALUES (@.ID)
I dont know of a way to acheive this inline using identity.
Mr Tea
"Remco" <rembo_r@.hotmail.com> wrote in message
news:OEmKrq0FFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I would like to insert the value of an identity column into an other field
> during the same insert statement and not by using a trigger.
> Sample Table
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
>
> Sample statements NOT working but to indicate what I would like to do
> INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
> INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
>
> Is this possible and if yes, could you please inform me how?
> Thanks in advance,
> Remco
>|||I prefer to use SCOPE_IDENTITY( ) unless you are using SQL Server 7 then use
@.@.identity
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:n10Sd.124$u56.22@.newsfe5-win.ntli.net...
> Without using a trigger (error handling omitted):
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
> DECLARE @.ID int
> BEGIN TRAN -- these next two data operations should be atomic
> INSERT INTO Test(DocumentParentID) VALUES (NULL)
> SET @.ID = @.@.IDENTITY
> UPDATE Test SET DocumentParentID = @.ID WHERE DocumentID=@.ID
> COMMIT
> INSERT INTO Test(DocumentParentID) VALUES (@.ID)
> I dont know of a way to acheive this inline using identity.
> Mr Tea
> "Remco" <rembo_r@.hotmail.com> wrote in message
> news:OEmKrq0FFHA.2156@.TK2MSFTNGP09.phx.gbl...
field
>|||thanks for the tip :)
Mr Tea
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6mVLN1FFHA.3972@.TK2MSFTNGP15.phx.gbl...
>I prefer to use SCOPE_IDENTITY( ) unless you are using SQL Server 7 then
>use
> @.@.identity
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:n10Sd.124$u56.22@.newsfe5-win.ntli.net...
> field
>|||Why does the document reference itself as its own parent? Typically an
adjacency list hierarchy in a table looks like this:
CREATE TABLE Documents (documentid INTEGER NOT NULL PRIMARY KEY,
parent_documentid INTEGER NULL REFERENCES Documents (documentid))
The root nodes of the tree then have NULL as the parent_documentid. If
you use IDENTITY as the key then will need either a trigger or an
INSERT followed by an UPDATE to populate a self-referencing parent id.
David Portas
SQL Server MVP
--|||>> I would like to insert the value of an identity column [sic] into
an other field [sic] during the same insert statement and not by using
a trigger. <<
IDENTITY is a table property that exists only in the machine, not in
the data model. Columns and fields are totally different concepts.
And it looks like you are trying to use an adjacency list model for a
hierarchy. Try a nested sets model and all of your problems go away and
you avoid proprietary code.
CREATE TABLE Documents
(document_id INTEGER NOT NULL,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );
I have a whole book on trees and hierarchies in SQL.|||>> I would like to insert the value of an identity column [sic] into
an other field [sic] during the same insert statement and not by using
a trigger. <<
IDENTITY is a table property that exists only in the machine, not in
the data model. Columns and fields are totally different concepts.
And it looks like you are trying to use an adjacency list model for a
hierarchy. Try a nested sets model and all of your problems go away and
you avoid proprietary code.
CREATE TABLE Documents
(document_id INTEGER NOT NULL,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );
I have a whole book on trees and hierarchies in SQL.|||Try using Ident)Seeed()
as in
INSERT INTO Test(DocumentParentID) VALUES (Ident_Seed('Test'))
"Remco" wrote:

> Hello,
> I would like to insert the value of an identity column into an other field
> during the same insert statement and not by using a trigger.
> Sample Table
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
>
> Sample statements NOT working but to indicate what I would like to do
> INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
> INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
>
> Is this possible and if yes, could you please inform me how?
> Thanks in advance,
> Remco
>
>|||IDENT_SEED returns seed value, not the identity column value.
If you have a column defined as IDENTITY(1,1), The IDENT_SEED
Function will return 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CBretana" <CBretana@.discussions.microsoft.com> wrote in message
news:F02EA293-84B0-4A8F-BA4A-A896EE548D33@.microsoft.com...
> Try using Ident)Seeed()
> as in
>
> INSERT INTO Test(DocumentParentID) VALUES (Ident_Seed('Test'))
>
> "Remco" wrote:
>|||I tested it with a newly created table, and it returned a '1', which was
both the seed and the value to be inserted... <gr>. It's Ident_Current()
That is needed here.
Try using Ident_Current()
as in
INSERT INTO Test(DocumentParentID) VALUES (Ident_Current('Test') + 1)
"Roji. P. Thomas" wrote:

> IDENT_SEED returns seed value, not the identity column value.
> If you have a column defined as IDENTITY(1,1), The IDENT_SEED
> Function will return 1
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "CBretana" <CBretana@.discussions.microsoft.com> wrote in message
> news:F02EA293-84B0-4A8F-BA4A-A896EE548D33@.microsoft.com...
>
>sql

Identity sequence for multithreads insert

Recently I'm working on a multi-thread solution based on SQL-Server, now I'm facing such a problem:

Suppose I have process No.1(with multi-threads) inserting data to Table A, which has its identity column auto generated. And process No.2(also with multi-threads) retrieving data from Table A ,generate some records and insert the result into Table B. Both of these two processes are doing batch processing(batch retrieving and batch writing), and they are running parallelly.

Now since process No.2 retrieve data sequencely by the identity of Table A, it found there exists missing results. This is due to that records with bigger identities are not necessarily commited earlier than those who have smaller identities.

One direct solution is add one flag field in Table A indicating whether this record has been processed by process No.2, and each time it was processed , the field will be set. But unfortunatelly the table structure is not supposed to be modified.

So is there any other good solutions for this problem? Thanks.

A solution may be that process 1 uses row-level locking when select/update and process 2 use the readpast hint when selecting the records:

READPAST specifies that locked rows be skipped during the read. READPAST only applies

to transactions operating at the default READ COMMITTED isolation level, and will

only read past row-level locks. READPAST can only be used in SELECT statements.

Normal blocking can be worked around by having transactions read past rows being

locked by other transactions.
See the following article: http://www.sql-server-performance.com/rd_table_hints.asp

Else I cannot see any other solution except adding some external data structure to keep track of processing status.|||

Hi carlop, I'm afraid locks won't resolve this issue.

Maybe I should explain my problem more detailly.

Suppose Process No.1 have 3 threads working , and here are the identities generated after the insertion.

thread 0 : 1 , 4, 7

thread 1: 2 , 5, 8

thread 2: 3, 6, 9

And at one time point, thread 0 commited the transaction with 3 rows inserted , whilst Process No.2 is retrieving data from Table A , so only these 3 records were retrieved and processed. So Process No.2 will record "7" as the biggist identity it has processed, and next time it will start processing from identities greater than "7" . So records with identities 2, 5,3,6 are lost if thread 1 and thread 2 commited later.

|||You have to add an external struct that keeps track of the processing jobs. I see no other way.|||You could add another table that is logged to on insert to table A. Process 2 reads these records and deletes them from the new tableonce processed. When it goes back for a second time it reads the nest set to process and then deletes from. You could use Service Broker if you wanted as it has a nice queue mechanism|||

Maybe this is the only solution I think.

Thank you all.

Identity seed lost...?

Dear all,
The last value I see for a identity field is 174. That's fine.
But the next value after insert which appears is 217 instead of 175. How do
I force the sequence 'natural' again'
I suppose that it happen due to I deleted some rows...
I would need in order to add a new row into a another table.
Thanks in advance,
EnricEnric
SET IDENTITY_INSERT
Be aware that an IDENTITY property may have gaps as well , and if it is
important , you can change to the natural key and add value to maximal key
SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How
> do
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||Thanks for your post, anyway I will not know which will be the next value in
case I delete some rows.
"Uri Dimant" wrote:
> Enric
> SET IDENTITY_INSERT
> Be aware that an IDENTITY property may have gaps as well , and if it is
> important , you can change to the natural key and add value to maximal k
ey
> SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
>
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
>
>|||Try this ...
DBCC CHECKIDENT(tablename, RESEED, 0)
DBCC CHECKIDENT(tablename, RESEED)
"Enric" wrote:

> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How d
o
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||"Enric" wrote:
> Thanks for your post, anyway I will not know which will be the next value
in
> case I delete some rows.
> "Uri Dimant" wrote:
If you want a gapless sequence then IDENTITY is the wrong solution. Don't
use IDENTITY in a way that has meaning for your users precisely because you
can't always control the IDENTITY value. IDENTITY is intended to be used as
an artificial surrogate key only.
Why do you need an IDENTITY column and why do you care if the sequence has
gaps?
David Portas
SQL Server MVP
--

identity range check constraint

SQL 2005 merge replication.
I took the defaults on an article for publication - auto identity
management and the default ranges. If I attempt to insert >2000 rows
(the size of the 2 ranges assigned), I recieve the following error:
The insert failed. It conflicted with an identity range check
constraint in database 'DHD_73', replicated table
'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
column is automatically managed by replication, update the range as
follows: for the Publisher, execute sp_adjustpublisheridentityrange;
for the Subscriber, run the Distribution Agent or the Merge Agent.
Does this mean I cannot do an insert of more than 2000 rows at a time
to this particular article?
TIA,
john g.
Sorry, I forgot to add, I am doing the inserts on the publisher
jg
jgmein...@.gmail.com wrote:
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
|||You can batch up the inserts, or increase the size of the assigned rabge,
otherwise you're stuck.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Check the constraint to see what the range is. You can insert up to this
value and then run a sync. This should update the range.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jgmeinder@.gmail.com> wrote in message
news:1167937361.253647.109410@.51g2000cwl.googlegro ups.com...
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
>

Identity order

Hi,

I have some tables in a database with a identity (autoincrement) column (PK).

After several operations (INSERT, UPDATE and DELETE), some holes appeared in the identity column, like this:

ContactId Contact

1 John

2 Mary

5 Sam

9 David

where ContactId is the identity column.

Can I order the ContactId column, by removing the empty spaces, in order to the table appears like this?:

ContactId Contact

1 John

2 Mary

3 Sam

4 David

(I'm using SQL Server 2005.)

Thank you in advance.

Identity values are meant to be unique (but not technically guaranteed) but they won't necessary be sequential or without any gaps. An identity is probably not going to work well for this scenario. You can create another table to manage this. And this may give you another option:

http://blogs.msdn.com/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

However, you will still have issues with deletes - those won't be easy to manage if this is your requirement.

-Sue

|||

Sue:

Thank you for your answer.

I understand what you mean. But, after the table is filled with data, can I apply any command in order to put the identity values in sequential order? (Maybe 'ALTER INDEX' or 'DBCC' commands.)

I ask this, because I think identity column value must have a limit (maybe integers maximum limit in C language), and after long time with too much database operations, perhaps that limit be reached and several empty spaces (talking of auto generated values) remains in the table.

I beg your pardon for my silly question, but I'm newbie with SQL Server.

Anyway, thanks a lot.

--

Adrián

|||

You can change the number it's seeded at - for example if the next contact id number will be 100 but your last contact id in the table is 50 - using DBCC CHECKIDENT but it won't do anything about the gaps. For that, you would need to do something along the lines of creating a new table, populate the exiting data in the old table into the new table, drop the old table and rename the new table.

If the issue with gaps is that you want to use the number and are afraid you will run out of numbers, if the data type is an int, you can go up to 2,147,483,647 and then after exhausting positive values it will start using negatives through the value -2,147,483,648. So you have over 4 billion to work with there. If you double your storage space and use bigints the range is larger. It would handle thousands of ids generated per second over the course of over 100 years. I can remember the details but I really doubt that you would run out of numbers. You would of course want to use an appropriate data type due to the difference in storage required.

-Sue

|||

Thank you very much.

Your answer was very clear.

Identity order

Hi,

I have some tables in a database with a identity (autoincrement) column (PK).

After several operations (INSERT, UPDATE and DELETE), some holes appeared in the identity column, like this:

ContactId Contact

1 John

2 Mary

5 Sam

9 David

where ContactId is the identity column.

Can I order the ContactId column, by removing the empty spaces, in order to the table appears like this?:

ContactId Contact

1 John

2 Mary

3 Sam

4 David

(I'm using SQL Server 2005.)

Thank you in advance.

Identity values are meant to be unique (but not technically guaranteed) but they won't necessary be sequential or without any gaps. An identity is probably not going to work well for this scenario. You can create another table to manage this. And this may give you another option:

http://blogs.msdn.com/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

However, you will still have issues with deletes - those won't be easy to manage if this is your requirement.

-Sue

|||

Sue:

Thank you for your answer.

I understand what you mean. But, after the table is filled with data, can I apply any command in order to put the identity values in sequential order? (Maybe 'ALTER INDEX' or 'DBCC' commands.)

I ask this, because I think identity column value must have a limit (maybe integers maximum limit in C language), and after long time with too much database operations, perhaps that limit be reached and several empty spaces (talking of auto generated values) remains in the table.

I beg your pardon for my silly question, but I'm newbie with SQL Server.

Anyway, thanks a lot.

--

Adrián

|||

You can change the number it's seeded at - for example if the next contact id number will be 100 but your last contact id in the table is 50 - using DBCC CHECKIDENT but it won't do anything about the gaps. For that, you would need to do something along the lines of creating a new table, populate the exiting data in the old table into the new table, drop the old table and rename the new table.

If the issue with gaps is that you want to use the number and are afraid you will run out of numbers, if the data type is an int, you can go up to 2,147,483,647 and then after exhausting positive values it will start using negatives through the value -2,147,483,648. So you have over 4 billion to work with there. If you double your storage space and use bigints the range is larger. It would handle thousands of ids generated per second over the course of over 100 years. I can remember the details but I really doubt that you would run out of numbers. You would of course want to use an appropriate data type due to the difference in storage required.

-Sue

|||

Thank you very much.

Your answer was very clear.

identity inserts

Hey All,

I was trying to use a typed dataset to create a very simple DAL. I found that the code generated for the INSERT statement includes an identity field the table has. That can obviously never work (unless identity_insert is set, which it is not). My question is whether it is possible to control this insert statement generation? Is there a property I am missing somewhere? My solution was to change the INSERT statement on the DataTableAdapter, but that seems awkward for me to have to do that..

Thanks,

Yuval

From SQL side you can turn on an option to enable identity inserts to a table:

SET IDENTITY_INSERT ONmyTable

Then you can insert values specifying columns and identity value:

INSERT INTOmyTable (id,name) VALUES(100, 'Iori')

Note: this is a session option, which means you have to turn on this option for every connection you want to insert identity values. For more information about this option, please refers to

http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_7zas.asp?frame=true

|||In my post, I actually said that this is not what I am trying to achieve. The problem is that the dataset generates an insert statement that includes the identity column and I have to edit it to not do that.|||Sorry for misunderstood you:)I've no idea of your issue, waiting for right answer...

Identity insert on

After setting identity_insert on for a table is there any way by which I can insert multiple or a range of records at a time?From where?

INSERT INTO myTable SELECT * FROM myOtherTable?

Or do you mean From a file?

BULK INSERT INTO myTable FROM 'C:\TEMP\newdata.dat'|||I mean from any table between a range of data.|||I mean from any table between a range of data.|||Well the INSERT INTO should do it with a predicate (WHERE clause)

Your next statement will be...

How do I get the middle 500 rows...

Yes?