insert or replace: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

Inserting 2 tables with Pk/Fk

Hello all... I'm working on a C++ Windows service that writes to a SQL Server database. I consider myself quite a novice at SQL Server, but I have played around with it over the years... Performance is going to be a concern with this project.

Let's say...
Table A has columns PkA(identity), Stuff(text), FkB (Table B's Pk)
Table B has columns PkB(identity), MoreStuff(text)

I'll be executing SQL statements from my service - INSERTs, etc...

What's the most efficient way to write to these two tables? The immediate challenge I have is getting that PkB value after inserting Table B and using it for Table A's FkB.

Is there a way I can insert into both tables with one SQL statement?

Thanks!! Curt.First, I recommend that your service call a stored procedure to make this happen, and not issue an ad-hoc query. the sproc would do both inserts, you'd just call it with the values you need to put in Stuff and MoreStuff. So as far as your service is concerned, both inserts happen in "one statement". Within the sproc it's still two inserts though.

Second, in your sproc after your insert into tableB, you can call SCOPE_IDENTITY() to get the identity value that was just inserted. use this value as the fk in tableA when you do the insert there.

take a look at SCOPE_IDENTITY() in BOL. @.@.IDENTITY is a related beast, but SCOPE_IDENTITY() is preferred since it's scoped, as the name implies.

Edit: since I have my roots in C++ as well, thought I would add this: leaving your tables open to ad-hoc queries from client apps is like designing a class in C++ where all the fields are public. If your table structure changes, you have to recompile and redeploy your service. You wouldn't want to do that would you? :)

I think of sprocs as analogous to the public member functions on a class. use them to control how clients are allowed to manipulate the private fields (your tables), and make all fields (tables) private.|||jezemine, yeah I guess I should have qualfied that a bit more... We are in fact planning to put that into a sproc a little later on. As I mentioned, I'm not really a sql server pro and sprocs are on my list of items to conquer... Right now we just need to get something up and running to help prove concept. Thanks for the tips, though! Perhaps I'll conquer that beast sooner than I thought! :)|||ok, but remember that prototype code sometimes has a way of "sticking" :)

Inserting 1:M relationship data via One Stored Procedure

Hi,

Uses: SQL Server 2000, ASP.NET 1.1;

I've the following tables which has a 1:M relationship within them:

Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)

I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:

LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.

Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?

Eagerly awaiting a response.

Thanks,

The best reference for this kind of thing when you truly have a 1:M relationship is Erland's web page: http://www.sommarskog.se/arrays-in-sql.html

But if you have a max of 3, then just write the proc with 3 parameters (something like):

create procedure contact$insert
(
@.LastName,
...
@.MobileNo,
@.PhoneNo1,
@.PhoneNo2,
@.PhoneNo3
)
--add your own error handling of course or add SET XACT_ABORT ON that
--will stop the tran on any error

begin tran

insert into contact (lastName, ..., MobileNo) --note, assuming contactId is an identity
values (@.lastName, ..., @.MobileNo)

declare @.newContactId int
set @.newContactId = scope_identity()

insert into contactTelephone
select @.newContactId, @.phoneNo1
where @.phoneNo1 is not null
union all
select @.newContactId, @.phoneNo2
where @.phoneNo2 is not null
union all
select @.newContactId, @.phoneNo3
where @.phoneNo3 is not null

commit tran

|||

Hi Louis,

Thanks for the Response, this cleared my mind and the problem. Thank you again!

sql

inserted/deleted tables

Does the data in the rows in the inserted and deleted tables always correspond? For instance, row 1 in inserted corresponds with row 1 in deleted.
Thanks,

OK, if you

-Insert n rows you have n rows in the inserted.
-Delete n rows you have n rows in the deleted table.
-Update n rows you have n rows in the inserted and n rows in the deleted table.

So for an update the rowcount is always corresponding.

HTH, Jens Suessmeyer

|||

Well, what I was really wanting to know is if I could assume that the data in row [1] (the new data to be inserted) of the inserted table corresponds with the data in row [1] (the data that was deleted) in the deleted table.

Example:

Update people

Set person_id = (select person_id from inserted)

Where people.person_id = (select person_id from deleted)

This type of update statement will only work if there is a single row being updated. I wanted to step through the inserted and deleted tables one row at a time for multiple row updates, but I did not know if it was safe to say that the data in inserted row # corresponded with the data in deleted row #.

|||

> Update people

> Set person_id = (select person_id from inserted)

> Where people.person_id = (select person_id from deleted)

What table is this trigger attached to? People, or another table? Are you

just trying to undo the update to people, or replicate the update to another

table? In what scenario?

> This type of update statement will only work if there is a single row

> being updated.

Absolutely correct, and a very common tripping point for hundreds of people

before you.

> I wanted to step through the inserted and deleted tables

> one row at a time for multiple row updates

No, no, no. You are going about this all wrong. Think about it in SETS.

If you give some proper DDL and specs (see http://www.aspfaq.com/5006) we

can help you do this in one statement and abandon this idea of iterating

through every row and trying to match some hypothetical "row number"...

|||

> Does the data in the rows in the inserted and deleted tables always

> correspond? For instance, row 1 in inserted corresponds with row 1 in

> deleted.

There is no "row 1"... a table, by definition, is an unordered set of rows.

Typically you identify a row by some unique value, like a primary key, not

whether it came first or last or somewhere in between.

|||

Hello to everyone.

here i want to know some more details regarding inserted/deleted tables.

consider the scenario that more than 100 users are inserting/updating rows of same or othere tables of a database and tiggers of after update upon each insert and/or update is been fired.

what will be the response of the SQL 2005 server to these operations as i am moving the updated data to the audit tables from the delted table. by using the following trigger.

CREATE TRIGGER [TrigAUTblA]
ON [TblA]
AFTER UPDATE AS
BEGIN
INSERT INTO [TblAHistory]
(
[guidA],
[Description]
)
SELECT deleted.guidA,
deleted.Description
FROM deleted

Also what issues can emerge using this scenario

|||

It is possible to update the unique key for multiple rows in a table. In that case, there is nothing to correlate the rows in "inserted" to the rows in "deleted" other than the order in which they are returned by a select statement.

So the question is a valid one, I think: If a table contains one unique key, and multiple rows in that table are updated such that the value of that key changes, can we count on the rows in the "inserted" and "deleted" tables being returned in the same order so that they can be matched up one to one?

Thanks,

Ron

inserted/deleted tables

Does the data in the rows in the inserted and deleted tables always correspond? For instance, row 1 in inserted corresponds with row 1 in deleted.
Thanks,

OK, if you

-Insert n rows you have n rows in the inserted.
-Delete n rows you have n rows in the deleted table.
-Update n rows you have n rows in the inserted and n rows in the deleted table.

So for an update the rowcount is always corresponding.

HTH, Jens Suessmeyer

|||

Example:

Update people

Set person_id = (select person_id from inserted)

Where people.person_id = (select person_id from deleted)

|||

> Update people

> Set person_id = (select person_id from inserted)

> Where people.person_id = (select person_id from deleted)

What table is this trigger attached to? People, or another table? Are you

just trying to undo the update to people, or replicate the update to another

table? In what scenario?

> This type of update statement will only work if there is a single row

> being updated.

Absolutely correct, and a very common tripping point for hundreds of people

before you.

> I wanted to step through the inserted and deleted tables

> one row at a time for multiple row updates

No, no, no. You are going about this all wrong. Think about it in SETS.

If you give some proper DDL and specs (see http://www.aspfaq.com/5006) we

can help you do this in one statement and abandon this idea of iterating

through every row and trying to match some hypothetical "row number"...

|||

> Does the data in the rows in the inserted and deleted tables always

> correspond? For instance, row 1 in inserted corresponds with row 1 in

> deleted.

There is no "row 1"... a table, by definition, is an unordered set of rows.

Typically you identify a row by some unique value, like a primary key, not

whether it came first or last or somewhere in between.

|||

Hello to everyone.

here i want to know some more details regarding inserted/deleted tables.

consider the scenario that more than 100 users are inserting/updating rows of same or othere tables of a database and tiggers of after update upon each insert and/or update is been fired.

what will be the response of the SQL 2005 server to these operations as i am moving the updated data to the audit tables from the delted table. by using the following trigger.

CREATE TRIGGER [TrigAUTblA]
ON [TblA]
AFTER UPDATE AS
BEGIN
INSERT INTO [TblAHistory]
(
[guidA],
[Description]
)
SELECT deleted.guidA,
deleted.Description
FROM deleted

Also what issues can emerge using this scenario

|||

Thanks,

Ron

Inserted/deleted table.

Hi,

I am currently working on a MS SQL server 2000.

I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted -- are not accessible. anyone knows why? And is there any other way to do this?

Thankspost your t-sql code that you used to access the inserted/deleted tablessql

INSERTED table and triggers

Hi. I was dealing with triggers when a doubt came in mind.

While I can understand that the DELETED and UPDATED tables can contain more rows that have been affected by the DELETE or the UPDATE statment, the INSERTED table that I read in a "FOR INSERT" trigger has just 1 row or can have more rows?

Thanks.

many rows. Number of rows depended on how many rows get deleted / updated / inserted|||Image the query

INSERT INTO SomeTable
SELECT SomeCOlumn From ManyRowTable

That will bring up more than one row. bew also aware that the trigger is fired upon DML statement not per row, this means that a query like

INSERT INTO SomeTable
SELECT SomeColumn From SomeTable2 Where 1 = 0

also brings the trigger to fire.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Inserted Rows

Does anyone have any SP or script that can help me easily
determine the activity within my tables? Specifically, I
am looking for any kind of SPs, scripts, or tools that
can help me easily determine how heavily hit my various
tables are. For example is table A with 1,000,000 rows
in it not the heavily used while table B with 5,000 rows
in it is constantly being inserted to, deleted from, and
updated. I am trying to track down my heavy hitter
tables to do some P&T on them or move them to their own
files, etc.Z,
You can use SQL Profiler to track activity against a database. One data
column it can report is the ObjectName being referenced. Examine the
discussion in the BOL on SQL Profiler and SQL Trace.
Ideally, you would run the trace and spool its results to a file. Afterward
you can load the file into a table and do some queries to aggregate the
activity you are experiencing.
Running a trace will take some CPU from your server, but if you are
judicious in the events and data columns it should not be oppressive to the
server unless you are running at very high CPU levels already.
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Z" <anonymous@.discussions.microsoft.com> wrote in message
news:07a601c3af8f$d7d328f0$a001280a@.phx.gbl...
> Does anyone have any SP or script that can help me easily
> determine the activity within my tables? Specifically, I
> am looking for any kind of SPs, scripts, or tools that
> can help me easily determine how heavily hit my various
> tables are. For example is table A with 1,000,000 rows
> in it not the heavily used while table B with 5,000 rows
> in it is constantly being inserted to, deleted from, and
> updated. I am trying to track down my heavy hitter
> tables to do some P&T on them or move them to their own
> files, etc.
>

Inserted row deletes after trigger

I'm hoping someone has seen this before because I have no idea what could be causing it.

I have an SQL 2005 database with multiple tables and several triggers on the various tables all set to run after insert and update.

My program inserts a record into the "items" via a SP that returns the index of the newly added row. The program then inserts a row into another table that is related to items. When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint. Checking the items table shows the record that was just inserted in there is now deleted.

The items record is only deleted when I have my trigger on that table enabled. Here is the text of the trigger:

GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO

ALTERTRIGGER [dbo].[TestTrigger]
ON [dbo].[items]
AFTERINSERT

AS
BEGIN

SETNOCOUNTON;

INSERTINTO tblHistory(table_name, record_id, is_insert)
VALUES('items', 123, 1)

END

tblHistory's field types are (varchar(50), BigInt, bit).

As you can see there is nothing in the trigger to cause the items record to be deleted, so I have no idea what it could be? Anyone ever see this before?

Thanks in advance!

Hey,

I don't know that the row is deleted, but that the row doesn't get actually inserted for some reason. What do the two insertions look like? In SQL or ADO.NET code? Could it be that the first item doesn't get inserted, then returns a number that doesn't match an entry in that table, and that is why you get an error for the second insert?

|||

No, the first item is inserted and the returned value is exactly what it should be. When we test it without the trigger enabled and it all works, the new primary key value is the next value after the one that disapeared (i.e. if the record that was deleted was 5 the next one that works is 6).

|||

Are you using @.@.identity?

|||

If one of the follow on triggers fails, for whatever reason, the insert statement will be rolled back.

I suggest commenting out the triggers one by one (from last run to first run) until you figure out which one is the problem.

(Or learn to use the debugger in sql server.)

|||

David is correct in that the trigger code is considered part of the insert transaction.

If the trigger fails, then the entire "transaction" is rolled back, including the insert. If tblHistory has a foreign key constraint, and the trigger fails because of it, then you will get exactly what you are describing. The record is inserted partially (uncommitted), the trigger is fired, an error is encountered, then the insert is rolled back and the error from the trigger is sent to the client.

|||

It couldn't have been the trigger failing, b/c there are no constraints on the history table and while yo uare correct the error would have been returned as if it was coming from the insert statement I said above "When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint." The error was not about hte history table.

Motley you actually had the answer. What was happening is the stored procedure ran and inserted the row into items, the trigger ran on that and inserted the row into web updates, the stored procedure then returned the @.@.Identity, but since that returns the last identity of any insert to the database it was returning the identity of the history table, not the items table. When the second insert was run it was trying to insert the wrong identity and failed the foreign key restraint, resulting in the entire transaction to fail and rollback, giving the appearance the items record had been deleted.

Thanks for your help!

|||

Use scope_identity(), not identity! scope_identity was created to avoid just this problem!

sql

Inserted records missing in sql table yet tables primary key field has been incremented.

I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.

I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.

And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.

Hi Nick,

Your problem seems interesting. Would you please put some related code here. So that we can analyze what exactly going on there.

|||

The code below indicates when the automated email is send and after that is the markup of my page.

ProtectedSub DetailsView1_ItemInserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.DetailsViewInsertedEventArgs)

'I have code to send automated emails here.

EndIf

Catch exAs Exception

'iam not catching nor doing any thing here. (possibly i should have done some thing here)

Finally

Response.Redirect("AfterInserting.aspx")

EndTry

And the markup is below

<asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server">

<table>

<tr>

<tdstyle="width: 100px; height: 21px; text-align: left;"valign="top">

<asp:LabelID="Label8"runat="server"Width="126px"></asp:Label>

<asp:LabelID="Label20"runat="server"Width="128px"ForeColor="#0000FF"></asp:Label></td>

<tdstyle="width: 100px; height: 21px; text-align: left;"valign="top">

<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataKeyNames="Incident_id"

DataSourceID="SqlDataSource1"DefaultMode="Insert"Height="50px"Width="497px"Font-Size="Smaller"OnItemInserted="DetailsView1_ItemInserted"BackColor="LightGoldenrodYellow"BorderColor="Tan"BorderWidth="1px"CellPadding="2"ForeColor="Black"OnItemInserting="DetailsView1_ItemInserting">

<asp:TemplateFieldHeaderText="Incident_id"InsertVisible="False"SortExpression="Incident_id">

<asp:LabelID="Label1"runat="server"Text='<%# Eval("Incident_id") %>'></asp:Label>

</EditItemTemplate>

<asp:LabelID="Label20"runat="server"Text='<%# Bind("Incident_id") %>'ToolTip="This is the Incident Number"></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Person Raising Report"SortExpression="Incident_Reported_By">

<asp:TextBoxID="TextBox7"runat="server"Text='<%# Bind("Incident_Reported_By") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("Incident_Reported_By") %>'ToolTip="Type the name of the person raising the report here (Your Name)"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="TextBox2"

ErrorMessage='You have not provided your name ......You must enter your name in the Person raising report field in order to report this incident .'

SetFocusOnError="True"ValidationGroup="email"EnableTheming="False">*</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label7"runat="server"Text='<%# Bind("Incident_Reported_By") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Person Raising Report's Employee#"SortExpression="Emp_No">

<asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("Emp_No") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource20"

DataTextField="EmpNumber"DataValueField="EmpNumber"SelectedValue='<%# Bind("Emp_No") %>'

Width="156px"ToolTip="Select Your Employee Number here. If you have no number check other options at bottom of the list and select one that suits you">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource20"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [EmpNumber] FROM [EmpNumbers] ORDER BY [EmpNumber]"></asp:SqlDataSource>

<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="DropDownList1"

ErrorMessage="You must select your Employee Number. Other options are : Trainee, Contractor, Casual, Canteen staff and Security personnel. "

InitialValue=".."ValidationGroup="email">.</asp:RequiredFieldValidator>

<asp:TextBoxID="TextBox24"runat="server"Text='<%# Eval("Emp_No") %>'Visible="False"></asp:TextBox>

</InsertItemTemplate>

<asp:LabelID="Label2"runat="server"Text='<%# Bind("Emp_No") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Personnel Directly Involved"SortExpression="Personnel_Directly_Involved">

<asp:TextBoxID="TextBox10"runat="server"Text='<%# Bind("Personnel_Directly_Involved") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox4"runat="server"Text='<%# Bind("Personnel_Directly_Involved") %>'ToolTip="Type the name of the person directly involved in the Incident here"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator4"runat="server"ControlToValidate="TextBox4"

ErrorMessage="Error in Personnel directly Involved Field....This field can not left blank"SetFocusOnError="True"

ValidationGroup="email">*</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label10"runat="server"Text='<%# Bind("Personnel_Directly_Involved") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Witness 1">

<asp:TextBoxID="TextBox21"runat="server"ToolTip="Type the name of the witness here. You can not leave this field blank"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator8"runat="server"ControlToValidate="TextBox21"

EnableTheming="True"ErrorMessage="You must atleast specify one witness to the Incident. Please type the witness name."

SetFocusOnError="True"ValidationGroup="email">.</asp:RequiredFieldValidator>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Witness 2">

<asp:TextBoxID="TextBox22"runat="server"Text='<%# Bind("Witness_2") %>'ToolTip="Type the name of the second witness here if any. (Optional)"></asp:TextBox>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Witness 3">

<asp:TextBoxID="TextBox23"runat="server"Text='<%# Bind("witness_3") %>'ToolTip="Type the name of the third witness here if any (Optional)"></asp:TextBox>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Date Incident Occured "SortExpression="Incident_Date">

<asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("Incident_Date") %>'></asp:TextBox>

</EditItemTemplate>

<cc1:GMDatePickerID="GMDatePicker1"runat="server"AutoPosition="True"CalendarOffsetX="-200px"CalendarOffsetY="25px"CalendarTheme="Green"CalendarWidth="250px"CallbackEventReference=""Culture="English (United States)"DateString='<%# bind("Incident_Date") %>'EnableDropShadow="True"MaxDate="2020-12-31"MinDate=""NextMonthText=">"NoneButtonText="None"ShowNoneButton="False"ShowTodayButton="True"TextBoxWidth="150"ZIndex="1"InitialText="select date"ToolTip="Click the icon on the right to select the date on which the incident occurred">

<CalendarTodayDayStyleBackColor="#C0FFC0"/>

</cc1:GMDatePicker>

<asp:RequiredFieldValidatorID="RequiredFieldValidator6"runat="server"ControlToValidate="GMDatePicker1"

ErrorMessage="You must select the date on which this Incident Occurred. Click the icon next to the incident occurred date field to show a calendar and then click the desired date from the calendar."

SetFocusOnError="True"ValidationGroup="email"InitialValue="select date">.</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label1"runat="server"Text='<%# Bind("Incident_Date") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Date Incident Is Reported "SortExpression="Date_Reported">

<asp:TextBoxID="TextBox9"runat="server"></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="Textbox3"runat="server"Text='<%# Bind("Date_Reported") %>'ReadOnly="True"Font-Size="9pt"ForeColor="#6666FF"ToolTip="Do not type anything here. This field is automated to always display and save the current date"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator3"runat="server"ControlToValidate="TextBox3"

ErrorMessage="Error in Incident Reported Date....This field can not be left blank. "

SetFocusOnError="True"ValidationGroup="email">*</asp:RequiredFieldValidator>

<asp:CompareValidatorID="CompareValidator2"runat="server"ControlToValidate="TextBox3"

ErrorMessage='Error in Incident Reported Date Field. Re-enter date in month/day/year format '

Operator="DataTypeCheck"SetFocusOnError="True"Type="Date"ValidationGroup="email">*</asp:CompareValidator>

</InsertItemTemplate>

<asp:LabelID="Label9"runat="server"Text='<%# Bind("Date_Reported") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Time Incident Occurred"SortExpression="TimeCoomencedshift">

<asp:TextBoxID="TextBox16"runat="server"Text='<%# Bind("TimeCoomencedshift") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox9"runat="server"Text='<%# Bind("time_incident_occurred") %>'Width="67px"Height="21px"ToolTip="Type the time at which the incident occurred here in 24 hour format."></asp:TextBox>

<asp:ListBoxID="ListBox1"runat="server"Height="24px"Width="55px"ToolTip="Use the up and down arrows to specify AM or PM">

<asp:ListItem>PM</asp:ListItem>

<asp:ListItem>AM</asp:ListItem>

</asp:ListBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator7"runat="server"ControlToValidate="TextBox9"

ErrorMessage="You must enter the time at which the Incident occurred"SetFocusOnError="True"

ValidationGroup="email"Height="10px">.</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label16"runat="server"Text='<%# Bind("TimeCoomencedshift") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="ReminderDate"SortExpression="ReminderDate">

<asp:TextBoxID="TextBox8"runat="server"Text='<%# Bind("ReminderDate") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox6"runat="server"Text='<%# Bind("ReminderDate") %>'Font-Size="9pt"ForeColor="#6666FF"ReadOnly="True"ToolTip="Do not type any thing here. This field is automated to always add 3 days to the current date "></asp:TextBox>

</InsertItemTemplate>

<asp:LabelID="Label8"runat="server"Text='<%# Bind("ReminderDate") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Department">

<asp:DropDownListID="DropDownList5"runat="server"DataSourceID="DEPTDataSource1"

DataTextField="name"DataValueField="name"SelectedValue='<%# Bind("Dept") %>'

Width="155px"ToolTip="Click the arrow ponting down to select a department of the person involved from this list ">

</asp:DropDownList><asp:SqlDataSourceID="DEPTDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [name] FROM [Deptments] ORDER BY [name]"></asp:SqlDataSource>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Incident Location"SortExpression="Incident_Location">

<asp:TextBoxID="TextBox3"runat="server"Text='<%# Bind("Incident_Location") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList2"runat="server"DataSourceID="SqlDataSource3"

DataTextField="Area_Name"DataValueField="Area_Name"SelectedValue='<%# Bind("Incident_Location") %>'

Width="155px"ToolTip="Select the location where the incident occurred from this list">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource3"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [Area_Name] FROM [Incident_Areas] ORDER BY [Area_Name]"></asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label3"runat="server"Text='<%# Bind("Incident_Location") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Incident Category"SortExpression="Incident_Category">

<asp:TextBoxID="TextBox4"runat="server"Text='<%# Bind("Incident_Category") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList3"runat="server"DataSourceID="SqlDataSource5"

DataTextField="Category_Name"DataValueField="Category_Name"SelectedValue='<%# Bind("Incident_Category") %>'

Width="155px"ToolTip="Select the category of the incident from this list. Please take special note of injuries ">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource5"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [Category_Name] FROM [Incident_Category] ORDER BY [Category_Name]">

</asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label4"runat="server"Text='<%# Bind("Incident_Category") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Incident Severity"SortExpression="Incident_Severity">

<asp:TextBoxID="TextBox5"runat="server"Text='<%# Bind("Incident_Severity") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList4"runat="server"DataSourceID="SqlDataSource7"

DataTextField="Incident_Severity"DataValueField="Incident_Severity"SelectedValue='<%# Bind("Incident_Severity") %>'

Width="157px"ToolTip="Select the incident severity from this list">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource7"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [Incident_Severity] FROM [Incident_Severity] ORDER BY [Incident_Severity]">

</asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label5"runat="server"Text='<%# Bind("Incident_Severity") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Incident classification"SortExpression="Incident_classification"Visible="False">

<asp:TextBoxID="TextBox12"runat="server"Text='<%# Bind("Incident_classification") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList7"runat="server"DataSourceID="SqlDataSource16"

DataTextField="classification"DataValueField="classification"SelectedValue='<%# Bind("Incident_classification") %>'

Width="157px">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource16"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [classification] FROM [Incident_Classification]"></asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label12"runat="server"Text='<%# Bind("Incident_classification") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText=" Incident timing"SortExpression="TimingOfIncident"Visible="False">

<asp:TextBoxID="TextBox19"runat="server"Text='<%# Bind("TimingOfIncident") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList8"runat="server"DataSourceID="SqlDataSource26"

DataTextField="timing"DataValueField="timing"SelectedValue='<%# Bind("TimingOfIncident") %>'

Width="155px">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource26"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [timing] FROM [roster_timing_OfIncident]"></asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label19"runat="server"Text='<%# Bind("TimingOfIncident") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Shift Details"SortExpression="ShiftDetails"Visible="False">

<asp:TextBoxID="TextBox18"runat="server"Text='<%# Bind("ShiftDetails") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList9"runat="server"DataSourceID="SqlDataSource27"

DataTextField="shiftdetails"DataValueField="shiftdetails"SelectedValue='<%# Bind("ShiftDetails") %>'

Width="158px">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource27"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [shiftdetails] FROM [ShiftDetails]"></asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label18"runat="server"Text='<%# Bind("ShiftDetails") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Equipment Involved"SortExpression="EquipmentInvolved">

<asp:TextBoxID="TextBox13"runat="server"Text='<%# Bind("EquipmentInvolved") %>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList6"runat="server"DataSourceID="SqlDataSource28"

DataTextField="Cause"DataValueField="Cause"SelectedValue='<%# Bind("EqiupmentInvolved") %>'

Width="156px"ToolTip="Select the equipment involved in incident. If the equipment involved does not exist in the list, please notify safety to have the equipment added to the list.">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource28"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [Cause] FROM [WhatCausedInjury] ORDER BY [Cause]"></asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label13"runat="server"Text='<%# Bind("EquipmentInvolved") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="N0. of days into Roster Cycle"SortExpression="Time Incident Occurred"Visible="False">

<asp:TextBoxID="TextBox17"runat="server"Text='<%# Bind("NumberOfDaysintoRosterCycle") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox10"runat="server"Text='<%# Bind("NumberOfDaysintoRosterCycle") %>'></asp:TextBox>

</InsertItemTemplate>

<asp:LabelID="Label17"runat="server"Text='<%# Bind("NumberOfDaysintoRosterCycle") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Hours into shift"SortExpression="Hoursintoshift"Visible="False">

<asp:TextBoxID="TextBox15"runat="server"Text='<%# Bind("Hoursintoshift") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox8"runat="server"Text='<%# Bind("Hoursintoshift") %>'></asp:TextBox>

</InsertItemTemplate>

<asp:LabelID="Label15"runat="server"Text='<%# Bind("Hoursintoshift") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Time To Finish shift"SortExpression="TimeToFinishshift"Visible="False">

<asp:TextBoxID="TextBox14"runat="server"Text='<%# Bind("TimeToFinishshift") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox7"runat="server"Text='<%# Bind("TimeToFinishshift") %>'></asp:TextBox>

</InsertItemTemplate>

<asp:LabelID="Label14"runat="server"Text='<%# Bind("TimeToFinishshift") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Incident Brief Description"SortExpression="Incident_Description">

<asp:TextBoxID="TextBox11"runat="server"Text='<%# Bind("Incident_Description") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox5"runat="server"Height="45px"Text='<%# Bind("Incident_Description") %>'

TextMode="MultiLine"Width="199px"ToolTip="Briefly describe the incident here. You can type upto a maximum of 4000 characters"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator5"runat="server"ControlToValidate="TextBox5"

ErrorMessage="Error in Incident Description Field....You must briefly describe the nature of the Incident"SetFocusOnError="True"

ValidationGroup="email">*</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label11"runat="server"Text='<%# Bind("Incident_Description") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Immediate Action"SortExpression="Immediate_Action">

<asp:TextBoxID="TextBox6"runat="server"Text='<%# Bind("Immediate_Action") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBoxID="TextBox20"runat="server"Text='<%# Bind("Immediate_Action") %>'

TextMode="MultiLine"Height="41px"Width="201px"ToolTip="Type the immediate action taken when the incident occurred here"></asp:TextBox>

<asp:RequiredFieldValidatorID="RequiredFieldValidator9"runat="server"ControlToValidate="TextBox20"

ErrorMessage="No Immediate Action Entered: Please first enter the Immediate Action taken when the Incident Occurred"

SetFocusOnError="True"ValidationGroup="email">.</asp:RequiredFieldValidator>

</InsertItemTemplate>

<asp:LabelID="Label6"runat="server"Text='<%# Bind("Immediate_Action") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Foward To Your Head Of Department">

<asp:DropDownListID="DropDownList10"runat="server"DataSourceID="SqlDataSource50"

DataTextField="Names"DataValueField="Names"SelectedValue='<%# Bind("Foward_to") %>'

Width="154px"ToolTip="Select the head of department you want to foward the incident to from here">

</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource50"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [Names] FROM [H.O.D's] ORDER BY [Names]"></asp:SqlDataSource>

<asp:RequiredFieldValidatorID="RequiredFieldValidator10"runat="server"ControlToValidate="DropDownList10"

ErrorMessage="You have not selected the Head Of Department. Please select your head of department and then report agian."SetFocusOnError="True"ValidationGroup="email">.</asp:RequiredFieldValidator>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldShowHeader="False">

<asp:ButtonID="Button1"runat="server"CausesValidation="True"CommandName="Insert"

Text="Report Incident/Hazard"ValidationGroup="email"/>

<asp:ButtonID="Button2"runat="server"PostBackUrl="~/StartPage.aspx"Text="<< Exit"/>

</InsertItemTemplate>

<ItemStyleHorizontalAlign="Center"/>

<asp:ButtonID="Button1"runat="server"CausesValidation="False"CommandName="New"

Text="New"/>

</ItemTemplate>

</asp:TemplateField>

</Fields>

<FieldHeaderStyleHorizontalAlign="Right"/>

<InsertRowStyleHorizontalAlign="Left"/>

<FooterStyleBackColor="Tan"/>

<EditRowStyleBackColor="DarkSlateBlue"ForeColor="GhostWhite"/>

<PagerStyleBackColor="PaleGoldenrod"ForeColor="DarkSlateBlue"HorizontalAlign="Center"/>

<HeaderStyleBackColor="Tan"Font-Bold="True"/>

<AlternatingRowStyleBackColor="PaleGoldenrod"/>

</asp:DetailsView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"DeleteCommand="DELETE FROM [Report_Incident] WHERE [Incident_id] = @.original_Incident_id"

InsertCommand="INSERT INTO Report_Incident(Incident_Reported_By, Incident_Date, Date_Reported, ReminderDate, Personnel_Directly_Involved, Incident_Location, Incident_Category, Incident_Severity, Immediate_Action, Incident_Description, Incident_Assigned_To, EqiupmentInvolved, Emp_No, Foward_to, Dept, witness_1, witness_2, witness_3, time_incident_occurred) VALUES (@.Incident_Reported_By,@.Incident_Date,@.Date_Reported,@.ReminderDate, @.Personnel_Directly_Involved,@.Incident_Location,@.Incident_Category,@.Incident_Severity, @.Immediate_Action,@.Incident_Description,@.Incident_Assigned_To,@.EqiupmentInvolved, @.Emp_No,@.Foward_to,@.Dept,@.witness_1,@.witness_2,@.witness_3,@.time_incident_occurred) "

OldValuesParameterFormatString="original_{0}"SelectCommand="SELECT Incident_id, Incident_Reported_By, Incident_Date, Date_Reported, Personnel_Directly_Involved, Incident_Location, Incident_Category, Incident_Severity, Immediate_Action, Incident_Description, Incident_Assigned_To, Incident_classification, EqiupmentInvolved,Emp_No, ReminderDate,Foward_to, Dept, witness_1, witness_2, witness_3, time_incident_occurred FROM Report_Incident"EnableCaching="True">

<asp:ParameterName="original_Incident_id"Type="Int32"/>

</DeleteParameters>

<asp:ParameterName="Incident_Reported_By"Type="String"/>

<asp:ParameterName="Emp_No"/>

<asp:ParameterName="Incident_Date"Type="DateTime"/>

<asp:ParameterName="Date_Reported"Type="DateTime"/>

<asp:ParameterName="ReminderDate"/>

<asp:ParameterName="Personnel_Directly_Involved"Type="String"/>

<asp:ParameterName="Incident_Location"Type="String"/>

<asp:ParameterName="Incident_Category"Type="String"/>

<asp:ParameterName="Incident_Severity"Type="String"/>

<asp:ParameterName="Immediate_Action"Type="String"/>

<asp:ParameterName="Incident_Description"Type="String"/>

<asp:ParameterName="Incident_Assigned_To"Type="String"/>

<asp:ParameterName="EqiupmentInvolved"/>

<asp:ParameterName="Foward_to"/>

<asp:ParameterName="Dept"/>

<asp:ParameterName="witness_1"/>

<asp:ParameterName="witness_2"/>

<asp:ParameterName="witness_3"/>

<asp:ParameterName="time_incident_occurred"/>

</InsertParameters>

</asp:SqlDataSource>

<asp:ValidationSummaryID="ValidationSummary1"runat="server"ShowMessageBox="True"

ShowSummary="False"ValidationGroup="email"Font-Strikeout="True"Height="1px"Width="179px"/>

</td>

<tdstyle="height: 21px; width: 3px;"valign="top">

<br/>

<asp:ButtonID="Button3"runat="server"Text="Get Help ?"Width="101px"Font-Bold="False"OnClientClick='window.open("Help/onreporting.aspx")'/><br/>

<br/>

<asp:ButtonID="Button1"runat="server"PostBackUrl="~/StartPage.aspx"Text="<< Back"

Width="97px"/><br/>

</td>

</tr>

</table>

</asp:Content>

|||

Identity column data is not guaranteed to be consecutive.

If the inserts were done in the context of a transaction, and the transaction is rolled back, then that is exactly what you will see. They were there at one point, but since the transaction was rolled back they are no longer there, and the identity seed is incremented.

|||

Motley:
Identity column data is not guaranteed to be consecutive.
If the inserts were done in the context of a transaction, and the transaction is rolled back, then that is exactly what you will see. They were there at one point, but since the transaction was rolled back they are no longer there, and the identity seed is incremented.

Thanks.|||

I got the problem. There was a field in the table with varchar(7) datatype and if some one tried to insert a record and typed more than 7 characters in the textbox that insertes into this table column, the identity field would be incremented but nothing would actually be saved in the database. In my own opinion, i would say microsoft should have designed it in a way that if nothing is inserted due to such a problem, then let nothing be done on database as well. Incrementing the identity field even when no record has been inserted makes it harder to troubleshoot.

inserted in dynamic query

Hi,
Is it possible to use inserted or deleted tables in a dynamic query in a
trigger?
Thanks.No, you can only reference the inserted and deleted tables within the contex
t
of the trigger.
What exactly are you trying to do? Perhaps there is a workaround.
"helpful sql" wrote:

> Hi,
> Is it possible to use inserted or deleted tables in a dynamic query in
a
> trigger?
> Thanks.
>
>

Wednesday, March 28, 2012

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

Inserted and Deleted tables

Hi:

Can any of the experts please confirm the fact that Inserted and deleted tables in SQL Server 2005 are stored in tempdb?. If so, how can I query them in tempdb ( A code snippet would be useful).

Thanks

Hi Ankith,

Inserted and deleted table are created in Trigger execution time and can't possible query them, only in trigger execution time.

Regards,

|||

Thanks for the reply. I still would like to know if they are stored in tempdb though in SQL Server 2005 Vs getting stored in memory in SQL Server 2000.

Any pointers?

Thanks

|||inserted/deleted are memory-resident tables. You cannot access them outside of the execution context.|||Thanks OJ. So what I might have read is probably talking of row versioning that uses tempdb. Thanks again for the clarification.|||You shouldn't be allowed to read internal worktable even if it resides in tempdb. If you could, this would be a major security hole. ;-)|||

Hi OJ:

Right I agree with you. However what does the following paragraph mean?

URL is :http://www.sqlmag.com/Article/ArticleID/93465/sql_server_93465.html

The first impression i get when i read the paragraph is the tables are stored in tempdb in 2005. This is where I am confused. Can you please elaborate further?.

Thanks

Triggers have long been a part of SQL Server and were the only feature prior to SQL Server 2005 that provided any type of historical (or versioned) data. Triggers can access two pseudo-tables called deleted and inserted. Inside the trigger, you can access these two tables as if they were real tables, but accessing them while not in a trigger results in an unknown object error. If the trigger is a DELETE trigger, the deleted table contains copies of all the rows deleted by the operation that caused the trigger to fire. If the trigger is an INSERT trigger, the inserted table contains copies of all the rows inserted by the operation that caused the trigger to fire. And if the trigger is an UPDATE trigger, the deleted table contains copies of the old versions of the rows, and the inserted table contains all the new versions. Before SQL Server 2005, SQL Server would determine which rows were included in these pseudo-tables by scanning the transaction log for all the log records belonging to the current transaction. Any log records containing data inserted in or deleted from the table to which the trigger was tied were included in the inserted or deleted tables.

In SQL Server 2005, these pseudo-tables are created by using RLV technology. When data-modification operations are performed on a table that has a relevant trigger defined, SQL Server creates versions of the old and new data in the version store in tempdb.This occurs whether or not either of the snapshot-based isolation levels has been enabled.When a SQL Server 2005 trigger accesses the deleted table, it retrieves the data from the version store.When a trigger needs to determine which rows in the table are new rows and accesses the inserted table, SQL Server again gets the inserted table rows from the version store.

|||The article describes how sqlserver physically create/maintain the inserted/deleted table. For a very long time now, tempdb has always been used as the workspace for sqlserver. It uses tempdb to hold the paged data that can't fit in the allowable memory - @.table variable is the best example of this. So, in the new sql2k5, instead of scanning the log to materialize the inserted/deleted table, it goes ahead and store a copy of updated data in tempdb. This will make the materialization faster because it does not have to scan the entire log.

Long story short, inserted and deleted table are very special table. Regardless of how they're materialized, they can only be accessed within the execution (trigger) context.|||

I am curious why you would want to do this in the first place. Are you simply trying to access the data before and after the record is created. In a trigger you can access the date using inserted and deleted as a table name.

select * from inserted

Also, it is interesting to point out that an update consists of both an insert and a delete.

|||Thanks OJ for your explanation.sql

inserted and deleted tables

ok i know the rows being affected are put in these temporary tables, but
if i do an insert with 5 rows does inserted have 5 rows in it?
if htats the case how do you check field values for every row
I was using
if (select newfield from #inserted) = this
begin
update #inserted set newfield = that
end
but that isnt going to work if inserted contains all 5 rows, i thought
inserted only had the current row and it passed through the instead of
trigger 5 times once for each row. if thats not the case how do you do
something like
for each newfield in #inserted do
if newfield is this
set it to this.
for example
say i have an insert with three fields
category, categoryid, name
and the 3 rows in my insert are
('Standard', 1, 'Toys')
('NonStandard, null, 'Games')
('Misc', null, 'Puzzles')
and in my instead of trigger i want to fill the nulls with the proper
number so in my instead of trigger i say
if (field2 is null)
begin
set field2 = (select rightnumber from mastertable where name = field2)
end
but it has to do it for each rowChris M wrote:
> ok i know the rows being affected are put in these temporary tables,
> but if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you
> do something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
Yes. The inserted and deleted logical tables contain all affected rows.
No. You cannot modify data in the inserted and deleted tables, so I'm
not quite sure how your code was even executing. The tables do not have
a '#' prefix. The are plainly 'inserted' and 'deleted'.
How would you get the "right number" from mastertable is the second
column is NULL. What are you joining on?
Personally, I would just throw up a RAISERROR. I don't really understand
your test scenario. If you could join up with mastertable, then it seems
you should be using a FK to that table rather than repeating data
values.
Could you provide the DDL for the tables in question.
David Gugick
Imceda Software
www.imceda.com|||It will do it for each row in inserted, just write the expression on the
right of the set newfield =...
so that it will be a different value for each row of inserted...
update Table set newfield =
Case newfield
When 'this' Then 'That'
When 'TheOther' Then 'OtherThat'
End
But what is #Inserted? a Temporary Table?
What are you trying to update in this trigger?
"Chris M" wrote:

> ok i know the rows being affected are put in these temporary tables, but
> if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you do
> something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Stop thinking procedure and start thinking sets. If there are 3 rows in
the inserted set and 2 have NULL in some column (as your example shows)
you can do an UPDATE like this:
UPDATE original_table
SET column_name = (select rightnumber
from mastertable m inner join inserted i
on m.<join cols> = i.<join cols> )
WHERE EXISTS (SELECT * FROM inserted
WHERE column_name IS NULL
AND inserted.ID = original_table.ID)
The "WHERE column_name IS NULL" in the UPDATE's WHERE clause subquery
will identify the rows in original_table that have the 'column_name' set
to the "rightnumber."
The <join cols> have to be a column, or columns, that uniquely identify
the rows in inserted that relate to rows in mastertable, so the
"rightnumber" can be retrieved. I would have to see the design of
mastertable and original_table to determine which columns those would
be. You could even do w/o the inserted set and just use something in
the mastertable that identifies which row in mastertable has the correct
data that is to be placed in the original_table. IOW, if you had a
Default value in mastertable that always goes in that column - data in
mastertable looks like this:
column_ rightnumber
-- --
Price 25
The SET subquery would look like this:
SET Price = (select rightnumber
from mastertable
where column_ = 'Price')
NB: By now you should realize that you can create a DEFAULT on the
column(s) in original_table instead of using a trigger like the above.
E.g.: CREATE TABLE T (col_1 int, col_a char(2) default ('zz'))
insert into t (col_1) values (2)
select * from t
col_1 col_a
-- --
2 zz
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmguqYechKqOuFEgEQJJSgCg8iAqLWvq7TwF
9BLQlhBbcY/uxx0AnRRi
DXmdayzhLIxU5WBk4wSL4x4R
=mDFy
--END PGP SIGNATURE--
Chris M wrote:
> ok i know the rows being affected are put in these temporary tables, but
> if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you do
> something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
>|||MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Stop thinking procedure and start thinking sets. If there are 3 rows in
> the inserted set and 2 have NULL in some column (as your example shows)
> you can do an UPDATE like this:
> UPDATE original_table
> SET column_name = (select rightnumber
> from mastertable m inner join inserted i
> on m.<join cols> = i.<join cols> )
> WHERE EXISTS (SELECT * FROM inserted
> WHERE column_name IS NULL
> AND inserted.ID = original_table.ID)
> The "WHERE column_name IS NULL" in the UPDATE's WHERE clause subquery
> will identify the rows in original_table that have the 'column_name' set
> to the "rightnumber."
> The <join cols> have to be a column, or columns, that uniquely identify
> the rows in inserted that relate to rows in mastertable, so the
> "rightnumber" can be retrieved. I would have to see the design of
> mastertable and original_table to determine which columns those would
> be. You could even do w/o the inserted set and just use something in
> the mastertable that identifies which row in mastertable has the correct
> data that is to be placed in the original_table. IOW, if you had a
> Default value in mastertable that always goes in that column - data in
> mastertable looks like this:
> column_ rightnumber
> -- --
> Price 25
> The SET subquery would look like this:
> SET Price = (select rightnumber
> from mastertable
> where column_ = 'Price')
> NB: By now you should realize that you can create a DEFAULT on the
> column(s) in original_table instead of using a trigger like the above.
> E.g.: CREATE TABLE T (col_1 int, col_a char(2) default ('zz'))
> insert into t (col_1) values (2)
> select * from t
> col_1 col_a
> -- --
> 2 zz
I can do that sometimes but if i want to set something like an id number
based on a column in another table that i cannot join on i can't do it
with a set
unless there is a command like
select * into #inserted from inserted
update #inserted
set keyfield = getNextValueFromTable()
insert into myTable select * from #inserted
but I cannot figure out how to get the getNextValueFromTable()
procedure since i cannot call stored procedures that way and UDF's
cannot access my table values
I am only doing this the way i'm doing it to maintain compatibility with
a program. If i was to design this myself I'd be doing it with
constraints, foreign keys, identities, etc|||Chris M wrote:
> MGFoster wrote:
>
< SNIP >
> I can do that sometimes[,] but if i want to set something like an id numbe
r
> based on a column in another table that i cannot join on i can't do it
> with a set
>
<SNIP >
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
How can you know which "id number[...]in another table" to use if you
cannot join on it? That implies that there is "some other" way of
determining the relationship between one table and another; and, that
that relationship is defined outside the database. This goes against
RDB design principles.
If the "id number [is] based on a column in another table" that means
there is a relationship between the 2 tables. If there is a
relationship between the 2 tables you can join them.
So, what's going on there? ;-)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmgyZYechKqOuFEgEQJd5QCfaDO4xXjYNt2S
oYbvwG9acyT4ncAAoPyO
BL03rAJrHegoe1ktC8L/pRBI
=8GJu
--END PGP SIGNATURE--|||What do you mean..
<snip> ...that i cannot join on ...</snip>
Why Not?
If the objective here is to insert some records into MyTable, then just do
that in the trigger
Insert MyTable
Select <Stuff>
From inserted
The <Stuff> above needs t oeb written as a set-based expression, (Set of
expressions), such that the values will be appropriate... But there's no way
for us to guess what that is until you tell us whjat you are trying to do
with getNextValueFromTable()...
again, if all you are tyrying to do is set the value based on the value in
the inserted table, then, as an example...
Insert MyTable
Select <OtherColumns>,
Case newField
When <ValueA> Then <outValueA>
When <ValueB> Then <outValueB>
When <ValueC> Then <outValueC>
When <ValueD> Then <outValueD>
Else <OutVAlueDefault> End
From inserted|||MGFoster wrote:
> Chris M wrote:
>
> < SNIP >
>
> <SNIP >
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> How can you know which "id number[...]in another table" to use if you
> cannot join on it? That implies that there is "some other" way of
> determining the relationship between one table and another; and, that
> that relationship is defined outside the database. This goes against
> RDB design principles.
> If the "id number [is] based on a column in another table" that means
> there is a relationship between the 2 tables. If there is a
> relationship between the 2 tables you can join them.
> So, what's going on there? ;-)
A table called generators
create table generators (
generator_name varchar(50),
generator_lastid integer
)
in my table say myTable if i want to get the next ID from generators i
have to
select generator_lastid from generators where generator_name =
'gen_id_mytable)
so i do not know how i can join on that, and I'm sure this does violate
some rule, but its meant to simulate the sequence/generator object of
oracle/interbase/firebird|||> if i do an insert with 5 rows does inserted have 5 rows in it?
Yes if the insert/update was done as a single statement.

> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
What is "this"? Is the idea to override the values being inserted/updated in
the
trigger? If that is the case, then you need an InsteadOf trigger not an Afte
r
trigger.

> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of tri
gger
> 5 times once for each row. if thats not the case how do you do something like[/co
lor]
No. That is not the case. Each *statement* fires the trigger once (ignoring
cascades for the moment). Thus, imagine the statement:
Insert Table(F1...Fn)
Select F1...FN
From Table
That might insert 1000 records with that once statement. That statement will
fire the trigger once and populate the "inserted" table with 1000 records. I
f it
is an update, then you will get 1000 records in the "inserted" table and 100
0
records in the "deleted" table.
> for each newfield in #inserted do
> if newfield is this
> set it to this.
Can't do that with an After trigger. You need to do that with an InsteadOf
trigger.

> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper numb
er
> so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
Create Table Stuff
(
Category VarChar(50) Not Null
, SomeNumber Int Null
, Description VarChar(50) Not Null
)
Create Table SomeOtherTable
(
SingleValue Int
)
Insert SomeOtherTable(SingleValue) Values(99)
Create Trigger trigStuff On dbo.Stuff
Instead Of Insert
As
Begin
Insert Stuff(Category, SomeNumber, Description)
Select Category
, (Select SingleValue From SomeOtherTable)
, Description
From inserted As I
End
Insert Stuff(Category, SomeNumber, Description) Values('Standard', 1, 'Toys'
)
Insert Stuff(Category, SomeNumber, Description) Values('NonStandard', Null,
'Games')
Insert Stuff(Category, SomeNumber, Description) Values('Misc', Null, 'Puzzle
s')
Select * From Stuff
HTH
Thomas|||Chris M wrote:
> MGFoster wrote:
>
>
> A table called generators
> create table generators (
> generator_name varchar(50),
> generator_lastid integer
> )
>
> in my table say myTable if i want to get the next ID from generators i
> have to
> select generator_lastid from generators where generator_name =
> 'gen_id_mytable)
> so i do not know how i can join on that, and I'm sure this does violate
> some rule, but its meant to simulate the sequence/generator object of
> oracle/interbase/firebird
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Ah... In that case you can just insert that value "generator_lastid"
into the NULL columns in the original table like this (this is in the
trigger):
UPDATE original_table
SET null_column = (SELECT generator_nextid FROM generators
WHERE generator_name = null_column_name)
WHERE id IN (SELECT id FROM inserted WHERE null_column IS NULL)
Substitute correct table/column names where appropriate.
Each row would get the same number. This won't work if you want
incrementing numbers in each row that had the NULL valued column. There
is no way to increment the nextid for the next call. A function can't
be used 'cuz ya can't run an UPDATE inside a function (to increment the
nextid). A procedure can't be used 'cuz ya can't use a procedure as a
recordsource, like ya can w/ a function.
Looks like (ugh!) a WHILE loop would have to be used to cycle thru all
the inserted rows that had NULL values in the column.
@.count = (select count(*) from inserted where column_name is null)
while @.count > 0 begin
-- do the update & generate new nextid
@.count = @.count - 1
end
Quite a problem.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmhsb4echKqOuFEgEQL6JwCguTf9eD2kFh7F
fZDJAtnUhIKvKWwAniqR
xD1fe46JZ8B2jXX11NQRQmtJ
=xD8y
--END PGP SIGNATURE--

inserted and deleted table

for after trigger the records stored in followig table

inserted and deleted table.

but i want to know where this tables physically stored ...i mean in which database master or some other database?

and 2nd thing tigger fired for each row or for only insert,delete,update statement?

thanx

Where stored?

Obviously in temp tables at tempdb.

Is it executed for each row?

No. If single query affects more than one row, the inserted or deleted table may have more than one row. When you write a trigger you have to keep consider this & you have to handle your trigger query which will support both single row & multiple rows.

|||

The table is not physically stored it is virtual only, it only exists within the trigger context. Triggers are fired per statement not per row, you will need to handle mutlirow existance in your trigger and in addition the occurence of no affected rows,a s the trigger is also fired if no rows is affected like

Code Snippet

UPDATE SomeTable SET SomeColumn = 'SomeValue' WHERE 1=2

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Is it executed for each row?

mani

i mean trigger fired for each row or only for update statement..here i m not talking about inserted and deleted table

|||

On high level it is called virtual, but SQL Server always use the TempDB as workspace to store the data, so the data may be presented or stored in tempdb but you can't access these data from outside of your trigger scope & these are absolutely read-only.

There is interesting thread on same question on DB Engine forum

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=908238&SiteID=1

|||The answer is for one statement not for each rows.

|||thanx mani and jens.

inserted / deleted tables for triggers

Hi i was hoping someone could help me. If i have the following trigger
defined:
CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
UPDATE mytable SET
field1 = ISNULL(inserted.field1, 0),
field2 = ISNULL(inserted.field2, 0),
field3 = ISNULL(inserted.field3, 0)
FROM inserted
WHERE mytable.userid = inserted.userid
Iperform the following:
UPDATE mytableview
SET field1 = 1
WHERE userid = 1234
lets take for example the row pertaining to userid = 1234 within mytable to
be:
userid field1 field2 field3
1234 0 1 2
What is the state of the inserted table when the trigger is fired? Does the
inserted table do the following:
1) copy into itself the row from mytable pertaining to userid = 1234
2) modify this copied row to reflect field1 = 1
so inserted looks like this:
userid field1 field2 field3
1234 1 1 2
OR
1) creates a row within itself with field1 = 1, and all the other fields set
to NULL?
so inserted looks like this:
userid field1 field2 field3
NULL 1 NULL NULL
Ay help most appreciated. I think i am slightly

with the state of
the inserted/deleted tables when triggers are invovled.
Cheers,
peterAn UPDATE with a trigger is performed as a DELETE followed by an INSERT. So
the DELETED table will contain the *before* data records and the INSERTED
table will contain the *after* data records.
HTH
Jerry
"PWalker" <pwalker@.nospam.com> wrote in message
news:OlU7ioz0FHA.2428@.tk2msftngp13.phx.gbl...
> Hi i was hoping someone could help me. If i have the following trigger
> defined:
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0)
> FROM inserted
> WHERE mytable.userid = inserted.userid
>
> Iperform the following:
>
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
>
> lets take for example the row pertaining to userid = 1234 within mytable
> to be:
> userid field1 field2 field3
> 1234 0 1 2
> --
> What is the state of the inserted table when the trigger is fired? Does
> the inserted table do the following:
> 1) copy into itself the row from mytable pertaining to userid = 1234
> 2) modify this copied row to reflect field1 = 1
> so inserted looks like this:
> userid field1 field2 field3
> 1234 1 1 2
> OR
> 1) creates a row within itself with field1 = 1, and all the other fields
> set to NULL?
> so inserted looks like this:
> userid field1 field2 field3
> NULL 1 NULL NULL
>
> Ay help most appreciated. I think i am slightly

with the state of
> the inserted/deleted tables when triggers are invovled.
> Cheers,
> peter
>|||thanks, so an update removes the relevant row(s) from the trigger table and
sticks them into the deleted table; then inserts the new modified row(s)
into both the trigger table and the inserted table.
thanks for the clarification.
cheers, peter

> An UPDATE with a trigger is performed as a DELETE followed by an INSERT.
> So the DELETED table will contain the *before* data records and the
> INSERTED table will contain the *after* data records.
> HTH
> Jerry
> "PWalker" <pwalker@.nospam.com> wrote in message
> news:OlU7ioz0FHA.2428@.tk2msftngp13.phx.gbl...
>

Inserted & Deleted Tables!

Suppose a trigger gets fired when the following UPDATE query gets
executed:
---
UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
---
Now the Inserted table will have the new record '12345' in the Pwd
column & the Deleted table will have the old record '11111' in the Pwd
column. So will the record 'jack' exist in the UserID column of both
the Inserted table & the Deleted table that the trigger will be making
use of?
Thanks,
ArpanHi
Yes, the whole row, as it was before and after are in the respective tables,
not just the column that changed.
If you update the primary key of a table, then comparing the Inserted and
Deleted becomes very difficult.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123800474.961292.259990@.g49g2000cwa.googlegroups.com...
> Suppose a trigger gets fired when the following UPDATE query gets
> executed:
> ---
> UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
> ---
> Now the Inserted table will have the new record '12345' in the Pwd
> column & the Deleted table will have the old record '11111' in the Pwd
> column. So will the record 'jack' exist in the UserID column of both
> the Inserted table & the Deleted table that the trigger will be making
> use of?
> Thanks,
> Arpan
>|||On 11 Aug 2005 15:47:55 -0700, Arpan wrote:

>Suppose a trigger gets fired when the following UPDATE query gets
>executed:
>---
>UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
>---
>Now the Inserted table will have the new record '12345' in the Pwd
>column & the Deleted table will have the old record '11111' in the Pwd
>column. So will the record 'jack' exist in the UserID column of both
>the Inserted table & the Deleted table that the trigger will be making
>use of?
Hi Arpan,
Almost.
The exact correct way to put this is:
- The deleted table will hold 0, 1, or many rows that all have UserID
'jack' and Pwd '11111'. Impossible to tell what the other columns will
be.
- The inserted table will hold 0, 1, or many rows (but the same number
as the deleted table) that all have UserID 'jack' and Pwd '12345'; the
other columns will be the same as in the corresponding rows in the
deleted table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Insert/Updated SP from multiple tables

How do I insert unrelated statistical data from three tables into another
table that already exist with data using an insert or update stored procedure?
OR...
How do I write an insert/Update stored procedure that has multiple select
and a where something = something statements?

This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this...

CREATE PROCEDURE AddDrawStats
AS
INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers)

SELECT
WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing),

TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ),

RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID )

FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID

WHERE D.DrawingID = DS.CurrentDrawing
GO"INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing"
and
"WHERE W.DrawingID = DS.CurrentDrawing"
are redundant. They both accomplish the same thing; associating records in the two tables. Among SQL Server DBAs, the INNER JOIN syntax is preferred, so drop the links in your WHERE clauses.

As to your other issues, I'm sorry but the SQL statement you posted is too disjointed to figure out what your intentions are. You will need to describe your tables and your objective if you want more help, but embedding subqueries into the SELECT clause is rarely a good idea. I highly suspect that what your SQL statement describes is not really what you are trying to do.|||yes my attention is that I have Four related/non-related table and I would like to get some statistical data such as the count of how many student are in the student table, how many student are playing the current drawing, how many tickets are in the current drawing, and how many students won the current drawing. Setting up a common inner join would not allow me to get the exact data I need. Plus, I need to insert this data in the drawing table record that already have data but these fields are null. My stored procedure works somewhat, but it creates a new record; I want the stored procedure to insert this information in the record that already exist where drawing = the CurrentDrawing. So should I do an insert/update stored procedure, and how? All I need to see is an example of a stored procedure that insert or update data in some table where some criteria are met which comes from multiple select statements using different table within those select statement.|||This is what your SQL Statement describes, but again, I doubt that it is exactly what you want:

CREATE PROCEDURE AddDrawStats
AS

Declare @.Players int
Declare @.RegisterPlayers int
Declare @.TicketsPlayed int

set @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
set @.RegisterPlayers = (SELECT Count(*) FROM Student)
set @.TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

Update Drawing
set WinnersWon = WinnersSubquery.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
from Drawing
inner join
(SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
on Drawing.DrawingID = WinnersSubquery.CurrentDrawing

INSERT INTO Drawing (DrawingID, WinnersWon, TicketsPlayed, Players, RegisterPlayers)
select WinnersSubquery.CurrentDrawing,
WinnersSubquery.WinnersWon,
@.TicketsPlayed,
@.Players,
@.RegisterPlayers
from (SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
left outer join Drawing on WinnersSubquery.CurrentDrawing = Drawing.DrawingID
where Drawing.DrawingID is null|||Thanks for all the help! This works but I have two questions?
Could I have written this Stored procedure better? and...
Why this statement yeilds the wrong results? *i.e.*each player can have up to five tickets in the ticket table, but this statement count each ticket as a player.How do I write this statement to get only unigue AccountID within the ticket table?
**SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)

CREATE PROCEDURE AddDrawStats2
AS

DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT

SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )

UPDATE Drawing
SET WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers

WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO|||SET @.Players = (SELECT Count(Distinct T.AccountID) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)|||Thanks so much for all the help. This stored procedure does the job, but you see any drawbacks?

CREATE PROCEDURE AddDrawStats
AS
DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT

SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players =(SELECT Count(Distinct T.AccountID) FROM Ticket T WHERE T.Active = 1)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )

UPDATE Drawing
SET
WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO

insert/select

I am trying to find an easier way to handle my insert/select statement. If
I have the following tables and query - Is there a way to do this in one
statement?
create table Master(
MasterKey int,
)
Create table SubTable1(
MasterKey int,
PK int,
Data varChar(1000)
)
Create table SubTable2(
MasterKey int,
PK int,
Data varChar(1000
)
Master Table
MasterKey Priority
1 0
2 0
3 1
4 0
5 0
6 0
SubTable1
Empty
SubTable2
MasterKey PK
1 1
1 1
1 2
2 3
2 3
3 4
3 4
3 4
4 4
4 4
4 5
4 5
What I want to be able to do is move data from SubTable2 to SubTable1
I tried to do something like:
Select @.MasterKey from Master where Priority = 1 (this would give me
a MasterKey of 3)
insert (MasterKey,PK,Data)
Select @.MasterKey,PK,Data
From SubTable2
Where PK = 4
This would move/create 5 records with a @.MasterKey of 3 into the SubTable1.
This works as long as there is only
one MasterKey. But what if I want to create a 5 records for all (or a
potion) of the MasterKeys.
I could reexecute the command multiple times from a loop to get the results
I want, but I was curious if there was an easier way, using one SQL
Statement.
Thanks,
Tomtshad, it is not clear what you are trying to do. In the example you give
you would end up with 5 records in SubTable1 that all had MasterKey = 3 and
PK = 4, that doesn't seem to make much sense.
If you explain it better I can probably help. I think you may want to use an
IN list in the SELECT, so something like:
insert (MasterKey,PK,Data)
Select @.MasterKey,PK,Data
From SubTable2
Where PK IN (4, 5, 6)
or otherwise you may need to use a subquery or a join, but I just can't tell
what you're trying to do.
Sean
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uIw5J5tjFHA.3756@.TK2MSFTNGP15.phx.gbl...
>I am trying to find an easier way to handle my insert/select statement. If
>I have the following tables and query - Is there a way to do this in one
>statement?
> create table Master(
> MasterKey int,
> )
> Create table SubTable1(
> MasterKey int,
> PK int,
> Data varChar(1000)
> )
>
> Create table SubTable2(
> MasterKey int,
> PK int,
> Data varChar(1000
> )
> Master Table
> MasterKey Priority
> 1 0
> 2 0
> 3 1
> 4 0
> 5 0
> 6 0
> SubTable1
> Empty
> SubTable2
> MasterKey PK
> 1 1
> 1 1
> 1 2
> 2 3
> 2 3
> 3 4
> 3 4
> 3 4
> 4 4
> 4 4
> 4 5
> 4 5
> What I want to be able to do is move data from SubTable2 to SubTable1
> I tried to do something like:
> Select @.MasterKey from Master where Priority = 1 (this would give
> me a MasterKey of 3)
> insert (MasterKey,PK,Data)
> Select @.MasterKey,PK,Data
> From SubTable2
> Where PK = 4
> This would move/create 5 records with a @.MasterKey of 3 into the
> SubTable1. This works as long as there is only
> one MasterKey. But what if I want to create a 5 records for all (or a
> potion) of the MasterKeys.
> I could reexecute the command multiple times from a loop to get the
> results I want, but I was curious if there was an easier way, using one
> SQL Statement.
> Thanks,
> Tom
>
>

Friday, March 30, 2012

Inserting 2 tables with Pk/Fk

Inserting 1:M relationship data via One Stored Procedure

inserted/deleted tables

inserted/deleted tables

Inserted/deleted table.

INSERTED table and triggers

Inserted Rows

Inserted row deletes after trigger

Inserted records missing in sql table yet tables primary key field has been incremented.

inserted in dynamic query

Wednesday, March 28, 2012

Inserted and deleted temp tables ?

Inserted and Deleted tables

inserted and deleted tables

inserted and deleted table

inserted / deleted tables for triggers

Inserted & Deleted Tables!

Insert/Updated SP from multiple tables

insert/select

insert or replace

Blog Archive

About Me