The Three Methods Of Update MSSQL Table, Which Is The Best?

Mar 8, 2006

In order to update a MSSQL table, named [Table_1]. The procedure is:

(
@id int,
@Field0 nvarchar(1000) = NULL,
@Field1 nvarchar(1000) = NULL,
... ...
@Field9 nvarchar(1000) = NULL
)
Method 1:

UPDATE
    Table_1
SET
    Field0 =
        CASE
            WHEN @Field0 IS NULL THEN Field0
            ELSE @Field0
        END,
    Field1 =
        CASE
            WHEN @Field1 IS NULL THEN Field1
            ELSE @Field1
        END,
    ... ...
    Field9 =
        CASE
            WHEN @Field9 IS NULL THEN Field9
            ELSE @Field9
        END
WHERE
    id = @id

Method 2

IF @Field0 IS NOT NULL
    UPDATE
        Table_1
    SET
        Field0 = @Field0
    WHERE
        id = @id
    ... ...
    UPDATE
        Table_1
    SET
        Field9 = @Field9
    WHERE
        id = @id

Method 3:

DECLARE @SQL nvarchar(max)
DECLARE @NeedComma bit
SET @SQL = 'UPDATE Table_1 SET '
SET @NeedComma = 0
IF @Field0 IS NOT NULL
    BEGIN
        IF @NeedComma = 1
            SET @SQL = @SQL + ', '
        SET @SQL = @SQL + 'Field0 = @Field0'
        SET @NeedComma = 1
    END
... ...
IF @Field9 IS NOT NULL
    BEGIN
        IF @NeedComma = 1
            SET @SQL = @SQL + ', '
        SET @SQL = @SQL + 'Field9 = @Field9'
        SET @NeedComma = 1
    END
SET @SQL = @SQL + ' WHERE id = @id'
EXEC sp_executeSQL @SQL, N'@id int,@Field0 nvarchar(1000), ... ... , @Field9 nvarchar(1000)', @id = @id, @Field0 = @Field0, ... ... , @Field9 = @Field9

 

Which is the best? Thanks!

View 3 Replies


ADVERTISEMENT

Update A Dataset From An Updated MSSQL Table

May 23, 2007

Here is my problem: I've created a table in MSSQL named MyReferences and then a typed dataset to connect to it.



Then I realized that I needed to add a column to MyReferences table and then I did it.



The problem is that I cannot find a way to add the new column to the typed dataset. I am unable to view it in the available columns.



Can anyone help me?



I'm using Visual Studio 2005 sp1 and C#.



Best regards



Alessandro

View 1 Replies View Related

Task Upgrades, Update And CanUpdate Methods.. Why?

Sep 18, 2005

I am writing a new version of a task with and additional property, and wish to support upgrades between the versions. I have implemented the CanUpdate and Update methods, and update the Xml for the new property. The problem I then have it LoadFromXml gets called which promptly fails since it is still using the old version of the Xml. Have I misunderstood the point of the Update mechanism? It would seem to me that I€™d better off ignoring Update and implementing the checks defaulting my new property within LoadXml. The new version of SaveToXml would sort out the upgrade when saved again.

View 3 Replies View Related

SQL Table Lookup Methods

Aug 29, 2006

I am looking for a good method to do table lookups in a 2003 SQL database. I am retrieving products from an item table and then want to do value lookups in related tables in the same database like category name, category type, brand name etc. These values are referenced in the item master file by guid links to related tables.I am using a datareader to loop through the Select records and then I am writing results  to a tab delimited data file for a data conversion.  I am limited to net 1.1 and so can't use the 2.0 executescalar for single lookups.What would be the more efficienct method to use?'Dim myItemData As New DataReader Dim myItemReader As SqlClient.SqlDataReaderDim myItemCommand As New SqlClient.SqlCommand Dim myConnection = New SqlClient.SqlConnection("server=sql01.xxx") myItemCommand.Connection = myConnectionmyItemCommand.CommandText = "SELECT ""mf_items"".""item_guid"", ""mf_items"".""item_description"", ""mf_items"".""item_wholesale_price"", ""mf_items"".""item_description_title"",""mf_items"".""item_cd"",""mf_items"".""category_guid"" FROM ""CDB006"".""dbo"".""mf_items"" WHERE ""mf_items"".""item_closed"" = 0 And ""mf_items"".""item_visible"" = 1 AND ""mf_items"".""item_available"" = 1"myItemCommand.Connection.Open()myItemReader = myItemCommand.ExecuteReader()Dim myfileout As StreamWritermyfileout = File.CreateText(Server.MapPath("out.txt"))myfileout.WriteLine("link" & vbTab & "title" & vbTab & "description" & vbTab & "price" & vbTab & "image_link" & vbTab & "category" & vbTab & "id")While myItemReader.ReadIf myItemReader.Item(4).ToString() > "" And Val(myItemReader.Item(2).ToString) > 0 Then'what method to to look up for example the category name using the category_quid referenced in the item master'Write out record for parts file 

View 2 Replies View Related

ASP Update Method Not Working After A MSDE To MSSQL 2005 Expess Update

Oct 20, 2006

The Folowing code is not working anymore. (500 error)

Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close

The .execute Method works fine

strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1

W2003 + IIS6.0

Pls advice?

View 1 Replies View Related

Update Date In Mssql

Sep 28, 2005

hi.
i am working with PHP and MSSQL (i know its not a smart move, but I don't have a choice).
I got almost everything working correctly except the date format.

I can select the date and display it the way I want by doing this select convert(VARCHAR(10), date, 105) as new_date FROM table.

But when I have a field to update the date I am having trouble.

How do I update the MSSQL database with a new date if the date displayed is DD-MM-YY

View 2 Replies View Related

Wrong Datetime When Update Using MSSQL

Dec 11, 2005

Dim ldDatetime As DateTime = Now() '11/12/2005 13:05:09
my command text is:
"Insert Into WebSiteThread (TransactionDate) Values ( " & " ' " & ldDatetime & " ' " & ") "
The result I got was "12/11/3091 13:05:09" Not "11/12/2005 13:05:09"
What's wrong please!!
 

View 6 Replies View Related

ACCESS To MSSQL Update Query

Oct 24, 2005

I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.

Thank you

"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"

View 8 Replies View Related

Update MSSQL Database From .MDX Files

Sep 7, 2004

Hello guys,

Here is the case. I have up and running database on MSSQL server. My client deliver me updates. Since now they were as INSERT statements, but they decided to change some stuff at their side. Now the updates will come as pairs of files (.dbf and .mdx) for each table...

How I can insert them in the running database?

View 1 Replies View Related

Update Rows From MSSQL To MySQL

Feb 7, 2004

Is there any way where I can add the MySQL Server as a Linked server to MS SQL server?
Is there any way where I can update a table in MySQL server based because a row was updated in MS SQL server using a trigger?

Thanks,
Amit

View 1 Replies View Related

How To Update And Concatentate A String In MSSQL

Sep 30, 2006

Hi all,I have a
problem here where I am trying to use SQL Parameters to update a column
in the database, but the problem is that I need to concatenate the same
column to some Text from an Application

This is the Code I Have, But it throws an exception

UPDATE rdm_comments SET commentinfo = commentinfo + 'additional info' where commentid=2356

I get an Error stating that i cannot use the addition operator for a string. Help

View 8 Replies View Related

Access To MSSql Update Or Insert

Oct 31, 2007

Hi im new to SSIS even i did some basice things in SSIS. Now i run in to the problem, I hawe a access file with arount one milion records and i won to transfare this records in Ms SqlServer . But befora i transfare that i nead to check if that record exsist by ID if exsist i must do update else i must do insert.
Can some one Help me how can i do it..
THX
Sorry for my bad englisht

View 3 Replies View Related

Intallation Update On MSSQL 2000 SP4 Failed

Dec 23, 2007

I'll installing update http://support.microsoft.com/?kbid=899761 to correct memory usage. When installation process started I recieved this error - "An error in updating your system is occured." and installation failed. What I must doing to correct this issue?

View 2 Replies View Related

Mssql 2005. How To Make Update Stored Procedure ?

May 1, 2008

Hi ~
I made simple stored procedure that is to update user information following as...
ALTER PROCEDURE UpdateUserProfile(  @user_id uniqueidentifier,  @user_firstname nvarchar(50),  @user_lastname nvarchar(50),  @user_birth nvarchar(20),   @user_gender nvarchar(20)  )
AS 
 UPDATE user_profile    SET         user_firstname = @user_firstname,     user_lastname = @user_lastname,     user_birth = @user_birth,     user_gender =  @user_gender  WHERE user_id = @user_id  RETURN
When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message.
What's the problem ? 
 

View 2 Replies View Related

Update One Colum With Other Column Value In Same Table Using Update Table Statement

Jun 14, 2007

Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani

View 3 Replies View Related

How To Convert Foxpro Table In To MSSQL Table

Dec 7, 2005

Hi
I am trying to convert foxpro database table example mytable.dbf in to MSSQL Database table. I have created odbc connection to foxpro database, Then i have created an data set in which i have fill the mytable (stucture and data) by odbc adapter. Now i want to import this Data Set in to the sql database. My table (stucture and data) is in memory and my proble is that how i can write to sql data set or create data table which i can see in MSSQL Database.
Please Suggest
Thanks in Advance.
 
 

View 4 Replies View Related

How To Correctly Update A Table Which Values Can Be Either Inserted/updated/deleted On Update?

Feb 16, 2006

Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza

View 8 Replies View Related

Create Table In MSSQL

Apr 14, 2000

Hi anyone can tell me how to create a table from an existing table in MSSQL.
In oracle we issue " create table <tablea> as select * from <tableb>; " ?
Thanks in Advance.

View 3 Replies View Related

Optimizing Table In Mssql

Jun 21, 2007

Hi there,
Is there an optimize table command in mssql which will work the same way as "OPTIMIZE TABLE tablename" of mysql?

I have a php application that should work on both mysql and mssql. To do defragmentation, I am using the above command. Is there an equivalent in mssql?

Cheers,
Celia

View 1 Replies View Related

Plz Help...update Value In Multiple Db Table Using Single 'update Command'

Mar 18, 2005

hi,friends

we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------

i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.

i want to update value in multiple database table(more than one) using single 'update command'

i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.

it's urgent.

thanks in advance.

View 2 Replies View Related

Transact SQL :: Firing After Update Trigger - On Table Row Update

Jul 8, 2015

I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
 
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),

I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.

I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?

View 7 Replies View Related

Pivoting A Results Table MSSQL

Nov 12, 2007

I have a query in which I would like to pivot the resultsI presently have my results displaying something like this. OrderNumber    Product       OrderQuantity---------------        ---------------    ----------------------0608                Prod1          30608                Prod2          120608                Prod3          2 What I am after is for the results to display something like this.OrderNumber    Prod1   Prod2   Prod3 
---------------        ---------   ---------  ---------
0608                3           12       2 This is using SQL Server ver 8.0  

View 3 Replies View Related

Iterating Over Table Rows In MSSQL

Sep 23, 2004

Hello all,

I have recently started working on a project which involves using MSSQL to access a simple database. I have worked with Postgres SQL before, so I have a general idea of what SQL can be used for, but I'm having some difficulties applying that knowledge to MSSQL.

Currently, I would like to do the following (in abstract terms):

declare tmp record
select column1 from tableA into tmp
for each entry from above selection do
insert into tableB values (tmp[column1], 0, 0, 0)

I remember doing something like this fairly easily in postgres. Trying to put that into MSSQL, I have:

CREATE FUNCTION dbo.newDay (@mDate datetime)
RETURNS int
AS
BEGIN
DECLARE @id int
DECLARE item_cursor CURSOR FOR
SELECT id FROM tblKitchenCat
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO tblKitchenList VALUES (@id, 0, 0, 0, 0, 0, @mDate)
FETCH NEXT FROM item_cursor INTO @id
END
CLOSE item_cursor
DEALLOCATE item_cursor
RETURN 0
END
GO

I get a syntax error next to AS... what is it?

Can somebody please help me out here... any articles related to moving to MSSQL from Postgres would also be highly appreciated.

In addition to that, I would like to schedule a particular function to run once a day, say at 2am. Is there a way to do this using MSSQL?

Thanks in advance.
Cheers,
Michael

View 3 Replies View Related

MSSQL Table Joining Issue

Jun 8, 2007

Hello all,

I am Using MSSQL and ASP Classic for this project. Let me explain my problem, i have two tables:

Users
ID
Username

ChangeLog
ID
Description
UserID
DateChanged

The Users table will contain one record for each user in the system.

The ChangeLog table can contain any number (including 0) of rows relating to each user, when a change to the users account is made, and entry is put into the ChangeLog table.

The object of my report is to display a list of all users in the system and display the date and description of the last change made to the user.

So far i have written this:


Code:


SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged
FROM Users
LEFT OUTER JOIN ChangeLog ON (ChangeLog.UserID = Users.ID)
ORDER BY ChangeLog.DateChanged DESC



However, if a user has got more than one change against it then a row will be returned for each change instead of just showing the latest change. So i then tried this:


Code:


SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged
FROM ChangeLog
LEFT OUTER JOIN Users ON (Users.ID = ChangeLog.UserID)
ORDER BY ChangeLog.DateChanged DESC



The problem with this one? It only shows users that have actually had a change made to them, and the report *needs* to display ALL users regardless of wether they have been changed or not.

I'd appretiate any help you can give me at all, thanks in advance!

View 5 Replies View Related

Mssql 2005 Table Transfer

Jun 23, 2008

Hello,

In MSSQL2000, I am able to transfer files from one db to other.
using addTask -> export data.

In MSSQL2005, I am not able to transfer files from one db to other.

Please advise.

Thanking You.
Regards,
Edward

View 16 Replies View Related

How Do I Set Up A Linked Table In MSSQL 2005?

Sep 28, 2006

I am new to SQL Server.

I have a fox pro database that is part of a vendor's product. I want to link some of the individual tables in the foxpro database in my SQL database.

Can someone point me to a tutorial or example on how to do this?



Thanks!

View 4 Replies View Related

SQL Connection Methods

Jun 5, 2007

Set myConn1 = Server.CreateObject("ADODB.Connection")
 Set myRs1 = Server.CreateObject("ADODB.Recordset")
 
Can anybody tell me what the ADODB in these two statements is telling me?

View 3 Replies View Related

UDT For Division Methods

Sep 4, 2007

Hi,

I plan to implement UDT for division methods.


The following fragment TSQL with zero check:


DECLARE @a int, @b int


SELECT



CASE WHEN @b = 0 THEN NULL

ELSE @a / @b
END



Clr UDT may look like this and script will be shorter.


DECLARE @a int, @b int


SELECT @a Type :: divide(@b)







Is that better to use UDT ?

Anyone have try this before?

View 8 Replies View Related

Methods Within An Assembly

Apr 4, 2007

Is there a way to retrieve the methods within an assembly that are attributed with SqlTrigger or SqlProcedure using T-SQL?

View 1 Replies View Related

Replication Methods

Aug 31, 2007

Hi,

I'm a newbee to replication and wanted to know about the whole mechanism of replication and how is two way replication implemnted on 2 diffrent servers located at 2 different remote locations????

View 2 Replies View Related

MSSQL Dbo Table Duplicates On Host Server

Apr 26, 2005

(i am not sure which forum to post this too)
Hi, I imported a table onto my host's server, table_Login which has username password, and userID. userID is Primary Key & identity.
When I had my code do insert of new user, I was getting error the Login.userID doesn't allow null values. Which shouldn't have mattered because userID is an identity.
After making a diagram from Enterprise Manager, I noticed that I have two Login tables (although only one shows in Enterprise Manager's "Table" node view. The second table, which had none of the constraints that my table of the same name which is listed under the "Table" node does; appears to be the table which is being written to by my code.
I can't find this table, Login(dbo), through Enterprise Manager, nor am i sure how to access it through Query Analyzer. Needless to say, I don't like this arrangement. and well tech support for this host seems to leave a lot to be desired.
Anyone know what needs to be done so that I can fix it or fight with tech support to convince them that they should?

View 2 Replies View Related

SQL Script To Show The Table Definition In MSSQL

Dec 7, 2005

Just like "describe select(*) from table_name" in DB2?

Thanks!

View 6 Replies View Related

MSSQL 2000: How Can We Track Table/sp/function Changes?

May 19, 2006

My company would like to start keeping track of everytime a table, stored procedure, or function is changed.

What is the best way to do this?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved