insert or replace: records

Showing posts with label records. Show all posts

Friday, March 30, 2012

INSERTING 20Mill Records into a Table with 100Mill Records..

Hi, Iam new to SQL Srvr 2005 with a Oracle Background..

I have three tables
Table 1 (100 Mill Rows)
Table 2 (20 Mill Rows)
Table 3 (10 Mill Rows)

INSERT INTO Table1
select Table2.* from Table 2
where exists (select 1 from Table3
where Table2.xyz = Table3.xyz
and Table2.abc = Table3.abc);

Whats the most efficient way to do this..
Iam already DISABL'ng the Indexes before the Insert on Table 1
Also -- Whats the SQLSRVR's equivalent to Rollback Segment?

I would suggest to use a join and be sure to have indexes in table2 and table3 by the columns used in the join.

- index on table2 by (xyz, abc)

- index on table3 by (xyz, abc)

The index could be also by (abc, xyz), but it will depend on the order of an existing constraint like primary key or foreign key, or in case there not a constraint, then the selectivity of those columns..

INSERT INTO Table1

select

Table2.*

from

Table 2 inner join Table3

on Table2.xyz = Table3.xyz and Table2.abc = Table3.abc

AMB

|||

Inserting 20 mil rows at one time will create a log of Transaction Log activity.

IF, and that is a big IF, this is a singular operation, and if there is no other activity in the database, you may wish to change the recovery model to 'SIMPLE' (after first making a backup.)

Then do the import in batches of 100k rows - this will greatly reduce the logging pressure and could make a radical difference in speed.

When finished, return the recovery model to the previous setting. AND then make a full backup.

|||

If you go with changing the Recovery Model to simple and back, you'll want to be sure to run a full back-up immediately after reverting back.

Switching to the Simple model breaks the log chain and a full back-up is required to establish a new chain.

|||

Thanks, Dale,

I should have explicitly mentioned that (assumptions, assumptions, etc.)

inserting 100 records

How to insert 100 record at a time by explicit inserting of identity column i.e.., by setting identity column to false

You mean like:

INSERT INTO t1(c1,c2)

SELECT '1','2'

UNION

SELECT '3','4'

UNION

...

|||

This will turn off the identity column for a table,

set identity_insert <tablename> on

[insert 100 records .. ]

set identity_insert <tablename> off

|||

No i mean if identity column is off i.e.., the we should explicitly insert ID column by fetching an XML having 100 records for example

Table1

ID StudRollNo StudName

Inserting into table1(Identity column for column ID is OFF) where i will get the XML of table having 100 records like

ID StudRollNo Studname

|||If you mean to read data from XML into datbase table,?I?suggest?you?learn?XQuery?in?SQL2005

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.

Wednesday, March 28, 2012

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.

Insert/update/delete Transaction

Hi,

I have an unbound DataGridView and I have load it with a set of records from a Data base.

I modify existing rows, delete rows and add new rows to DataGridView control. I have to send a new modified dataset back to the data base.

Please any suggestions how to solve the problem?

Thanks in advance

George

Hi George,

I think you'll have more success posting your question on the Visual Studio forums - this is the T-SQL forum which is primarily used for back-end SQL questions, rather than user interface coding problems like DataGridViews.

Hope that helps :)

Menthos
|||Thanks :)sql

Friday, March 23, 2012

Insert Triggers

I have written an Insert Trigger to examine newly inserted records and set some values. However, each time a record is inserted, all records are checked. How can I make the trigger work only on newly inserted records?Within the trigger, you can access a view called INSERTED that shows only the rows that are being inserted by the statement that launched the trigger. You can use the INSERTED view (probably via a JOIN) to limit the number of rows you are affecting in your underlying table.

-PatP|||my telepathic usb port is clogged...can you post the trigger...

probably take us a few minutes...

DDL would be nice as well

and pat's correct(what again? say it ain't so...)|||CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
FOR INSERT
AS
insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
select tblGroup.Id as DataRecordId,
1 as TaskNum,
"Ready" as Status,
tblUsers.Id as UserId,
getdate() as StartDateTime
from tblGroup, tblUsers, tblVendors where (tblGroup.I_Field3=tblVendors.OdissVendorId)
And (tblGroup.I_Field6 Is Null OR tblGroup.I_Field6='0')
And (tblUsers.WFID=1)

..a little complex. the check for tblGroup.I_Field6 is necessitated because all records are being checked - this where clause could be stripped off if only new records were being checked.|||Something like this would do it:

CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
FOR INSERT
AS
if exists (select 1 from inserted)
insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
select i.Id, 1, 'Ready', u.Id, getdate()
from inserted i
inner join tblVendors v
on i.I_Field3=v.OdissVendorId
inner join tblUsers u
on (u.WFID=1)|||thanx..will try this.

Wednesday, March 21, 2012

Insert trigger calls .NET application

I have an appication that feeds a SQL Server 2005 database with records. I
have another application that should treat the records inserted by this firs
t
application. I know you can achieve this by incorporating .NET code in SQL
Server 2005.
However is there another possibility that SQL Server tiggers my second
application after inserting records from the first application?
thanks.Hello Guy,

> I have an appication that feeds a SQL Server 2005 database with
> records. I
> have another application that should treat the records inserted by
> this first
> application. I know you can achieve this by incorporating .NET code in
> SQL
> Server 2005.
> However is there another possibility that SQL Server tiggers my second
> application after inserting records from the first application?
There's a couple of ways of doing that:
a. Use service broker to do the inserts and send a copy of the data to a
queue that your second program processes
b. Use a SQLDependency in your second application to watch the table in ques
tion,
get the new records and process them.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql

Monday, March 19, 2012

Insert stored procedure for related tables

I have two sets of related tables: Quote - QuoteDetail and Order - OrderItem

I need to copy Quote - QuoteDetail records to Order - OrderItem tables

I have the stored procedure up to this point: Insert a Quote in the Order table and get the new Order @.@.Identity.

I need to insert the QuoteDetail records into the OrderItem table using the new OrderID

Thank you for your help.Thanks to all who looked!

I figured it out. I was trying to make it harder than it actually was.|||Hope this helps !


CREATE PROCEDURE [InsertTest]AS
INSERT INTO tblPerson(Login,Password,Email,DateCreated)
VALUES('jaja','aaa','22@.yahoo.com','02-03-04')
INSERT INTO tblSnippet(CategoryID,PersonID,Title,Description,DateCreated)
VALUES(1,@.@.IDENTITY,'HAHA','This is good article',GETDATE());
GO

|||can u post the solution :)|||Here is the code you requested. I was having a mental block over the Select versus the VAULES() to put the @.OrderID into the new Item records. The simple solution is setting the@.OrderID AS OrderID in the SELECT statement.


CREATE PROCEDURE dbo.ECPO_Quote_Convert
(
@.QuoteIDint,
@.OrderIDint output
)
AS
INSERT INTO ECP_Order
(
UserID, ...
(rest of the fields)
 )
SELECT
UserID, ...
(rest of the fields)
FROM
ECP_Quote
WHERE
QuoteID = @.QuoteIDSELECT @.OrderID = @.@.Identity
-- Insert QuoteDetail
INSERT INTO ECP_OrderItem
(
OrderID, ...
(rest of the fields)
 )
SELECT
@.OrderID AS OrderID, ...
(rest of the fields)
FROM
ECP_QuoteDetail
WHERE
QuoteID = @.QuoteID AND Quantity > 0

Monday, March 12, 2012

Insert Statement Help

I need to insert over 600 similar records in a table. The only way I know is to write insert into <table> command 600 times. Is there another way which is easier?
Thanks in advance,
SauravFirst create this interger table

CREATE TABLE Numbers(
Number INT NOT NULL,
CONSTRAINT PK_Numbers
PRIMARY KEY CLUSTERED (Number)
WITH FILLFACTOR = 100)
INSERT INTO Numbers
SELECT
(a.Number * 256) + b.Number AS Number FROM
(SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number <= 255)a (Number),
(SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number <= 255)b (Number)
GO

now run this

CREATE TABLE test(
name VARCHAR(20),
age INT)

INSERT INTO test VALUES('joy',30)

INSERT INTO test

SELECT name,age FROM test
CROSS JOIN Numbers
WHERE number<601|||Thank you Rudra for the reply. However, I need to insert similar values (actually not the same values). Values in certain columns are same and in others different.

Thanks,
Saurav|||Thank you Rudra for the reply. However, I need to insert similar values (actually not the same values). Values in certain columns are same and in others different.

Thanks,
Saurav

Please give some more info,I mean examples of your table and data.Then it would be easy for us to help you.Please read the sticky at the top most post.|||Where is this data now?|||Where's the data now?|||What is the location and format of the data at present?

(just thought I'd change it up a bit ;) )|||If the data is some sort of file you should create a DTS package and import the data. It would be a lot easier than creating some sort of BULK insert statement|||When nothing has been done with the data,then it should there where it was earlier...so don't worry be happy ;)|||Yes, my fuzzy friend, but we were not able to glimpse the location of the data earlier.

And what you say is not always true, glasshoppa...sometimes doing nothing can cause loss of data, which would mean it is not where it was...and further cause a great deal of debate over whether it ever was.|||Welcome back Paul,I missed you a lot ...;)|||i think the real issue should not be locating this data. Rather it's the logic our friend requires to sort out his difficulty.|||i think the real issue should not be locating this data. Rather it's the logic our friend requires to sort out his difficulty.So the logic is independent of whether the data is handwritten on some forms on his desk, contained in qualitative text in a word document or normalised and typed in an Oracle database?|||i think the real issue should not be locating this data. Rather it's the logic our friend requires to sort out his difficulty.Yes, as Pootie has so well highlighted, perhaps the logic our friend requires to sort out his difficulty is rooted in the location of his data. In fact, one might argue that at least on the surface, the origin of data is one of the cornerstones of database analysis and design.

In fact, I put forth for your consideration the assertation that without knowing the origin of one's data, the manipulation of said data is perhaps nearly impossible.

Or, as Grandma used to say, one cannot hope to successfully build a relational database for the future without knowing intimately the data around which the database is to be built, and this knowledge is largely based upon knowing the past of one's data.

She usually followed up this bit of sage advice with an often lengthy tirade against the use of cursors, and sometimes followed that with a treatise on the evils of using sweet apples in an apple pie...but that's a discussion for a different thread.

insert statement

When I run below statement, I got 3 records insertion.
I only want 1 record added when there was any update on any column on the
source data. I don't want update statement because, I would like to see all
the change from time to time.
Please help,
Culam.
INSERT INTO CUSTOMER_PROFILE_HIST
([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
FROM
CUSTOMER_PROFILE src
LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
WHERE
ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)try using inner join.. Just a guess
--
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||You will have problems after there are 2 records for the customer in
the history file because one will always be different than the current.
You need to only compare to the latest historical record.|||Thanks Jeff.
Do you know the way to insert 1 record when multiple fields are changed?
My method will insert new records for each changed field.
Lam
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||try this.
INSERT INTO CUSTOMER_PROFILE_HIST
([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
FROM
CUSTOMER_PROFILE src
WHERE
not exists( select 1 from CUSTOMER_PROFILE_HIST dst where
src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
ISNULL(src.[RATE], 0) = ISNULL(dst.[RATE],0)
AND ISNULL(src.[AGE1], 0) = ISNULL(dst.[AGE1], 0)
AND ISNULL(src.[AGE2], 0) = ISNULL(dst.[AGE2], 0)
)|||Thanks, it works.
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||On Tue, 9 May 2006 13:32:03 -0700, culam wrote:

>Thanks Jeff.
>Do you know the way to insert 1 record when multiple fields are changed?
>My method will insert new records for each changed field.
Hi Lam,
No, it won't.
It will insert new rows for each existing row in the history table. If
you have three rows in CUSTOMER_PROFILE_HIST, you'll get three
additional rows (or rather: maximum three rows - if any of the existing
history rows happens to match the current rw on all columns, you'll only
get two new rows).
Check JeffB's reply - he hit the nail right on the head.
If yoou need more assitance, then please check out www.aspfaq.com/5006
to find out what additional information yoou need to give to make it
possible for us to help you.
Hugo Kornelis, SQL Server MVP|||All Credit goes to Jeff. I just ex[anded his point of view.
--
"culam" wrote:
> Thanks, it works.
> "culam" wrote:
>|||Unless 3 changed fields actually causes 3 separate inserts. I have seen
this happen (on the application side) where a change to department, salary,
and jobcode actually triggers 3 separate transactions. This is not to
suggest that your post is inaccurate, only that the OP could possibly be
referring to something else here...
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:e23262pmojsjfnt5aph2jdausbrmmv9lcc@.
4ax.com...
> On Tue, 9 May 2006 13:32:03 -0700, culam wrote:
>
> Hi Lam,
> No, it won't.
> It will insert new rows for each existing row in the history table. If
> you have three rows in CUSTOMER_PROFILE_HIST, you'll get three
> additional rows (or rather: maximum three rows - if any of the existing
> history rows happens to match the current rw on all columns, you'll only
> get two new rows).
> Check JeffB's reply - he hit the nail right on the head.
> If yoou need more assitance, then please check out www.aspfaq.com/5006
> to find out what additional information yoou need to give to make it
> possible for us to help you.
> --
> Hugo Kornelis, SQL Server MVP

Friday, March 9, 2012

Insert records while purging

Not sure if this is possible, seems like it should be with
the right locking mechanism.
I'm purging the oldest dated records from a fairly large
table, and want to be able to insert a new record. The new
record would have a current date(GetDate()). My first
tests are not going well. I can't insert the record at
all. So I am a bit confused as to why. Shouldn't I be able
to insert a record while a purge/delete is occuring if the
records are at opposite ends of the clustered index?
Any help is much appreciated.
Thanks,
JamesBefore an insert, the engine doesn't pre-determine what the value for a
column will be in order to determine whether or not it would be affected by
any existing queries. This is true for constants, variables, computed
columns and, yes, even those with defaults (because you could override the
default). So, rather than risk deciding between an insert and a delete for
a particular row that overlaps under both queries, it blocks inserts until
the delete is finished.
"James" <bigg_game_james@.hotmail.com> wrote in message
news:035c01c39a7d$6bed7720$a501280a@.phx.gbl...
> Not sure if this is possible, seems like it should be with
> the right locking mechanism.
> I'm purging the oldest dated records from a fairly large
> table, and want to be able to insert a new record. The new
> record would have a current date(GetDate()). My first
> tests are not going well. I can't insert the record at
> all. So I am a bit confused as to why. Shouldn't I be able
> to insert a record while a purge/delete is occuring if the
> records are at opposite ends of the clustered index?
> Any help is much appreciated.
> Thanks,
> James|||If your table is properly indexed, you should be able to control how many
rows you need to delete each time without locking up the whole table. As
long as you don't ending locking the whole while you are doing your delete,
you should be able to insert the new row.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"James" <bigg_game_james@.hotmail.com> wrote in message
news:035c01c39a7d$6bed7720$a501280a@.phx.gbl...
> Not sure if this is possible, seems like it should be with
> the right locking mechanism.
> I'm purging the oldest dated records from a fairly large
> table, and want to be able to insert a new record. The new
> record would have a current date(GetDate()). My first
> tests are not going well. I can't insert the record at
> all. So I am a bit confused as to why. Shouldn't I be able
> to insert a record while a purge/delete is occuring if the
> records are at opposite ends of the clustered index?
> Any help is much appreciated.
> Thanks,
> James|||Quick question about this issue.
In my purge routine, I break up the purge by only deleting
a fraction of the records between commits. If an insert
has been executed, shouldn't it get to execute between the
delete statements? Do I need manually escalate a lock for
that insert statement?
>--Original Message--
>Before an insert, the engine doesn't pre-determine what
the value for a
>column will be in order to determine whether or not it
would be affected by
>any existing queries. This is true for constants,
variables, computed
>columns and, yes, even those with defaults (because you
could override the
>default). So, rather than risk deciding between an
insert and a delete for
>a particular row that overlaps under both queries, it
blocks inserts until
>the delete is finished.
>
>"James" <bigg_game_james@.hotmail.com> wrote in message
>news:035c01c39a7d$6bed7720$a501280a@.phx.gbl...
>> Not sure if this is possible, seems like it should be
with
>> the right locking mechanism.
>> I'm purging the oldest dated records from a fairly large
>> table, and want to be able to insert a new record. The
new
>> record would have a current date(GetDate()). My first
>> tests are not going well. I can't insert the record at
>> all. So I am a bit confused as to why. Shouldn't I be
able
>> to insert a record while a purge/delete is occuring if
the
>> records are at opposite ends of the clustered index?
>> Any help is much appreciated.
>> Thanks,
>> James
>
>.
>|||> In my purge routine, I break up the purge by only deleting
> a fraction of the records between commits. If an insert
> has been executed, shouldn't it get to execute between the
> delete statements?
Yes. If it's being blocked, it should be able to jump in between your
delete batches (assuming they are also committed individually).|||So I was definitely on the right track. I had the design
right, but accidently had set implicit_transactions to ON,
once I turned it off, as I originally intended, my inserts
were allowed through.
Thanks for the quick responses.
James
>--Original Message--
>> In my purge routine, I break up the purge by only
deleting
>> a fraction of the records between commits. If an insert
>> has been executed, shouldn't it get to execute between
the
>> delete statements?
>Yes. If it's being blocked, it should be able to jump in
between your
>delete batches (assuming they are also committed
individually).
>
>.
>

Wednesday, March 7, 2012

Insert Records Using from Text box to SQL database

Hi,

I am having three text box which accepts user data & insert that in sql database. But I am not able to do this , I think this is the simplest of all . Can somebody plz tell me how this can be done from scratch that is connection string & settings in web.config ? Plz guide using C#

Thanks

Regards,

-Sunny,

Here's a simple example using a SqlDataSource.

WEB.CONFIG

<connectionStrings><addname="NorthwindConnectionString"connectionString="Data Source=AMERUS-CW6GLINJ\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"providerName="System.Data.SqlClient" /></connectionStrings>

ASPX

CompanyName:<asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"insertcommand="INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@.CompanyName, @.Phone)"selectcommand="SELECT * FROM [Shippers]"><insertparameters><asp:controlparameter controlid="txtCompanyName" name="CompanyName" /><asp:controlparameter controlid="txtPhone" name="Phone" /></insertparameters></asp:sqldatasource>

CODE-BEHIND

protected void btnSubmit_Click(object sender, EventArgs e){SqlDataSource1.Insert();}

|||

One more thing Can you please tell me how can I auto update date & time in above code ?

I mean user need not to enter Date & time, it should be directly uploaded on database when user click on button & it should be visible to admin when the user has clicked the & entered the information.

Thanks You,

Regards,

-Sunny.

|||

Assuming you had a Parameter for your DateTime field, I'd simply set it within the SqlDataSource.Inserting event handler. Then set it to DateTime.Now.

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e){e.Command.Parameters["@.DateCreated"].Value = DateTime.Now;}

|||

I tried below code but its not inserting any date in my database Sad

<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>" insertcommand="INSERT INTO [TryNow] ([Name],, [organization] ) VALUES (@.CompanyName, @.Phone, @.Org)" selectcommand="SELECT * FROM [TryNow]"> <insertparameters><%--<asp:ControlParameter Controlid="txtDate" Name="DateCreated" />--%> <asp:controlparameter controlid="txtName" name="CompanyName" /> <asp:controlparameter controlid="txtEmail" name="Phone" /> <asp:controlparameter controlid="txtOrg" name="Org" /> <asp:Parameter Name="DateCreated" /> </insertparameters> </asp:sqldatasource>

Code Behind :

protected void btnSubmit_Click(object sender, EventArgs e) { SqlDataSource1.Insert(); }protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e) { e.Command.Parameters["@.DateCreated"].Value = DateTime.Now; }

Can you please tell me where I am going wrong ?

Thank You,

Regards,

-Sunny.

|||

Dose this technique also closes the SQL connection or must you do something else?

|||

Yes this creates connection & I used same code, nothing else is been getting done here.

Please let me know why date field is not getting updated here. Thank you

Regards,

-Sunny.

|||

I can't really help you with the "Date and Time", but my question was if the created SQL connection was closed at the end of the INSERT or was there something we needed to do to make sure it closes?

PS: I'm not just lazy I just don't know how to use the "Date and Time". I did try, without succes.

|||how do i get this working i keep getting the following error Cannot insert the value NULL into column 'user_id', table 'cse.cse.priti_userid'; column does not allow nulls. INSERT fails.The statement has been terminated.although my database seems to fine...also where it says @.username do i need to set it as a variable?|||

The problem is pretty simple.
Your INSERT statement is trying to insert nothing (NULL) into "user_id" and in your database "user_id" dose not allow nulls ().
If user_id is a int and has identity set to yes then just ignore it in your INSERT statement. When a column is set to identity it's all automatic.

I'm not sure about the @.username question? When you are learning SQL go simple try 1 or 2 columns at a time and allow nulls, when you get the hang of it then you can start to normalize.

Did this help? let me know.

|||kinda. how do you get data from a textbox to insert into a database?|||

Well this is the code you have in this thread. It's kind of hard to break it down any more, let me see your source code of your ASPX page ASPX.CS page and your WEB.CONFIG page.

|||

the coding i have so far is as follows

front page

<%@. Page Language="C#" AutoEventWireup="true" CodeFile="register.aspx.cs" Inherits="Default2" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> username <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> password <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> email address<asp:TextBox ID="email" runat="server"></asp:TextBox><br /> security question <asp:TextBox ID="securityq" runat="server"></asp:TextBox> <br /> security answer <asp:TextBox ID="securitya" runat="server"></asp:TextBox><br /> <br /> <asp:Label ID="Label1" runat="server"></asp:Label><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="register" />  <br /> </div> </form></body></html>

backend

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;public partial class Default2 : System.Web.UI.Page
{
 protected void Page_Load(object sender, EventArgs e)
 {
 }
 protected void TextBox1_TextChanged(object sender, EventArgs e)
 {
 }
 protected void TextBox5_TextChanged(object sender, EventArgs e)
 {
 }
 protected void Button1_Click(object sender, EventArgs e)
 {
 string un;
 string pw;
 string mysql;
 un = TextBox1.Text;
 pw = TextBox2.Text;
 mysql = "Insert into priti_userid(user_id,password)" + " Values('" + un + "','" + pw + "')";
 Label1.Text = "Thanks for signing up";
 }
 protected void password_TextChanged(object sender, EventArgs e)
 {
 }
}

webconfig

<?xml version="1.0"?>
<!--
 Note: As an alternative to hand editing this file you can use the
 web admin tool to configure settings for your application. Use
 the Website->Asp.Net Configuration option in Visual Studio.
 A full list of settings and comments can be found in
 machine.config.comments usually located in
 \Windows\Microsoft.Net\Framework\v2.x\Config
-->
<configuration>
<appSettings/>
<connectionStrings>
 <add name="cseConnectionString" connectionString="Data Source=SQLB1.webcontrolcenter.com;Initial Catalog=cse;Persist Security Info=True;User ID=cse;Password=salford"
 providerName="System.Data.SqlClient" />
 </connectionStrings>
<system.web>
<!--
 Set compilation debug="true" to insert debugging
 symbols into the compiled page. Because this
 affects performance, set this value to true only
 during development.
 -->
<compilation debug="true"/>
<!--
 The <authentication> section enables configuration
 of the security authentication mode used by
 ASP.NET to identify an incoming user.
 -->
<authentication mode="Windows"/>
<!--
 The <customErrors> section enables configuration
 of what to do if/when an unhandled error occurs
 during the execution of a request. Specifically,
 it enables developers to configure html error pages
 to be displayed in place of a error stack trace. <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
 <error statusCode="403" redirect="NoAccess.htm" />
 <error statusCode="404" redirect="FileNotFound.htm" />
 </customErrors>
 -->
</system.web>
</configuration

|||

The things you should consider is when you code use strong names so when your page is 1000 lines you are sure of what you are using (securityq = securityQuestionTextBox ).

When you post here remember to remove your passwords a specially if your database is live.

If you want to use the method suggested up here in this post your code should go like this:

No change to the web.config

ASPX

<form id="form1" runat="server">
<div>
username
<asp:TextBox ID="userNameTextBox" runat="server"></asp:TextBox><br />
password
<asp:TextBox ID="passwordTextBox" runat="server"></asp:TextBox><br />
email address<asp:TextBox ID="email" runat="server"></asp:TextBox><br />
security question
<asp:TextBox ID="securityQuestionTextBox" runat="server"></asp:TextBox>
<br />
security answer
<asp:TextBox ID="securityAnswerTextBox" runat="server"></asp:TextBox><br />
<br />
<asp:Label ID="statusLabel" runat="server"></asp:Label><br />
<br />
<asp:Button ID="sendButton" runat="server" Text="register"
onclick="sendButton_Click" /> <br />



<asp:sqldatasource id="SqlDataSource" runat="server" connectionstring="<%$ ConnectionStrings:cseConnectionString%>"

insertcommand="INSERT INTO [priti_userid] ( [user_id], [password], [securityQuestion], [securityAnswer])
VALUES (@.userName, @.password, @.securityQuestion, @.securityAnswer)"

selectcommand="SELECT * FROM [priti_userid]">

<insertparameters>
<asp:controlparameter controlid="userNameTextBox" name="userName" />
<asp:controlparameter controlid="passwordTextBox" name="password" />
<asp:controlparameter controlid="securityQuestionTextBox" name="securityQuestion" />
<asp:controlparameter controlid="securityAnswerTextBox" name="securityAnswer" />
</insertparameters>

</asp:sqldatasource>

</div>
</form>

Code Behind

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

public partialclass _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void sendButton_Click(object sender, EventArgs e)
{
SqlDataSource.Insert();
statusLabel.Text ="Thanks for signing up";
}
}

If you want to do the insert in C# well good luck that's where I am, when I make it work I'll let you know.

|||

thanks for the above...trying to get it working but it the sqldatasource.insert function will not work.'inserting is not supported by data source 'sqldatasource' unless insertcommand is specified' ?

Insert records into unusual table

This problem goes against everything that I've been taught and have always read about, so please don't think I designed this. :)

I'm trying to write a procedure to insert orders from 3 tables into one table to import into our mail order system. That's not a problem.
The problem is the mapping.
The basic design:

tblOrders (order_id, customer_id, shipto_id, billto_id, card_num, etc.)
tblOrderItems ( item_id, item_price, item_name, etc.)
tblContacts(contact_id, f_name, l_name, card_no, etc.)

BUT, I have to export to this table format....REALLY!!

tblOrdersToExport (
order_id, contact_id, fname, lname, address, city, state, zip, country,
-- now here's the good part
product1, quanity1, price1,
product2, quanity2, price2,
product3, quanity3, price3,
product4, quanity4, price4,
product5, quanity5, price5,
order_continued
)

The order_continued is flagged if the order contains more line items than product fields (5), and the row doesn't contain any information except product#, quanity#, price# and the order_continued flag.

I think I've got the right idea. I'm playing with cursors and some procedures, but I'm running into problems when I have more than 5 items and have to insert into this very unusual structure.

I know this is strange, but it's what I have to work with and i have no way of changing the db structure or table layout since it's part of a program that was purchased and there's not way that I can recode anything.

If someone can make sense of what I'm trying to do, I can post an example if it will help. I wanted to keep the length of this message short and it's very hard to explain sense it doesn't make much sense.

thanks
RandyCan you post the code you've written so far, and indicate where it runs into problems? It sounds like you just need to loop through the items and whenever you get to a multiple of 5 + 1 start a new record - i.e. at item 6, 11, 16, ...

Whoever designed their database should be shot!

Insert records in multiple tables via store proc

Any help will be appreacited
I need to insert records into multiple tables via store proc. I wrote a query statement that does that, but I need to carry one value to the next piece of the script, which is easy via query analizer, but I do not know how to pass that value to the next step in the store proc. Please see the query I am using to give me some light. The case sample is 9731285 and needs to be carry out to each step in the store proc. Thank you!
DECLARE @.Casenumber as char(20
SET @.CASENUMBER = '9731285
INSERT INTO tblCaseDat
(CaseNumber, DisplayCaseNumber
VALUES (@.CASENUMBER, (left(@.casenumber, 2))+'-'+rtrim(Right(@.casenumber,18))
G
declare @.casenumber char(20
select @.casenumber = '9731285
INSERT INTO tblname (longname
values (@.casenumber+' '+ 'Debtor1'
g
declare @.casenumber char (20
select @.casenumber = '9731285
INSERT INTO tblCasename (caseid, NameID, NameTypeID
(select caseid, (Select NameI
from tblnam
where longname =(@.casenumber+' '+ 'Debtor1')), '5
from tblcasedat
where casenumber = @.casenumber
G
declare @.casenumber char(20
select @.casenumber = '9731285
INSERT INTO tblname (longname
values (@.casenumber+' '+ 'Debtor2'
g
declare @.casenumber char (20
select @.casenumber = '9731285
INSERT INTO tblCasename (caseid, NameID, NameTypeID
(select caseid, (Select NameI
from tblnam
where longname =(@.casenumber+' '+ 'Debtor2')), '6
from tblcasedat
where casenumber = @.casenumber
Gthe batch separator (GO) resets any variable declarations.
hence, if you remove the 'GO'
remove the additional DECLARE / SET CaseNumber,
you can execution the entire set of statments as one
batch, which can be put into a stored proc,
also, an explicit BEGIN TRAN , COMMIT TRAN around the
entire set of inserts statements is probably warranted
>--Original Message--
>Any help will be appreacited.
>I need to insert records into multiple tables via store
proc. I wrote a query statement that does that, but I need
to carry one value to the next piece of the script, which
is easy via query analizer, but I do not know how to pass
that value to the next step in the store proc. Please see
the query I am using to give me some light. The case
sample is 9731285 and needs to be carry out to each step
in the store proc. Thank you!!
>DECLARE @.Casenumber as char(20)
>SET @.CASENUMBER = '9731285'
>INSERT INTO tblCaseData
> (CaseNumber, DisplayCaseNumber)
>VALUES (@.CASENUMBER, (left(@.casenumber, 2))+'-'+rtrim
(Right(@.casenumber,18)))
>GO
>declare @.casenumber char(20)
>select @.casenumber = '9731285'
>INSERT INTO tblname (longname)
> values (@.casenumber+' '+ 'Debtor1')
>go
>declare @.casenumber char (20)
>select @.casenumber = '9731285'
>INSERT INTO tblCasename (caseid, NameID, NameTypeID)
> (select caseid, (Select NameID
> from tblname
> where longname =(@.casenumber+' '+ 'Debtor1')), '5'
> from tblcasedata
> where casenumber = @.casenumber)
>GO
>declare @.casenumber char(20)
>select @.casenumber = '9731285'
>INSERT INTO tblname (longname)
> values (@.casenumber+' '+ 'Debtor2')
>go
>declare @.casenumber char (20)
>select @.casenumber = '9731285'
>INSERT INTO tblCasename (caseid, NameID, NameTypeID)
> (select caseid, (Select NameID
> from tblname
> where longname =(@.casenumber+' '+ 'Debtor2')), '6'
> from tblcasedata
> where casenumber = @.casenumber)
>GO
>.
>

INSERT Records in multiple tables

I need to update two tables. I have created a view and am using the code in the attached file to insert into the two tables.

The page loads without errors, but I get this message that the view is not updatable because the modification affects multiple base tables.

I thought this was the purpose of views?

Does anyone have any suggestions? I am using Dreamweaver MX and SQL Server.

Thanks!
NNo, that is not the purpose of views. Views are frequently not updateable, and I don't think it is ever possible to update different columns from different tables in the same view. Even a direct SQL Update statement will only update one table at a time, so you will need to issues separate update statements or handle the problem through triggers or cascading updates.

Truth is, views don't serve much purpose any more.

Good database application design principles dictate making all your updates through stored procedures. Your application should rarely if ever have direct access to the database tables, even for retrieving data.|||No, you can update the columns of each of the base table independantly (one or more UPDATE statements per base table), but you can't update multiple base tables in a single pass.

Thinking outside of the SQL box, a table represents a relational algebra entity. An entity has no inherant order for either columns or rows, they behave something like a hash in that respect.

Views represent a relational algebra result. A result can have order, there can be a first, middle, and last for both rows and columns in a view.

-PatP|||Views (with multiple base tables) can be updated at one shot by using INSTEAD OF trigger

Here is some supporting article from MSDN

Cheers

Benny
-----------------------

Modifying Data Through a View
You can modify data through a view in these ways:

Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements.

Use updatable partitioned views that modify one or more member tables.
If a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that:

The view contains at least one table in the FROM clause of the view definition; the view cannot be based solely on an expression.

No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. However, aggregate functions can be used within a subquery defined in the FROM clause provided that the derived values generated by the aggregate functions are not modified.

Note Partitioned views using the UNION ALL operator can be updatable.

No derived columns are used in the select list. Derived columns are result set columns formed by anything other than a simple column reference.
Guidelines for Modifying Data Through a View
Before you modify data through a view without using an INSTEAD OF trigger or an updatable partitioned view, consider these guidelines:

All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.

SQL Server must be able to resolve unambiguously the modification operation to specific rows in one of the base tables referenced by the view. You cannot use data modification statements on more than one underlying table in a single statement. Therefore, the columns listed in the UPDATE or INSERT statement must belong to a single base table within the view definition.

All the columns in the underlying table that are being updated and do not allow null values have values specified in either the INSERT statement or DEFAULT definitions. This ensures that all the columns in the underlying table that require values have them.

The data modified in the columns in the underlying table must adhere to the restrictions on those columns, such as nullability, constraints, DEFAULT definitions and so on. For example, if a row is deleted, all the underlying FOREIGN KEY constraints in related tables must still be satisfied for the delete to succeed.

A distributed partition view (remote view) cannot be updated using a keyset-driven cursor. This restriction can be resolved by declaring the cursor on the underlying tables and not on the view itself.
Additionally, to delete data in a view:

Only one table can be listed in the FROM clause of the view definition.|||Originally posted by blindman
Truth is, views don't serve much purpose any more.

what??!!

maybe not for use by the DBA, but for use by end users in a reporting environment, views are invaluable

"much purpose any more"?

what do you suppose the purpose of a view used to be then, before it got to where this purpose was diluted?

okay, here's an example

say a table is called Accounts and say it contains a column called LedgerCode and say the column values range from A to E, and now you have to change the table so that instead of values A to E, the LedgerCode becomes a numeric tinyint foreign key to a Ledger table with values A through Z

the mere fact that you can have a view with a join in it eliminates the need for the end user to figure out how to write a join

rename the table, change the table, declare a view called Accounts, build the join into the view, and voila, all existing code that used to select from the Accounts table still works

a long time ago i used to work in a shop where end users never got to use base tables, they were always given just views, and i can definitely see the logic behind that

it's called program-data independence|||Let me rephrase that...

Truth is, views don't serve much purpose any more, "IMHO".

I used to use views a lot too, specifically for program-data independence. Now, at least for application development, I always use Sprocs or UDFs.|||cool :cool:|||I believe you mean...

"Cool, IMHO." ;)|||indeed

burying application code inside sporcs and fuds is not cool to everybody, i admit -- especially those folks who would like to see a clear separation of application logic from proprietary database languages

usually i insist on declarative relational integrity but otherwise force application logic outside the database

you know, like so that your app is not dependent on any particular dbms

but sporcs and fuds are reasonably coolish, in my opinion, yeah|||Okey-dokey. I usually take the exact opposite approach, putting as much application logic into the RDBMS as possible, so that the application is not dependent on any particular interface. The reasoning is that these days people frequently want to access their data through different channels, such as a VB application, a Crystal Report, Access ADP project, Dot-Net, etc. By putting the application logic in the database you ensure consistent input and output and you avoid duplicating code. Let the the interface do what it does best: display the data and guide the user through it.

I guess the decision depends upon whether it is more likely that your application will need to be ported to a different RDBMS, or that users will come up with new requirements for accessing it. Perhaps I prefer the latter because the former results in boring "file cabinet" databases which frequently lack any sort of referential integrity. They just aren't as much fun or rewarding to work with as a database which is practically an application in itself.

Insert Records from Foxpro tables to SQL Server tables

Hi,

Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:

1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.

I only know the following ways to import Foxpro data into SQL Server:

#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables

I'm thinking whether the following choices will be better than the current way:

1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2

Thank you for any suggestion.There are more ways to skin a cat than there are cats, but that is no reason to stop trying!

Without knowing a lot more about your situation, it is tough for me to reocmmend any one approach. A lot will depend on whether you are closer to wanting the data "as is" from your FoxPro tables, or "cleaned up" as you intend to use it going forward in SQL.

There are also two options which you haven't mentioned. FoxPro can gleefully use SQL Server as its data store (instead of DBF files). SQL Server will happily read DBF files made visible using sp_addlinkedserver.

While I can offer lots of opinions, you are the one that needs to make it work. If you have a preference for one method over another, then I'd say that you should go for it!

-PatP|||Hi Pat,

I'm moving from a pure VFP application which read/write data to VFP tables to a VFP application which read/write data to SQL Server tables. So, I need to convert all the existing VFP data to the SQL Server tables so the new VFP application can read/write data. The VFP tables and the SQL Server tables are different in both structures and relationships.

The current way to convert the existing VFP data to the SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables. For example, VFP table cust_vfp has name, address1 , address2, and other columns which is converted to 2 SQL Tables cust_sql which contains name and other columns and another one custaddr_sql contains name, address so each name can relate to multiple addresses.

Thank you for any help.

insert records dependent on values from other table - with a l

thats great, all looks fine when the input data are from a table or view.
My data comes from a select statement from table1
for example:
SELECT [id] , InvoiceId + SoldTo AS f1, pieces as nr FROM table 1 order
by InvoiceId
how can i use this as a input.
best regards
Xavier
"R.D" wrote:
> Xavier
> Try this, If its ok for you to use cursors
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER proc Myproc
> as
> DECLARE @.ID int,
> @.F1 varchar(20),@.f2 varchar(10),
> @.NR INT,
> @.COUNTER INT
> DECLARE Mycursor CURSOR
> READ_ONLY
> FOR SELECT [ID],f1,nr FROM TABLE1
> OPEN Mycursor
> FETCH NEXT FROM Mycursor INTO @.ID,@.F1,@.NR
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> select @.COUNTER = 1
> WHILE (@.NR > (@.COUNTER - 1))
> BEGIN
> select @.f2 = @.F1 + CAST(@.COUNTER AS VARCHAR(10))
> INSERT INTO TABLE1([ID],F2) VALUES(@.ID,@.f2)
> SELECT @.COUNTER = @.COUNTER + 1
> END
> END
> FETCH NEXT FROM Mycursor INTO @.ID,@.F1,@.NR
> END
> CLOSE Mycursor
> DEALLOCATE Mycursor
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> Regards
> R.D
> --Post back if you want something less of cursors
>
> "Xavier" wrote:
>Just change the FOR SELECT statement in the cursor
That should work
Regards
R.D
"Xavier" wrote:
> thats great, all looks fine when the input data are from a table or view.
> My data comes from a select statement from table1
> for example:
> SELECT [id] , InvoiceId + SoldTo AS f1, pieces as nr FROM table 1 order
> by InvoiceId
> how can i use this as a input.
> best regards
> Xavier
> "R.D" wrote:
>

INSERT RECORD with PDF WOED files

Hi guys!

I've made a simple INSERT form write some records in a database...

then I need to associate to every record a PDF FILE or a WORD FILE..

so who (a user) insert a record should upload a file ...

How could associate the record to the file that an user upload?

classical article pubblication problem...do you know some tutorial?

3rdEyed

Hi 3rdEyed,

There are 2 ways that come to my mind in doing this.

1. Add a VarChar field in your table which stores the path and file name of the PDF or DOC file. You can later get this file from the file system and do whatever you like.

2. Use a FileStream to get file in a byte array. You can store the byte array in a binary field in database table.

I will recommend the first way, since it will not give much overhead to database.

|||

Kevin Yu - MSFT:
Hi 3rdEyed,
There are 2 ways that come to my mind in doing this.
1. Add a VarChar field in your table which stores the path and file name of the PDF or DOC file. You can later get this file from the file system and do whatever you like.
2. Use a FileStream to get file in a byte array. You can store the byte array in a binary field in database table.
I will recommend the first way, since it will not give much overhead to database.

Hi thanks for the answer..did you some example SCRIPT or TUTORIAL?

insert random records..HELP!

Once again - My table should consist of 100 new records for a field MobilePhone(of char type) and last 5 digits should be randomly choosed (should be like this: +381randomno1randomno2.. etc.(example: +38156465, where '+' sign makes it char type and digits after +381 are randomly choosed. :confused: Anyone knows how to solve this...PLEASE?No need for replies guys..ive figured this out ..thanx, anyway :)|||No need for replies guys..ive figured this out ..thanx, anyway :)
Would it not be a good idea to post your solution? Some may benifit from your solution who may have a similar problem. Also, let's not forget the intelligence level in this community (excluding me of course); they may have suggestions to fine tune your solution!

Just my oppinion. Every question posted should be accompanied by a solution I think!

Mike B|||[QUOTE=MikeB_2k4]Would it not be a good idea to post your solution? Some may benifit from your solution who may have a similar problem. Also, let's not forget the intelligence level in this community (excluding me of course); they may have suggestions to fine tune your solution!

Just my oppinion. Every question posted should be accompanied by a solution I think!

Im really sorry..you are so right.Ok,here is the solution:

create table #randomphonenumbers( nmbr char(10) primary key )

declare @.digits table(nr char(1))
insert @.digits(nr) select '0' union select '2' union select '4' union select '6' union select '8'
insert @.digits(nr) select nr+1 from @.digits -- implicit conversion

insert #randomphonenumbers( nmbr )
select top 100 '+' + '388' + a.nr+b.nr+c.nr+d.nr+e.nr
from @.digits a cross join @.digits b cross join @.digits c cross join @.digits d cross join @.digits e
where e.nr > 0
order by newid()

select * from #randomphonenumbers

drop table #randomphonenumbers

Bye now :)

Insert query timing out

This questions pertains to the administration aspect.
I have a table with 7 million records. We have defined about 5 indexes
according to our
reporting needs.The queries are performing satisfactorily.But offlate,we
have observed
that our insert queries are timing out. we are trying to insert a record
into the table
three times with a time out of 15 seconds everytime.the insert query is
timing out even
after three times. we are trying to find if there is anything wrong with the
table
structure.The table has about 30 columns and is designed properly.
I have run dbcc show contig on the table and observed that there is external
fragmentation with the table according to the statistics.
Please let me if my observation is wrong based on the statistics.
DBCC SHOWCONTIG scanning 'xxxx' table...
Table: 'xxxx' (1767677345); index ID: 1, database ID: 24 TABLE
level scan performed.
- Pages Scanned........................: 665043
- Extents Scanned.......................: 84073
- Extent Switches.......................: 181138
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 45.89% [83131:18113
9]
- Logical Scan Fragmentation ..............: 14.47%
- Extent Scan Fragmentation ...............: 24.48%
- Avg. Bytes Free per Page................: 1130.5
- Avg. Page Density (full)................: 86.03%
there is no fill factor defined on the clustered index.
i have run dbcc reindex with no fill factor but it did not any good w.r.t
insert query
time out.
Do we need to change the fill factor from o to 80?
another question,why are we having external fragmentation on the table?
and what can be done to stop external fragmentation on the table?
Does backup play any role in the table fragmentation?
please advise us on what can be done to avoid insert query time out?
every month we see about 4 million records in this table.Hi
You don't give the DDL for the table and indexes which would be very useful
information to have when answering this question. You also don't say what
updates/deletes occur on this table, or where you are inserting the new data
.
Have you checked for blocking?
John
"Deepak" wrote:

> This questions pertains to the administration aspect.
> I have a table with 7 million records. We have defined about 5 indexes
> according to our
> reporting needs.The queries are performing satisfactorily.But offlate,we
> have observed
> that our insert queries are timing out. we are trying to insert a record
> into the table
> three times with a time out of 15 seconds everytime.the insert query is
> timing out even
> after three times. we are trying to find if there is anything wrong with t
he
> table
> structure.The table has about 30 columns and is designed properly.
> I have run dbcc show contig on the table and observed that there is extern
al
> fragmentation with the table according to the statistics.
> Please let me if my observation is wrong based on the statistics.
> DBCC SHOWCONTIG scanning 'xxxx' table...
> Table: 'xxxx' (1767677345); index ID: 1, database ID: 24 TABLE
> level scan performed.
> - Pages Scanned........................: 665043
> - Extents Scanned.......................: 84073
> - Extent Switches.......................: 181138
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 45.89% [83131:181
139]
> - Logical Scan Fragmentation ..............: 14.47%
> - Extent Scan Fragmentation ...............: 24.48%
> - Avg. Bytes Free per Page................: 1130.5
> - Avg. Page Density (full)................: 86.03%
> there is no fill factor defined on the clustered index.
> i have run dbcc reindex with no fill factor but it did not any good w.r.t
> insert query
> time out.
> Do we need to change the fill factor from o to 80?
> another question,why are we having external fragmentation on the table?
> and what can be done to stop external fragmentation on the table?
> Does backup play any role in the table fragmentation?
> please advise us on what can be done to avoid insert query time out?
> every month we see about 4 million records in this table.
>|||Hello John
Thanks for replying. I have checked for blocking and there are no blocks.I
have run sql profiler and have not observed any locks during the time period
on this table .
There are only updates and inserts. there are no delete operations on this
table.
Updates are very minimum. inserts happen every other second during the peak
times.we have about 4 million transactions per month on this table. the issu
e
is only on inserting . do you believe that there is external fragmentation o
n
this table?
what is your take on changing the fill factor for the clustered index? this
is a normal table with 30 or more columns. the issue is only with inserts an
d
not with select queries.
Please advise us.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't give the DDL for the table and indexes which would be very usefu
l
> information to have when answering this question. You also don't say what
> updates/deletes occur on this table, or where you are inserting the new da
ta.
> Have you checked for blocking?
> John
>
> "Deepak" wrote:
>|||"Deepak" <Deepak@.discussions.microsoft.com> wrote in message
news:28B684B3-DE3F-44BF-AEF5-39512DEAEBBD@.microsoft.com...
> Hello John
> Thanks for replying. I have checked for blocking and there are no blocks.I
> have run sql profiler and have not observed any locks during the time
> period
> on this table .
> There are only updates and inserts. there are no delete operations on this
> table.
> Updates are very minimum. inserts happen every other second during the
> peak
> times.we have about 4 million transactions per month on this table. the
> issue
> is only on inserting . do you believe that there is external fragmentation
> on
> this table?
> what is your take on changing the fill factor for the clustered index?
> this
> is a normal table with 30 or more columns. the issue is only with inserts
> and
> not with select queries.
What do you mean by "timeout". SQL Server doesn't time-out queries, client
programs do. So what's the client program and how long is the timeout?
What else is going on? Are you very, very sure there's no blocking. This
sounds very much like a blocking problem.
The measures you propose might decrease transaction times slightly, but it's
unlikely that they will resolve your timeout issue.
David|||Hello
The client program is a vb component. the time out is 15 seconds. I try the
insert query three times . I am positive that there is no blocking.
thanks
Deepak
"David Browne" wrote:

> "Deepak" <Deepak@.discussions.microsoft.com> wrote in message
> news:28B684B3-DE3F-44BF-AEF5-39512DEAEBBD@.microsoft.com...
> What do you mean by "timeout". SQL Server doesn't time-out queries, clien
t
> programs do. So what's the client program and how long is the timeout?
> What else is going on? Are you very, very sure there's no blocking. This
> sounds very much like a blocking problem.
> The measures you propose might decrease transaction times slightly, but it
's
> unlikely that they will resolve your timeout issue.
> David
>
>|||Hi Deepak
I am surprised you say there is no locking or blocking, the process that
inserts should take out locks. Have you looked at lock escalation, lock
timeouts and deadlocks in SQL profiler? You may also want to look at the
errors/warnings and log at statement level to show more information about an
y
triggers being executed. Also look at the output from sp_who2 when the
process is running. You may want to try sp_blocker_pss80 see
http://support.microsoft.com/kb/271509.
I don't think fragmentation is the issue, only one in 4 extents are not
contiguous and you still have the problem after doing a re-index. You don't
say when the DBCC SHOWCONTIG information was taken! You may want to try
dropping the indexes to see what effect that has.
Have you checked perfmon information regarding the number of read/writes and
their duration and current disc queue lengths? You should also look at CPU
and memory usage to see if there are bottle necks there. Check out articles
on http://www.sql-server-performance.com/ on how to detect hardware
bottlenecks.
Are your log and data files on different spindles? Also see if you can
separate your indexes onto their own spindles as well!
If the source of these inserts is a data file you may want to review if you
can delay them until a quiet period and use BULK INSERT or BCP to populate
the information. You may also wish to look at partitioning the table.
Make sure that you are not continually expanding the data and log files, if
you expansion is too often you may wish to increase how much they expands.
Also if you continually shrink the data/log files there may be fragmentation
of the files on disc, check with the windows defragmentation program to see
if this is the case.
Check that your transactions are not too long. Make sure you are not
dependent on user input for them to complete. DBCC OPENTRAN will show open
transactions.
John
"Deepak" wrote:
[vbcol=seagreen]
> Hello
> The client program is a vb component. the time out is 15 seconds. I try th
e
> insert query three times . I am positive that there is no blocking.
> thanks
> Deepak
> "David Browne" wrote:
>

Friday, March 30, 2012

INSERTING 20Mill Records into a Table with 100Mill Records..

inserting 100 records

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

Wednesday, March 28, 2012

inserted and deleted table

Insert/update/delete Transaction

Friday, March 23, 2012

Insert Triggers

Wednesday, March 21, 2012

Insert trigger calls .NET application

Monday, March 19, 2012

Insert stored procedure for related tables

Monday, March 12, 2012

Insert Statement Help

insert statement

Friday, March 9, 2012

Insert records while purging

Wednesday, March 7, 2012

Insert Records Using from Text box to SQL database

Insert records into unusual table

Insert records in multiple tables via store proc

INSERT Records in multiple tables

Insert Records from Foxpro tables to SQL Server tables

insert records dependent on values from other table - with a l

INSERT RECORD with PDF WOED files

insert random records..HELP!

Insert query timing out

insert or replace

Blog Archive

About Me