Update On Basis Of Another Table
Apr 12, 2007
hello guys !
i need a little help in an update statement.
i have two tables as
table1(id1,name)
table2(id2,date,val2)
what i want is to set val2=45 in table2 where date=4/4/2007 (say) and id2, now id is to be obtained from table1 based on the name;
like
table1
---------
(1,"akmal")
(2,"peter")
(3,"kamal")
I know the name as peter, but i want to used its id1 (2) in the update statement. You might have got the idea that id2 is a foreign key referring to id1.
How to do that ?
View 2 Replies
ADVERTISEMENT
Sep 23, 2007
Hi All
Can any one solve my problem?
Requirements:
We have Our Local DataBase(SQL Server) Guess eg. DBLocal
We have a requirements to update this DBLocal Table Data with Other Database DBRemote(SQL Server) table data.
This Task is Schedule in a Week. Means We needs to update this DBLocal Table Every Week from DBRemote Data.
Both Side we have only one table. Means Source is One Table and Destination is Table One.
Right Now I have a connection string for DBRemote(SQL Server) and SQL Statement for getting Data from DBRemote(SQL Server).
can any one tell me what I need to do for achive this requirement?
please provide me link also; from there i can get enough information for my requirements.
Thanks in Advance
Regards
View 6 Replies
View Related
Sep 9, 2015
In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
View 4 Replies
View Related
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
Mar 21, 2008
How do I format a date value in SQL 2000 sp? Tired the following none are returning the quivilent rates. What is the syntax please?
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03/21/2008')
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '2008/03/2008')
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03-21-2008')
View 3 Replies
View Related
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
Apr 3, 2000
I have to import data into 2 tables on a daily basis.
The data is provided as a flatfile.
In order to fullfill this task the tables have to be truncated first.
Are there any possibilities to do this job automatically with dts, or do I
have to write an Interface
in VB or something like that?
Thanx
Michael F.
info@sunguard-explorers.de
View 2 Replies
View Related
Apr 11, 2008
Hi All
I am using following code for my application
SELECT
convert(varchar(10),acr._eventTime,120) [Date],
sum(case r.Severity WHEN 'Warning' THEN 1 end)[Warning],
isnull(sum(case r.Severity WHEN 'OK' THEN 1 end),0)[OK],
FROM ActiveCheckIssueResult r
GROUP BY convert(varchar(10),acr._eventTime,120)
it gives me Result as,
Date warning OK
--------- --------- -----
1/1/1008 1 1
2/1/2008 0 2
i want count on weekly basis
Could any one help me?
View 8 Replies
View Related
Dec 4, 2006
Hello friends!
I want to set my job agent to schedule for second basis but in EM i found by right clicking job in EDIT Recuring Job Schedule i m getting option in Hr and MM but not in SS so where i can set this in seconds (SS) or through QA????
T.I.A
View 2 Replies
View Related
Nov 5, 2007
Currently database1 and database2 are planned to be synchronized with the interval of 5 seconds!! ( dont know exact, but i select run continoulsly on subscriber screen )
This could cause serious performance issues as both applications will be live on difference servers.
how can i Synchronization should be performed on day basis or it should be modification basis to avoid any performance issue on live application
i am using merge replication
Local distributor
and subscriber on another server .
plz help me
its urgent
View 3 Replies
View Related
May 26, 2006
Hi all of you,
I'm just a newbie with XML. Now we're moving all our DTS to SSIS packages. Most of them are simply processes that takes a plain file and loading these data into Sql table or in inverse way. So that I wonder what role will play XML here.
I mean, are there big differences between store data as XML format and nvarchar?
That sort of stuff.
Thanks in advance for any input,
View 5 Replies
View Related
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
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
Mar 20, 2008
On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far. SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3], [column4], [column5] FROM [table] WHERE [column4] = 'condition4' AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate ", Conn) ; cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text)); cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text)); cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text)); I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help.
View 2 Replies
View Related
May 18, 2004
hi guys.
I have a datetime column in my SQL server database.. I need to select the value from the table by passing month and year only..
any suggestions..??
Thanks in advance..
:)
View 1 Replies
View Related
Aug 16, 2006
Hi All,
I have recently upgraded one of our servers using the in place upgrade method to SQL Server 2005 from SQL Server 2000.
We are currently having an issue with stored procedures as they appear to be running slowly and the only way to correct this issue is to recompile them on a daily basis.
Has any one else had a similar issue, any ideas what could be causing this to occur.
Thanks.
View 1 Replies
View Related
May 17, 2004
Hi all,
I currently have a series of stored procedures that capture stock prices on a daily basis, then summarize the results into a daily, and further, a weekly summary of the "index" of a group of stocks. The data is accumulated from a (to use a highly technical unit of measurement...) bunch-O-individual rows of data using aggregate functions such as AVG and SUM.
The problem is that I occasionally get a warning on such aggregate statements which is the common one complaining thusly: "Warning: Null value is eliminated by an aggregate or other SET operation"
I know where it comes from, and I know how to code to protect the aggregate from complaining (i.e., AVG(ISNULL(yadayada,0)) ) but I am interested in figuring out a way to REPORT the statement that contains null values. I can, of course, capture ERRORS in selects, but is the same mechanism used to capture these NULL warnings on my aggregate statements? I don't necessarily want to know which individual row is causing it, just want to "tag" somehow the statement that results in the warning so I can go back after the run and check into it (after capturing local "pointer" info at the time the offending aggregate is invoked).
The code I use to capture errors and trace information follows:
UPDATE PortfolioPerformance
SET PrevDate = @PrevDate,
DailyPerChg = GPP.DailyPerChg,
DailySumPriceChg = GPP.DailySumPriceChg,
SumCurrPrice = GPP.SumCurrPrice,
SumPrevPrice = GPP.SumPrevPrice,
StockCount = GPP.StockCnt,
AvgHighPriceRatio = GPP.AvgHighPriceRatio,
AvgLowPriceRatio = GPP.AvgLowPriceRatio,
Volume = GPP.Volume
FROMPortfolioPerformance PP (nolock), VIEW_Get_PortfolioPerformance GPP
WHERE PP.PortfolioID = GPP.PortfolioID AND
(PP.CreateDate = GPP.CreateDate AND
PP.CreateDate = @CreateDate) AND
PP.PrevDate IS NULL
SELECT @RowCount = LTRIM(STR(@@ROWCOUNT)) /* capture rowcount so @m_error select doesn't clobber it*/
SELECT @m_error = @@Error IF @m_error <> 0 GOTO ErrorHandler
SET @TraceMsg = 'Completed Daily Portfolio Performance calculations (updated ' + @RowCount + ' rows)'
EXECUTE [dbo].[tracelog] 1, 'Index', 'sp_Set_PortfolioPerformance', @TraceMsg
NOTE: the aggregation in the above code is performed in the view referenced as "GPP", but that's outside the realm of the question, I think, so I won't bore you with the details of that just yet.
So I think if I can capture the warning like I do the errors, I can accomplish what I want to accomplish. I haven't yet been able to find any guidance in the Books Online, so do any of you have any pointers?
Thanks!
Paul
View 2 Replies
View Related
Sep 27, 2013
I need to be able to restore a test db from a production db on a daily basis..
View 3 Replies
View Related
Sep 13, 2007
How would you solve this problem?
Would you use replication? SSIS? Schedule stored procs? A third party data tool?
We have about 50 databases located at offices around the world. There are several key tables which we need a central copy of at our Head Office.
We need only the new INSERTS or UPDATES for the day to be copied to head office.
The copying process should happen after business hours for that office.
Some cleaning of the data needs to happen before updating head office.
Our database is being installed at new offices every other month, so it's important to us that this be easy to add a new database into the schedule, ideally without needing a SQL DBA or Developer.
We want something robust, performant and easy to troubleshoot.
Kev
View 1 Replies
View Related
May 16, 2007
Hi,
When I execute the following statement:
Select * from table1;
On what basis, SQL server decides the sequence of these rows?
I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.
Thanks veyr much.
Regards,
Tim
View 5 Replies
View Related
May 7, 2012
I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.
View 2 Replies
View Related
May 12, 2014
I need to get results on quarterly basis, matching 2 quarters AUTOMATICALLY.
- As the new quarter starts, it needs to match the last quarter results.
SELECT DATEADD(mm, (QUARTER - 1) * 3, year_date) StartDate,
DATEADD(dd, -1, DATEADD(mm, QUARTER * 3, year_date)) EndDate,
QUARTER QuarterNo
[Code] ....
Here is my Query, I don't know whether I'm getting it right?
--Quarter 1
SELECTD.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail
INTO#Quarter1
FROMdbo.tblRPT_Spend AS A INNER JOIN
dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
[Code] ....
View 4 Replies
View Related
Jul 17, 2014
Following is my table structure
IDRowCount PagID
1448
2267
3297
4216
5405
6254
[Code] ....
PageId is currently set to 0
I have a user input, @IntNoOfRowsPerPage = 800 Means 800 rows per page. So following is the output I require.
AIDRowCountPageId
1448 1
2267 1
3297 2
4216 2
5405 3
6254 3
[Code] ....
The values of PageID are such that summation of RowCount for PageID is <= @IntNoOfRowsPerPage (i.e, 800)
If NTILE function can be used in such scenarios.
View 5 Replies
View Related
Feb 15, 2015
I have create Store procedure for all the Source and destination table for loading new and updated record.
For example I have below Store procedure
1-SP_Archive_using_merge_Fdoor
2-SP_Archive_using_merge_Fdoop
now I want to call all the sp on the basis of input like If filename is Fdoor then it shold fire the SP_Archive_using_merge_Fdoor , if file name is Fdoop then it shoilud fire the SP_Archive_using_merge_Fdoop like that .
below is the 2 sp .
--First SP
ALTER PROCEDURE [dbo].[SP_Archive_using_merge_Fdoor]
AS
BEGIN
SET NOCOUNT ON
DECLARE @Source_RowCount int
DECLARE @New_RowCount int
[code].....
View 1 Replies
View Related
Nov 2, 2014
Looking to create a query, as simple as possible, that allows me to compound returns on a rolling daily basis. So far this this have I have:
DECLARE @stock_returns TABLE
(
stock_code VARCHAR(10) NOT NULL,
date1 DATE NOT NULL,
daily_return NUMERIC(10, 2) NOT NULL
);
[Code] ....
But I´m not getting what I need. If you run the above select, the output should be:
stock_codedate1daily_returnLAGCompound_return
stock12014-07-080.00510 0.00000 0.0051000000
stock12014-07-090.00300 0.00510 0.0081153000
stock12014-07-100.00500 0.00300 0.0080150000
stock12014-07-110.00600 0.00500 0.0110300000
stock12014-07-120.00200 0.00600 0.0080120000
stock12014-07-130.00700 0.00200 0.0090140000
stock12014-07-140.00240 0.00700 0.0094168000
stock12014-07-150.00240 0.00240 0.0048057600
stock12014-07-160.00250 0.00240 0.0049060000
The problem is with this column:
(lag(daily_return, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return
The (daily_return + 1) portion should be the accumulated compound return. So it should be something like
(lag(ACCUMULATED_COMPOUND RETURN, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return
And the output should be:
Date1Daily returnLAGCompound Return
08/07/20140,00510,00000,0051
09/07/20140,00300,00510,0081
10/07/20140,00500,00300,0132
11/07/20140,00600,00500,0192
12/07/20140,00200,00600,0213
13/07/20140,00700,00200,0284
14/07/20140,00240,00700,0309
15/07/20140,00240,00240,0334
16/07/20140,00250,00240,0359
View 10 Replies
View Related
Oct 17, 2007
Hi All,
Anyone have any experience in connecting to a Basis database?
I want to build an SSIS package to ETL data out of a Basis database and into a sql server 2005 db.
I have downloaded the ODBC driver, but am not usre how to plug it in to SSIS to do teh extraction.
Also I am totally new to Basis...it is not an RDBMS at all but the literature says it supports SQL queries, so I am not sure about where to start.
Thanks,
Stephen
View 7 Replies
View Related
Feb 21, 2007
I've just spent the most frustrating 4 hours trying to install SP2.
I kept getting "unable to start SQL Server service....see BOL ...re starting the service manually" messages; with the only options being to Retry or Cancel. Retry resulted in the same message. Cancel resulted in all components except SQL (sqlexpress) being installed - therefore totally useless
No mater what I did i could start the SQLservice as it did'nt show in the list of Services; nor did the SQL EE programme show in the Add/Remove Programs...evenn though the other components did.
I deleted/removed everything that was istalled; cleaned up the Registry; and tried again...
..and again
..and again
FIVE install / re-install failures; numerous registry cleanups!!!!
I was at the point where it looked like i'd have to reformat my hard disk, and completely re-install XP, all aplications i.e. Office 2003 etc in order for the thing to work.
I gave it one more shot, but this time, during install
- selected all components except SDK
- I unchecked the Start SQL / BROWSER at startup
- Checked the Enable User Instances and Add to Administrators boxes
Lo and behold....it installed!!!
I'm still very worried as the registry is full of "duplicate" entries i.e. Blah blah blah and blah blah blah.1
But at least the thing is installed.
Would interested to hear if anyone has had similar or other problems with the SP2 install/upgrade
----------------------
For info:
I ran the install as Administrator
IIS not installed
Win XP Sp2 fully updated
Dell Inspiron XPS M1710 laptop with 2gb Ram; 2ghz Duo core processors
View 1 Replies
View Related
Oct 30, 2007
I would like to force only certain users on certain databases to use encrypted connections for accessing our 2005 SQL Server. I've found a clunky way to do it, via registering a login event notification and using the kill statement. However, that's ugly, and I don't think there is any guarantee that the event will always fire before an evil client could get off a few evil commands. Is this supported, or is the only supported feature the forceencryption option, which is server-wide?
View 2 Replies
View Related
Sep 21, 2007
Hi All,
I'm creating 15 reports based on one data source.
Once I created, I'm supposed to run this on daily and monthly basis. I'm going to use snapshot option in Report Manager. Is it fine?
Do I need to create a data base too?
Also when I change the target URL address in the report property box, can I deploy it to any other server? Do I have to change the settings of report configuration manager too, inorder to publish reports on another server(not in my local machine)
I really appriciate answers for these questions
Thanks
View 5 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
May 12, 2008
Hello All,i have 2 excel files which i want to upload to sql database. i know how to upload to the folder. my question how to i use these excel files and convert them into tables in sql database.i have to do this on a regular basis. is there anyway i can do this. the 2 files when converted to tables, i need to create relationships etc. any suggestions or references please. i appreciate it. Thanks
View 2 Replies
View Related
Mar 10, 2014
I want the count of orders of a particular table on weekly basis i.e if date given to me is 10/3/2014 then my output should be count of orders from date 10/3/2014 to 09/3/2014(one week) then count of orders from 2/3/2014 to 08/3/2014(another week) and then from 24/2/2014 to 01/3/2014(another week).....
View 5 Replies
View Related
Jan 9, 2015
Split function. I have records of multiple users, the last value of every record is a contact number (10 Digits- Numeric), I want a split function which can take the whole text and split the records on the basis of contact number.
In order words i want SQL to locate the contact number and move to the next record after that and so on till the end of the text.
create table
tbl_1
(txt varchar (max))
insert into tbl_1 values ('john asfasdf 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,
finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
[Code] .....
Output
john jimenez 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
jeffrey galione 57 allen dr wayne nj 15810 562_434_0710 na na 5,8/12 executive, technical sales and support good 8137 91630 8.25 126 eileen oneal 8258364083
[Code] ....
View 6 Replies
View Related
