INSERTED table

I have a qn regarding the INSERTED table.

Whenever a row is inserted into the table i understand that the INSERTED table also gets that particular row. But how long does that particular row stay thr? Till another new row is inserted into the table? which means that rows get overwritten whenever a row is inserted?

Hope everyone understands what i am trying to say. Would be kind of you to reply.thanks!

GayathriPlease provide an example of the sql.|||SELECT JobNumber
from inserted
where ServiceType = 'On-site' and ServiceStatus = 'NEW' and DateModified=(SELECT MAX(DateModified) from inserted)

when i execute this statement alone i will only get one job number but when i put this into a trigger and channel the output into a cursor to copy it into a variable it selects some other job number as well...This is the whole code

DECLARE job_number_cursor CURSOR FOR
SELECT JobNumber
from inserted
where ServiceType = 'On-site' and ServiceStatus = 'NEW' and DateModified=(SELECT MAX(DateModified) from inserted)
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor

I was hoping to get just one output since i thought the INSERTED table only contains the last inserted row.|||I understand what you are asking...

Basically you are saying,...

When a table has a trigger on it the trigger has access to a table called inserted (assuming it is an insert trigger). How long does the inserted table with the insert record exist...

In all honesty, I'm not sure, but I would say it would exist until the insert and the associated trigger (if there is one) has been completed...

I'll look up some resources and see what I can find.|||Um,... question,... when you use your cursor are you doing an update or insert into the table with the trigger on it??|||I am inserting|||okie,... so lets think about that for a sec...

you are in the middle of an insert, your trigger fires which opens a cursor which does an insert (loop to start and insert a new record into the inserted table)

your cursor is still open when you do your second insert and it references the same inserted table... which now has the new record in it...

does that make sense??|||Check out your bol:

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.|||and I assume they get cleaned out once the insert is complete... including trigger execution...|||rnealejr ,I understand that INSERTED table stores copies of the rows inserted into the actual table...

So does this mean that everytime an insert or update statement is executed a new inserted table is formed?|||I think the table remains but the rows are removed after the action is completed.

The reason I think this is because according to the BOL you can reference the deleted table when doing an insert and the inserted table when doing a delete but there are no rows contained in the tables...

"When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing the deleted table while testing an INSERT, or the inserted table while testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases."|||You can have more than 1 record in the inserted table and the table is only accessible to the trigger - so the table exists as long as the trigger runs for a particular sql statement.|||So does this mean that everytime an insert or update statement is executed a new inserted table is formed?

These tables are created/stored in memory. From what I remember, I believe the scope of these virtual tables are for the life of the trigger. It would not make sense that ss would keep a table in memory any longer than needed.|||It's highly possible that they continue to exist after their usefulness has gone, after all we are talking a microsoft product and they have done stranger things in the past.

Also the amount of memory you are talking about is minimal so the effect of keeping the table alive in memory is unlikely to cause any real problems.

In fact, it is likely that the over head involved in creating the tables each time if more detrimental then kepeing them in memory especially when you consider that you are likely to do multiple updates/inserts/deletes on any given table at a time rather then constant swap around tables ...|||The inserted and deleted tables exist only within the scope of the trigger execution. Updates use both the inserted and deleted tables because they effectively insert new modified copies of the records and then delete the old ones.

gayamantra, the inserted table does not exist as a distinct and persistent object. Keep in mind that it has the same record format as whatever datatable was the subject of the operation.

New virtual tables of inserted and deleted records must be created (in memory only) for each operation, otherwise multiple users accessing the datatable would end up with their inserted/deleted records intermingling.

I would guess that there is little additional overhead in creating these virtual tables on the fly, because they may be incidental to the database server's operations anyway.|||"New virtual tables of inserted and deleted records must be created (in memory only) for each operation, otherwise multiple users accessing the datatable would end up with their inserted/deleted records intermingling."

Not necessarily, the tables could be created with a user context eg. they are specific to the user at the time... I don't really know though... lets be honest, there are quite a few methods that MS could be using... but for the nature of this discussion the inserted and deleted table exist for the duration of the insert or delete. :Dsql

Inserted and deleted temp tables ?

I want to know how inserted and deleted temp tables in SQL server work. My question is more regarding how they work when multiple users accessing the same database. Suppose two users update the database at the same time. In that case what are the values stored in the inserted and deleted tables.

I have a trigger that records changes to the database as in an audit trail. Like any other audit trail I insert data into my audit table from the inserted and deleted temp tables in MS SQL Server. I however am not clear as to how these inserted and deleted tables store values when two users update the database at the same time. Are there separate inserted and deleted tables for each session. The users access the database thru ASP pages.

The audit trail I am trying to use is http://www.nigelrivett.net/AuditTrailTrigger.html

I actually would like to store the inserted and deleted temp tables into other temporary tables so that I can access these tables thru a stored procedure. This is when the problem of same users updating the temporary tables is more pronounced.

Thanks in advance.If memory serves, the INSERTED and DELETED tables are special objects that belong to each session. So if two users update at the same time you should have two copies of these virtual tables

User1INSERTED/DELETED
User2INSERTED/DELETED

Brent

insert statement help

hi,
i have a small question regarding sql, there are two tables that i need to work with on this, one has fields like:
Table1:
(id, name, street, city, zip, phone, fax, etc...) about 20 more columns
Table2:
name
what i need help with is that table2 contains about 200 distinct names that i need to insert into table1, i'm using sql server, is there a way to insert them into table1?? i'm not sure how to write a query within the insert statment to get them inserted into table1? something like:
insert into table(id, name, street, zip, phone, fax, ...) values(newid(), (select distinct name from table2), null, null, null...)
and is there a way to do it without all the nulls having to be put in, there are about 20 more columns in table1, and id in table1 is unique.insert into table1 ( name)
select distinct name from table2

rudy

insert statement help

hi,
i have a small question regarding sql, there are two tables that i
need to work with on this, one has fields like:
Table1:
(id, name, street, city, zip, phone, fax, etc...) about 20 more
columns
Table2:
name
what i need help with is that table2 contains about 200 distinct names
that i need to insert into table1, i'm using sql server, is there a
way to insert them into table1?? i'm not sure how to write a query
within the insert statment to get them inserted into table1?
something like:
insert into table(id, name, street, zip, phone, fax, ...)
values(newid(), (select distinct name from table2), null, null,
null...)
and is there a way to do it without all the nulls having to be put in,
there are about 20 more columns in table1, and id in table1 is unique.[posted and mailed, please reply in news]

soni29 (soni29@.hotmail.com) writes:
> i have a small question regarding sql, there are two tables that i
> need to work with on this, one has fields like:
> Table1:
> (id, name, street, city, zip, phone, fax, etc...) about 20 more
> columns
> Table2:
> name
> what i need help with is that table2 contains about 200 distinct names
> that i need to insert into table1, i'm using sql server, is there a
> way to insert them into table1?? i'm not sure how to write a query
> within the insert statment to get them inserted into table1?
> something like:
> insert into table(id, name, street, zip, phone, fax, ...)
> values(newid(), (select distinct name from table2), null, null,
> null...)
> and is there a way to do it without all the nulls having to be put in,
> there are about 20 more columns in table1, and id in table1 is unique.

Your question is a bit vague, and since I don't see the tables, nor do
I see the data, I have to guess.

If all you want to is to insert the disctinct names in table2 into table1,
without providing any values for the other columns, save the id column,
this is the statement:

INSERT table1 (id, name)
SELECT disctint newid(), name FROM table2

Thus, you do need to list a column in the column list of the INSERT
statement, if you wish to set it to NULL or its default value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Check out the insert syntax in books online (use the Go/URL menus!):

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
ata.chm::/ac_8_md_03_1kz8.htm

If the columns are nullable and don't have a value or if the are not
nullable and take the default then you do not have to mention them in the
select statement. If they are nullable then the DEFAULT keyword can be used.

To create a default for your id column then it can be declare with a default
see:

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_na-nop_4pt0.htm

i.e.

CREATE TABLE cust
(
id uniqueidentifier NOT NULL
DEFAULT newid(),
....

)
GO

Therefore you can do something like:

insert into table(name, street, zip, phone, fax)
select distinct name, street, zip, phone, fax from table2

If you can not get distinct from this then you may need a subquery such as:

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_11_3smm.htm

John

"soni29" <soni29@.hotmail.com> wrote in message
news:cad7a075.0312140855.7c8b2475@.posting.google.c om...
> hi,
> i have a small question regarding sql, there are two tables that i
> need to work with on this, one has fields like:
> Table1:
> (id, name, street, city, zip, phone, fax, etc...) about 20 more
> columns
> Table2:
> name
> what i need help with is that table2 contains about 200 distinct names
> that i need to insert into table1, i'm using sql server, is there a
> way to insert them into table1?? i'm not sure how to write a query
> within the insert statment to get them inserted into table1?
> something like:
> insert into table(id, name, street, zip, phone, fax, ...)
> values(newid(), (select distinct name from table2), null, null,
> null...)
> and is there a way to do it without all the nulls having to be put in,
> there are about 20 more columns in table1, and id in table1 is unique.|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9451C9DBAF97CYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> soni29 (soni29@.hotmail.com) writes:
> > i have a small question regarding sql, there are two tables that i
> > need to work with on this, one has fields like:
> > Table1:
> > (id, name, street, city, zip, phone, fax, etc...) about 20 more
> > columns
> > Table2:
> > name
> > what i need help with is that table2 contains about 200 distinct names
> > that i need to insert into table1, i'm using sql server, is there a
> > way to insert them into table1?? i'm not sure how to write a query
> > within the insert statment to get them inserted into table1?
> > something like:
> > insert into table(id, name, street, zip, phone, fax, ...)
> > values(newid(), (select distinct name from table2), null, null,
> > null...)
> > and is there a way to do it without all the nulls having to be put in,
> > there are about 20 more columns in table1, and id in table1 is unique.
> Your question is a bit vague, and since I don't see the tables, nor do
> I see the data, I have to guess.
> If all you want to is to insert the disctinct names in table2 into table1,
> without providing any values for the other columns, save the id column,
> this is the statement:
> INSERT table1 (id, name)
> SELECT disctint newid(), name FROM table2
> Thus, you do need to list a column in the column list of the INSERT
> statement, if you wish to set it to NULL or its default value.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

A minor correction - the syntax above will generate a new uniqueidentifier
for each row in the source table before applying the DISTINCT, so you will
get all the values from the source table anyway. Something like this should
work correctly:

insert into table1 (id, name)
select newid(), name from
(
select distinct name
from table2 ) dt

Although as you pointed out, without seeing data and DDL, it's not at all
clear what 'correctly' means here, so my version may not be what the poster
wants either.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> A minor correction - the syntax above will generate a new uniqueidentifier
> for each row in the source table before applying the DISTINCT, so you will
> get all the values from the source table anyway.

Oops!

Thanks for the correction, Simon!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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