how to bulk update a temp table with the sum of another query. for example
update @temp
Set total =
(
select sum(bedstays)
from @temp2
WHERE @temp2.ID = @temp.ID
)
i keep getting error in sub query on the @temp.ID saying i need to declare @temp and when i alias @temp "@temp t" i get an incorrect syntax near "t".
I select few columns from table A in dataset. Example "EMP_ID","Name","Address","Phone_Number". I want to save data in this dataset into some other table B.
I want to save data from dataset into table B. But i want to have following functionality
1. Before saving a record from dataset to table B if that record if previously present in table B, if yes then check if any value are different, if yes then update that record with the record from dataset, if all the values are same then leave the records as is.
2. If the record is not present in table B then insert it new records in table B from dataset.
Is there any efficient way to implement in .NET C#.
[code]...
This query works perfectly on the query analyser.
But when configuring the Table adapter ,I try executing the query and i get 0 rows affected.
What could I be getting wrong in this case.
NB:Existing GalleryID has been supplied.
I am doing this in my stored proc. Creating a temp table. Dumping the select results into the temp table and then select the info where the search string is based on the status name info.
because of the temp table, it is taking long to excecute. How can I get rid of the temp table and still seach based on the CurrentStatusName
Create proc [dbo].[usp_GetRequestsInfoOnSearchString]
(
@SearchString varchar(100)
)
as
Create TABLE #Request(
[RequestId] [int] NULL,
[RequestDate] [datetime] NULL,
[RequestText] [varchar](500) NULL,
[CurrentStatusId] [int] NULL,
[RiskLevelId] [int] NULL
)
INSERT into #Request
([RequestId] ,
[RequestDate] ,
[RequestText] ,
[CurrentStatusId] ,
[RiskLevelId] )
SELECT [RequestId]
,[RequestDate]
,[RequestText]
,CurrentStatusName = dbo.udf_GetRequestStatusName(CurrentStatusId)
,[RiskLevelId]
FROM [dbo].[Request]
select [RequestId] as [Request Id]
,[RequestDate] as [Request Date]
,[RequestText] as [Request Text]
,[CurrentStatusName] as [Current Status Name]
from #Request
WHERE ([CurrentStatusName] like @SearchString OR len(@SearchString)= 0 OR @SearchString is null)
I can join with a regular SQL table fine but this won't work:
SELECT normalTbl.*, tmp.fld1 FROM normalTbl
LEFT JOIN #Temp tmp ON tmp.joinID = normalTbl.joinID
All tmp.fld1 values are shown as NULL even though fld1 contains valid values and the joinID matches the normalTbl (when I call SELECT * from #Temp). If I do the same exact thing with a regular table, fld1 shows the correct value. What am I doing wrong?
I am trying to update my table ARTICLES and it has a field COUNTER, Everytime a user enter this ARTICLES, the page_load event runs a LINQ to SQL query taking the QueryString as parameter, increasing the COUNTER field in 1.
[Code]....
i m using ##Temp Table in stored procedure..it gives this error There is already an object named '##people' in the database.in two codition1.in case of multisession2. if transaction drop in between even i drop Table in Last in stored procedure..what can i use in place of temp table to hold data for a time being
View 1 Repliesi want fetch data from temp table in sql server stored procedur like below code
[Code]....
primary key is of uniqueidentifier in dataTable
after a complex sql script I finally get temp table like this
No Invalid Port
1 AA,BB,CC,AE,CD,LA,SE,GE,XX,A2,A3...
2 PO,FD,SE,LE,SE,AQ,GB,NK,NA,OM,IA,JO,YA,LL,EN...
then I will use this temp table to join with another table to get the finally result like this
No Invalid Port Start No EndNo
1 AA,BB,CC,AE,CD,LA,SE,GE,XX,A2,A3... AA QO
2 PO,FD,SE,LE,SE,AQ,GB,NK,NA,OM,IA,JO,YA,LL,EN... BB QP
I have problem here.. the Invalid Port list will be a very very long string.. I want to insert symbol to this field (each 2 ports with one symbol)
[Code].....
I have setup a job in SQL Server Agent which run a store procedure.
[Code]....
After the job excuted, the temp table have not been created.
If I type "exec data_syn_ClearAllEposData". The temp table has been created.
Global Temp table cannot create in SQL SERVER AGENT?
I have one Store Procedure that generating report ..
For storing the data there , i used many temp. tables. There is many Select Query , little less insert and delete query.
Now if there is huge data around 1 lac in temp table my select query taking to much time and also may be insert and delete query
I added Primary key to all auto Increment Field in temp table. Also defined Clustered index on that primary key as unique Clustered index to improve the performance .
But there is not so much improvement in case of huge temp table.
Right now the whole Store Procedure is taking time to complete around 1.5 days or around 30 hours ..
So i want to increase the performance as much that it completes on nearly 3-4 hours.
how can i select data into temp table from another database in sql server 2000
View 2 RepliesHere is the sqldatasource config code: <asp:SqlDataSource ID="SqlDataSource1" runat="server"
View 1 Replies[code]....
Im trying to update my DB(DataBase) by using SQL UPDATE query ,but its not updating in the dataBase i receive confirmation(in testLabel) that one row is affected(dataReader = query.ExecuteReader(); return numbers of rows affected)...
I have given a HTML editortext control on a page,which generates HTML (i have to store it in my DB,that page is only for Admin) ,on pressing Update button , im receving in my testLabel that one row is affected(which shows DB is updated succesfully) but when i check my DB its in old state,it is not updating...
Here is my Event handler of Update Button which have to make updates in DB:
[Code]....
[Code]....
I'm sure this has been answered many times already, but I still can't seem to find exactly how to search for this issue, so apologies in advance.
Anyway, I have a VB script for entering a row of data into a table by a web user. When this row of data is entered, I wish to take some of the values from the columns of the "primary" table, and have that data automatically create a new row of data in a "secondary" table. I think I'm part of the way there, here is my script:
[code]...
"ProductsByMfr" is the "primary" table - the table that the user adds data into when the button click event is fired.
"DealerPricing" is a "secondary" table that I wish to have some of the data from the newly-entered row in ProductsByMfr copied into. Am I even close here?
I'm trying to update one my my tables from a field in another table.
I have Address fields on both tables and the old table has some addresses I need to copy over (I dont want to copy/paste or type them all in obviously). I basically need to:
[code]...
How to update two tables in a single update query.
View 6 RepliesIs there any way to update multiple rows in a SQL query without using an foreach statement? In a single query.
View 3 RepliesI have select query like this :
[Code]....
This is my table stucture...
This is first table...
ItemNo refno Process Name Qty
001 1 A John 50
001 2 A Jon 150
002 3 B
Kalis 100
003 4 A
Bob 300
Second Table
ItemNo Newrefno Process Name Qty
001 001 ZZ peter 50
001 001/a ZkZ Joe 70
002 002 Ab Ray 100
Result Set
001 A John 200 001/a ZZ ZkZ peter joe 50 70
002 B
Kalis 100 002 Ab Null Ray Null 100 Null
003 A
Bob 300 null null null null null null null
Here first row of the result set explanation:
up to this 001 A John 200 based on first table sum...
and 001/a zz ZkZ peter joe 50 70
here 001/a newrefno of 001 so i need to show all the ItemNo 001 record in one row like the above resultset.
What is syntax for query string for updating an sql datetime field and an sql number field in vb.net ?
View 1 RepliesI have a form that gets populated by LINQ and I want to be able to update the database using that same form. The form is populated by grabbing a querystring (for example, "edit=10") and searching the primary key for that record. So far so good. I have a button attached to the function below which is supposed to use LINQ to update the database with data from the form. I get no errors, but for some reason, it is only updating one field (Last_Updated) instead of all of them.
[Code]....
I have a button click event that adds a record to a table based on the userid and threadid the user is viewing. I am a bit new to entity so I am stumbling around. I have to where conditions
ThreadID == threadid
UserId == userIdPosts
I have this query but do not know how to execute it and if I have it as is I get this in the yellow screen of death Both the theadid and userIdPosts have data in them.
var query = context.TrackLists.First(t => t.ThreadID == threadid);
context.TrackLists.First(t => t.UserId == userIdPosts);
Sequence contains no elements
Line 90: // find is user has already tracked this thread.
Line 91: var query = context.TrackLists.First(t => t.ThreadID == threadid);
Line 92: context.TrackLists.First(t => t.UserId == userIdPosts);
I am trying to do a lookup in a table with a value stored in an xml column.
I tried this several ways but I can't get it to work.
Here is some code:
[Code]....
I have this sqldatasource:
<asp:sqldatasource id="SqlDataSourceX" runat="server" connectionstring="<%$ ConnectionStrings:MyConn%>" providername="System.Data.SqlClient"
selectcommand="SELECT ItemNum, MIN(DateAdded) as [DateAdded]
FROM Items WITH (NOLOCK)
WHERE [DateAdded] between @DateFrom AND @DateTo
[Code]....
When I call select in codebehind, after 30sec it throws timeout expired SqlException:
[Code]....
again , this select took up to 3 sec to be executed. In application I tried to use SqlConnection and SqlTableAdapter instead of SqlDataSource, with the same query, and again it took up to 3 sec.
If I choose tighter interval (e.g '2011-01-20 00:00:00' - '2011-01-21 00:00:00') , there is no timeout exception, so I know that sqldatasource select is executed correctly. So the conclusion is that sql server is not using index while executing query sent from sqldatasource, but is using index whenever else. I have also checked the index statistics on sql server and it is like that. In profiler i can see slight difference between this two cases. As for SqlDataSource, in events table, in first column "EventClass" after 30 sec, there is this: "RPC:Completed" and application throws timeout. If i use SqlConnection and SqlTableAdapter then at the same place there is this: "SQL:BatchStarting" and "SQL:BatchCompleted" and application does not throws timeout(the index has been used).
what is so special on SqlDataSource connection, that Sql Server performs differently compared to other types of connections?