.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Imran Ghani
Post New Web Links

update and insert together

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
 
hi experts I have the table "@temp" that contian id. and another table "myTable" that contain id, counter the field of id in both tables is equal. but not exists all @temp.id in the myTable.id. i want to 1 - increment counter if @temp.id exist in the myTable (@temp.id = myTable.id) 2 - and if the id not exists in the myTable, then create new row for the id and set counter to 1 i know i should update using inner join for state one,but what about another one?. i looking for a fast and optimized solution. Do using the Cursor is good way or there is a better way? Best Regards. Morteza  


View Complete Post


More Related Resource Links

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

  
Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?



Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.



For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Insert/Update OLE Attachment(Binary datatype) using SQL server 2000

  
Hi All, In the same senario how we can Insert/Update OLE Attachment(Binary datatype)  from one source table to other destination table by using SQL Server 2000 not with C# fronend code. Appreciated if you can give fix on this regads, Divakar.

Allow or deny roles to update, insert and delete records.

  
Hello, I have two roles "Admin" and "Basic". I also have a listview on the web page. My goal is that to make "Admin" role has the highest privilege to deal with records such as "insert", "update" and "delete". For the role "Basic", it only can update the records. Is there any sample code for that? Thanks.  

update/insert parent child at the same time

  
Hi Al, Is there a way to create a contact form that takes the name. address and other info for the master record; Then, somehow, on the same page, allow the user to enter multiple contact info as chid records (i.e. phone, email, etc.) All this using the Objec DataSource or SQL DataSource (which ever is easy) and doesn't require writing too much code. Any reference material welcomed. Thanks, rod.

Possible to insert & update in formview?

  
I have a formview where DefaultMode="Insert". Underneath, I have a gridview that displays the information from the inserts. Is there a way to update the information as well as insert? Maybe there is a better way to do what I'm trying to do. Any help is appreciated. Thanks!

Possible to update and insert from formview?

  
I have a formview where DefaultMode="Insert". Underneath, I have a gridview that displays the information from the inserts. Is there a way to update the information as well as insert? Maybe there is a better way to do what I'm trying to do. Any help is appreciated. Thanks!

Concurrent INSERT and UPDATE

  
I created a table with the following columns in SQL server 2008 express version,   create table action (id int identity , comment varchar(256), description varchar(max), opendate date, closedate date) I was trying to verify whether inserts will slow down once the table has large number of records. Hence i started inserting in loop (30 times) 10000 records. This was done using Microsoft jdbc driver ver 4.0. The time taken was almost in the range 8-10 seconds. In between i also tried to update the 'description' column for all the rows from sql client window. This affected the insert operations and it took 31 seconds for inserting one set of 10,000 rows. Please suggest how to handle such scenarios. Is the slowness because of the entire table update?

EntityDataSource Insert, Update, and ConvertEmptyStringToNull

  
I've noticed that when using an EntityDataSource with FormViews, GridViews, and other databound controls, that empty strings are not always converted to null for inserts and updates. This isn't an issue when using templated DynamicControls, but when normal WebControls are bound to the datasource. For example: <asp:TextBox ... Text='<%# Bind("NullableColumnName") %>'  During inserts and updates, when the text box has an empty string, the value saved to the table cell in the db is an empty string rather than NULL. This is avoidable if normal <asp:Parameter Name="NullableColumnName" Type="String" /> parameters are added to the InsertParameters and UpdateParameters collections, since the default property of ConvertEmptyStringToNull is true. Is there another way to ensure empty strings get converted to null, other than using DynamicControls with EDS? I'm just curious what is happening behind the scenes here. Data values of type Int32 are automatically converted to null when they are bound to an empty string, as are datetime values. It only seems to be normal strings that get pushed to the EDM without conversion to null. Any explanations?

MS Linq To SQL Samples - Insert/Update/Delete

  
Hi, I'm stumbling along trying my hand at Linq to SQL and have come across this code below that was provided by MS.http://msdn.microsoft.com/en-us/vbasic/bb737928.aspxThe problem is there is no ".add" method for Categories to chose in the statement db2.Categories.add(newCategory)I've seen similar videos here on ASP .NET that uses code very much like the example below, but I am unable to run this example connected to a Northwind SQL database using Web Developer 2010 Express. This is the One-to-Many example listed under Linq To SQL Samples - Insert/Update/Delete. Thanks for any help! Shawn Dim db2 = New DataClassesDataContext Dim ds = New DataLoadOptions() ds.LoadWith(Of Category)(Function(p) p.Products) db2.LoadOptions = ds Dim newCategory = New Category With {.CategoryName = "Widgets", _ .Description = "Widgets are the customer-facing analogues " & _ "to sprockets and cogs.", _ .Picture = Nothing} Dim newProduct = New Product With {.ProductName = "Blue Widget", _ .UnitPrice = 34.56#, _ .Category = newCategory, _

SQL Checking the table before insert or update

  
I need your help with a SQL Stored Procedure PLEASE!!!GOAL: I need to check TableA for the ItemID if the ItemID Is Not found Insert a new record. If the ItemID Is found Update existing record.***********************Below is Incorrect***********************Please HELP!!!!IF @ItemID is not null          Set NewRecord =  Select ItemID FROM ContentItems Where EXISTS  (select ItemID from ContentForm_Fields where ItemID=@ItemID)IF NewRecord is not null   Update ContentForm_FieldsELSE Insert ContentForm_Fields

Update, delete, insert

  
I have two databases.  When I am using MS Visual Web Developer, I can drag the table onto the webpage and it gives me check boxes to Insert, Update, Delete,... When I do the exact thing using the other database, it only has check boxes forpaging, sorting and selection.  What is the difference?  I seem to have all teh same permissions in both databases. How can I make the second database update, delete and insert?  

Insert-Update to SAP from SharePoint - Best Approach in terms of development and deployment

  
Hi, I am having a requirement where I need to design and develop a workflow solution that interacts with the SAP to insert/update the data. Here are few questions that I am looking for some guidance... 1) If I develop the Insert-update operations using BCS and a web-service (or WCF) in SharePoint designer, how to use it with workflow ? Also , the stuff that is configured using SharePoint designer , how to deploy it on Staging and Production? 2) If the above approach has quite a few limitations, then what should be the best approach for such requirement?   Thanks.  

Update, Delete, Insert Options

  
I have two databases.  When I am using MS Visual Web Developer, I can drag the table onto the webpage and it gives me check boxes to Insert, Update, Delete,... When I do the exact thing using the other database, it only has check boxes forpaging, sorting and selection.  What is the difference?  I seem to have all teh same permissions in both databases.

Trigger without UPdate,INSERT and DELETE

  

I have SQL SERVER 2005 and I have a table with a Cloumn DATETIME data Type,

I want to ceate a trigger that checks the table rows and delete the record (s) when it has (DATETIME colume value - today date()) >20  days.

Is it possible to write a trigger for that? 

I am new to SQL Server and trigger.

Thanks,

MK 

 


Insert/Update data to multiple tables on a single button click

  

Hi experts,

I need to insert/update data to 3 or more tables on a single button click.What is best method for achieving this?I am using mysql as my DB.

Thanks for your valuable reply in advance.


SQL Stored Procedure - if exists Update else Insert.

  

Hey,

I'm trying to create a stored procedure that get a Key and Name (both varchar), the procedure will check if a the key allready exists in the table, if it exists it will update the name, if its not exists it will add it to the table..

I have no clue how to use IF to check if the row exists, please help.


Thanks :)


Why deadlock occurs using INSERT/UPDATE in a table with 2 sessions

  

Hi,

I am working on a problem in my odbc application, which i have isolated to the MS SQL Server query analyzer.

The problem is related to the deadlock faced by one of the session, when 2 transactions each running from 2 sessions are trying to INSERT a record from their respective session with the duplicate values for the columns icol1 and cCol2 and then one of the session UPDATE the value of column icol1 in the previously inserted record. After the completion of the test, I see 1 record created in the table from one of the session, but other session becomes deadlock victim.

Following is the table structure:

CREATE TABLE [sgarg].[test_buf](

[icol1] [int] NULL DEFAULT ((0)),
[ccol2] [varchar](30) NULL,
[ccol3] [varchar](30) NULL,
[RECID] [bigint] NULL
) ON [PRIMARY]


Following are the index definitions

CREATE UNIQUE NONCLUSTERED INDEX [test_buf##ixprim] ON [sgarg].[test_buf]
(
 [icol1] ASC,
 [ccol2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [test_buf#_#recid] ON [sgarg].[test_buf]
(
 [RECID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPU

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend