I am building an e-commerce site for a college project and in my database design I am using autonumbers/Identity for primary keys for things like order numbers.
I am using SQL Server 2005 and have the keys as int's and with identitys that start and 1 and increment by one. I know that when I do an insert the table will automatically put a number in the id field.
My question is if I have to do an insert on multiple tables, lets say and order table and order history table. How do I get the generated id number into the foriegn key on the other table? Is there someway to get SQL server to do it for you?
I have setup the relationships and stuff for the server. If you need a database diagragm to explain what I mean let me know and ill put one up.
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end) scenario In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table. I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
Hi, Can anyone explain what the difference is and the advantages or disadvantages of using the below statements in my SQL paramaters please. Is there a performance hit if I use the second option? If myCustomer.Phone2 IsNot Nothing Then versus If myCustomer.Phone2.Length > 0 Then
Is there an easy way with DTS to pump data from one table to another so that it will update the row if it exists (the source and destination have the same value for the ID colum) or insert it if it doesn't. I know this can be done with stored procedures/sql by doing IF EXISTS UPDATE ELSE INSERT but there are many tables and columns and this will be very tiime consuming.
I have a page where the user can update stock records. it has 5 x 5 text boxes. If the user has already entered stock before that stock will show up and they can change it and clicking the button it will update, however if they have just entered new data i would assume they would need to insert it, so how do i go about doing this? do i need to use both insert and update in the same sql string?
I am developing an application for the marketing dept at my company. Basically users can build the content of an email to be sent to our subscriber database.
I am wanting the application to initailly save the content into a database, the update the most recently inserted row.
The save button uses the following SQL command: Dim SqlMethod As String = "INSERT INTO CZC_email (Offer, SendDate, Destinations, Copy, BannerURL) VALUES ('" & txtCampaignName.Text & "','" & calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "','" & DestinationsSelected & "','" & FreeTextBox2.Text & "', '" & txtBannerPath.Text & "')SELECT @@IDENTITY AS 'CZ_ID'"
And my update button has this SQL command:
Dim SqlMethod As String = "UPDATE CZC_email SET SendDate = '" & calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "', Offer = '" & txtCampaignName.Text & "',Destinations = '" & DestinationsSelected & "', BannerURL = '" & txtBannerPath.Text & "' WHERE CZ_ID = @@IDENTITY "
but it doesnt seem to be updating. anyone know what I'm doing wrong?
I have a GridView on a page, It contains data from 2 joined tables and a command column to Edit/Update. I want to update the data in only one of the tables. If the data exists in the table to be updated there seems to be no problem (obviously). But I get an error when trying to update a record that does not exist (I should think so). Is there a way of Inserting the record before the update is fired? I have tried to do an insert contained in a 'Try' in the GridView1_RowUpdating, but this does not seem to work. as I still get the same error. Object cannot be cast from DBNull to other types. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Object cannot be cast from DBNull to other types. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [InvalidCastException: Object cannot be cast from DBNull to other types.] System.DBNull.System.IConvertible.ToInt32(IFormatProvider provider) +54 System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +293 System.Web.UI.WebControls.SqlDataSourceView.AddParameters(DbCommand command, ParameterCollection reference, IDictionary parameters, IDictionary exclusionList, String oldValuesParameterFormatString) +577 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +400 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1173 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +1084 System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +88 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +117 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +83 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +136 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +172 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4839
Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating Try Dim eventid As Integer = CInt(GridView1.Rows(GridView1.EditIndex).Cells(7).ToString) Dim userid As Integer = CInt(hfID.Value) Dim guests As Integer = CInt(GridView1.Rows(GridView1.EditIndex).Cells(4).ToString) Dim attending As Boolean = CBool(GridView1.Rows(GridView1.EditIndex).Cells(5).ToString) Dim extra As String = Server.HtmlEncode(GridView1.Rows(GridView1.EditIndex).Cells(6).ToString) InsertRecord(eventid, userid, guests, attending, extra) Catch ex As Exception End Try End Sub
Function InsertRecord(ByVal eventID As Integer, ByVal userID As Integer, ByVal guests As Integer, ByVal attending As Boolean, ByVal extra As String) As Integer Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='AFRA'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [AFRAAttendance] ([EventID], [UserID], [Guests], [Attending], [Extra]) VALUES (@EventID, @UserID, @Guests, @Attending, @Extra)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) sqlCommand.Parameters.Add("@EventID", System.Data.SqlDbType.Int).Value = eventID sqlCommand.Parameters.Add("@UserID", System.Data.SqlDbType.Int).Value = userID sqlCommand.Parameters.Add("@Guests", System.Data.SqlDbType.Int).Value = guests sqlCommand.Parameters.Add("@Attending", System.Data.SqlDbType.Bit).Value = attending sqlCommand.Parameters.Add("@Extra", System.Data.SqlDbType.VarChar).Value = extra Dim rowsAffected As Integer = 0 sqlConnection.Open() Try rowsAffected = sqlCommand.ExecuteNonQuery Finally sqlConnection.Close() End Try Return rowsAffected End FunctionThank you
Is it possible to run a DTS package so that it just updates existing rows rather than a full on insert?
I have an HR db as a source and a health and safety db as destination. records of peoples H+S acheivments and assessments are maintained in the H+S db but i need to keep this topped up with the newly recruited staff details and the existing staff change of details. The DTS need to know to insert where its a new record and to update where its just a change of name or work location etc.
I have a form that pulls existing information from a database and allows users to edit it. Every field can be left blank if the user wishes. I am having an issue with determining which SQL statement to run. There has to be an easier way to do this then what I am doing. For each filed I process the data this way:
SQL2="select * from 1985ClassList where MailingListID =" & Session("EID") set aData = oConn.execute(SQL2)
SQL = "Update 1985ClassList SET "
if aData.Fields("FirstName") > " " and Request.QueryString("FirstName") > " " then SQL = SQL & "'FirstName' = '" & Request.QueryString("FirstName") & "', " end if
if aData.Fields("FirstName") = " " and Request.QueryString("FirstName") > " " then strFSQL = strFSQL & "FirstName, " strVSQL = strVSQL & Request.QueryString("FirstName") & ", " end if
Hi, Is it possible to Read data From a Table while Insertion/Update is happening on the table?? In case if this is not possible,Is there anyway to do that??
Tables: DETAIL --Here are all the detailed transactions Customer Item Amount ...MoreFields
TOTAL --Here are the accumulated values (1 record by Customer-Item Customer Item Accumulated ...MoreFields
What I want to know is the general logic to -Read all records from table DETAIL -If relationship Customer-Item exists in TOTAL table then UPDATE the TOTAL.Accumulated field adding the DETAIL.Amount field -If relationship Customer-Item doesn't exists in TOTAL table then INSERT a new ecord with the values from DETAIL table
Because of my experience in VB and ADO my first try is to use a Cursor and loop record by record
But I know that is not the only and better solution
May You post some hint or some sample querie on how could be done
I'm new to DTS. I read some docs before adventuring into this matter.I still haven't found in all the docs I read if there is some "built-in" DTStask or function or wathever, to do a mixed "insert/update" import from asource, giving a unique field as primary key.I'll try to be more specific. The problem I would like to solve is this:I have a source file (csv) but it could be any source. I must check thisfile for all the records and compare them with the ones in the db (giving aunique field as a way of checking), so that all the records that alreadyexist, are UPDATED, and the others are INSERTED.I guess this is one most common task to accomplish, when you have a localbased application that you regularly update and then you want to export thedata to another "slave" application without using replication. But even ifthis sounds to me like a common task, I found no "buil-in" function for thatin DTS. I read something about "lookups" but it don't know if it's relatedsomehow.. it wasn't very clean.Thanks in advance for suggestions.--:: Massimiliano Mattei:: Project Leader:: E.xtranet V.irtual A.pplication:: www.evagroup.net :: www.commy.biz
I would like to import a .csv file into my existing table. On the DTS, I would like to check if the record already there then updated, on the other hand, if the record wasn't there then add it. How can I do that using DTS. Please help and thanks.
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
I am having an issue with this SQL Statement i am trying to write. i want to insert the values (Test Facility) from CCID table INTO CCFD table where CCID.id = CCFD.id this is what i have.UPDATE CCFD.id SET TEST_FACILITY = (SELECT CCID.id.TEST_FACILITYFROM CCID.idWHERE CCID.id = CCFD.id)orINSERT INTO CCFD.id(Test_Facility)SELECT TEST_FACILITYFROM CCID.idWHERE (CCFD.id.INDEX_ID = CCID.id.INDEX_ID) thanks in advanced
Is there a way I can stop a form from inserting or updating a record when there is an error. I have an sql 2000 DB. I have noticed that if the db field can handle 50 characters and the form field has no limit on the number of characters no errors are displayed to the user if they try to use more than the 50 characters in the textbox. The record is not saved. None of the fields are saved. I do notice the autonumber generated is skipped by the db. That is, the next autonumber for a successful insert skips the logical next number. How do I capture this error, or any save error and return the user back to the form? Yes, I have limited the number of characters a user can type on the textbox now, but I would really like to catch save or insert errors. I use asp.net 2.0 and VB. I don't know C#. thanksMilton
Hi, Is it possible to group all my update/insert (one table) ? Cause I hope to have all the update/insert in one Trigger execution. I need to sum up some figures here. I am using ASP.NET 2.0, SQLOLEDB and SQL 2005. Please Help and Thank you.
What I am attempting to do here is check to see if a record exists in a table, if so, I will update some fields, if not, I will insert a new record into the table. This is what I have so far, I was hoping someone could let me know if any of these elements are unnecessary. SELECT [IP] FROM [Table]if (strIP == @IP){ UPDATE [Table] SET [Column = value++]} else{ INSERT INTO [Table] (IP) VALUES (@IP) } I obviously left out parameters and whatnot, I am mainly concerned with the logic here. My apologies if this is something simple.
Hi All, I have this project I'm working on it's Product Content Management System rewrite. I got to the point of updating the Product By Sku and not sure if I should use UPDATE statement or I should DELETE sections assosiated with the ProductContentID and then re-insert them again? I'm not sure which is more afficient? I can really do both and it's really not that complicated, the only problem I see with DELETE then INSERT is the ProductContentSectionId in the Sections table is going to grow very fast and I'm a bit concerned about it. We use SQL Server 2000 and we have about 4 bound tables where we keep the data. The one I'm talking about is the sections table where we keep the actual types of product content like a BoxShot, Description, Key Features and so on... Thank you in advance! Tatyana Hughes
I'm trying to update 2 tables in SQL (say 2 Costumers table). 1 Lists all costumers per location( so 1 costumer can be placed in multiple locations), while the other is by location with contact details. Is it possible for me to update both pages in 1 web update page?????
Is there any way to use a graphical designer to build your insert & update SQL statements in Enterprise manager? I mean Access has an EASY way to build them, surely SQL does too?
I would just build them in Access and copy the SQL, but then I'm stuck replacing all the "dbo_" with "dbo." and other little nuances.
I have a situation where currently, I check to see if a record exists and I do an update if it does, I do an INSERT if it does not. This happens about 200k times each night.
I'm trying to speed the application up that is doing this and I was wondering if it would be a better idea to just delete the records I'm about to recreate, before I start, then do an unconditional insert.
It would eliminate my IF EXISTS check but if an update is faster than an insert, it might even out.
Also, will SQL deal with the big delete and the data being recreated each night without too much bloating or fragmentation?
Hi,I have a a table that is primarily a linking table that contains values for categories indid int Indicator ID indtype int Indicator Type can be either 0 or 1catflagnum int Category Flag Number the number of the Category catflagvalue int Cat Flag Value The Value for that category this table can then be updated from a web form.The Question I have is that can I do this in one statement or do I have to do it one at a time i.e The Data set could look something like indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 1 'This value will change Catflagvalue = 1 'This value will change indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 2 'This value will change Catflagvalue = 3 'This value will change indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 3 'This value will change Catflagvalue = 1 'This value will change indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 4 'This value will change Catflagvalue = 5 'This value will change A further complication is that in the table an entry may not be in that table for the category so we would possibly have to check if the record exist or do an insertI am stumped on this one?
when a record is inserted into tNTS, I need the column seq_num updated with the value of the column id (it's the PK)can someone help me with my tigger:CREATE TRIGGER updateNewSeq ON dbo.tNTS FOR INSERTASupdate tNTS (set seq_num =tNTS )SELECT NTS_idFROM INSERTEDcheers
i want to make otomatic insert or update base on specific field value in a column, for example if i inserted a date at column clmdate then otomatically insert a text e.g. 'open' at clmOpen, how can i do that?
I would like to update a record in a table when a record is added. The dilema is I want to update the record I am adding. I tried an insert trigger ( it doesn't fail, it just doesn't update) If I use the same syntax in TSQL after the record has been inserted it handles the update properly. SO I think the syntax is OK. I have the trigger below. The question is - Is there a way to do this? Thanks
CREATE TRIGGER transaction_ins_tr ON dbo.transactions FOR INSERT AS DECLARE @pat_id varchar(5), @location_name varchar(50), @account_id char(12), @tran_num int --get patient id SELECT @pat_id = patient_id FROM inserted select @account_id = account_id from dbo.patient pa where pa.patient_id = @pat_id --get location select @location_name = df_sitename from development..patient_ dp where dp.account_id = @account_id -- update trans location update dbo.transactions set df_sitename = @location_name where tran_num = @tran_num
