Need Query For Update And Insert In One Go.
Jun 13, 2006
Hi All,
Please look at the following tables. I have Two tables
Data and TmpData with the following structure.
Data ( All int columns, ID column is Primary and identity)
Id UserID PrgID RoldID
322112
323122
324132
325212
326222
327232
328312
329322
330332
TmpData
Id UserID PrgID RoldID
82113
83123
84133
85213
86223
87233
912012
922022
932032
942112
952122
962132
Now I need to run a query so that
Part 1: It updates existing RoleId columns ( Based on Userid,PrgID) in 'Data' Table with corresponding values from 'Data' table.
Part2 : It inserts new rows in 'TmpData' to 'Data' table.
I am done with Part1 using the simple update statement.
Update Data
Set Data.programroleid=tmp.ProgramRoleID
from TmpData tmp
where Data.userid=tmp.UserId
and Data.programId=tmp.ProgramId
This works fine for existing userids,programids in 'Data' and 'TmpData' tables. But I am struggling with inserting new rows into Data from 'TmpData' ( That exists only in 'tmpData' table). Based on above table structures how do I insert new data into 'Data' table from 'TmpData'.
View 2 Replies
ADVERTISEMENT
Mar 1, 2007
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
View 2 Replies
View Related
Mar 9, 2008
Hi, I have the following query:UPDATE lista SET audio='3847f5e9-4ef7-42d7-9e57-e5cbad9131b1.jpg' WHERE id='13';If the id already exists it'll modify the row correctly as expected. But if the id doesn't exist at the table I want the row to be inserted anyway, but this is not happening. The same query works well in mysql + php. The table has an identity increment of 1 for the primary key also. Any ideas?Thanks in advance.
View 1 Replies
View Related
Jul 12, 2005
I'm trying to update (increment)
Company.SumtotalLogons
from CompanyUsers.NumberOfLogons
where CompanyUsers.CompanyID = Company.CompanyID
I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons
I know this is possible in access, so i'm wondering how to go about it in ms-sql?
any ideas?
View 1 Replies
View Related
Apr 16, 2015
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
View 9 Replies
View Related
Nov 14, 2007
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.
View 6 Replies
View Related
Oct 23, 2014
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.
View 1 Replies
View Related
Feb 15, 2008
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
View 14 Replies
View Related
Jul 23, 2005
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.
View 4 Replies
View Related
Apr 11, 2008
Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
View 3 Replies
View Related
Mar 26, 2007
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';
View 2 Replies
View Related
Jul 7, 2006
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
Thanks
View 2 Replies
View Related
Dec 8, 2006
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.
View 1 Replies
View Related
Feb 2, 2008
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?
View 7 Replies
View Related
Jul 15, 2005
greetings
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?
Cheers
View 7 Replies
View Related
Nov 24, 2005
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
View 1 Replies
View Related
Jan 6, 2006
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.
if DTS wont do this, what are my options?
TIA
View 2 Replies
View Related
Aug 22, 2004
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
SQL = SQL & "where MailingListID =" & Session("EID")
set mData = oConn.execute(SQL)
if strFSQL > " " then
SQL1 = "Insert into 1985ClassList " & strFSQL & "VALUES " & strVSQL
end if
set mData = oConn.execute(SQL1)
It works fine for the UPDATE, but not for the INSERT.
View 9 Replies
View Related
Sep 1, 2004
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??
Thanks,
Karthik
View 2 Replies
View Related
Dec 6, 2004
Is there a way to insert / update in SQL Server 2000 with one command..
I know with Oracle there is a merge command....
View 1 Replies
View Related
Feb 19, 2004
I need a query to make an insert or and update in the same query
thanks
View 6 Replies
View Related
Jun 4, 2008
Scenario :
MS SQL 2005
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
Thanks
JG
View 2 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
May 22, 2008
Hi Team,
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.
View 3 Replies
View Related
Apr 30, 2015
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.
View 2 Replies
View Related
Sep 15, 2001
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this:
KeyField
Value1
Value2
Value3
The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
View 1 Replies
View Related
Jul 20, 2005
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Sep 28, 2006
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
View 1 Replies
View Related
May 1, 2007
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
View 5 Replies
View Related
Aug 23, 2007
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.
View 8 Replies
View Related
Dec 7, 2007
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.
View 9 Replies
View Related
Dec 13, 2007
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
View 3 Replies
View Related
Apr 14, 2008
Hi,
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.
Thanks
View 1 Replies
View Related
