Any Way To Find Out Which SP Is Updating Data In A Specific Table?
Sep 22, 2005
Can you create an UPDATE TRIGGER and use some type
of code to figure out which SP just updated the current table?
If not how can i achieve what i want?
I tried to run SQL Profiler and i don't understand why i can't
simply have the Profiler filter events only for the specific database id
and the table's object id i chose?
What am i doing wrong with SQL Profiler? I was testing this
through SQL EM. I had the filters chosen for a specific database id
and a specific table's object id, yet when i open another table SQL
Profiler captures that information too.
Thank you
View 2 Replies
ADVERTISEMENT
Jun 20, 2007
I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.
Thanks,
View 4 Replies
View Related
Jul 20, 2007
Hi,
DONT KNOW IF IT IS POSSIBLE.. PLEASE SUGGEST.
Currently working on a upload module where in the data from excel file is imported to the destination tables. Data in the excel sheet comes in phases. All excel sheet columns data don't come at first shot. The excel sheet's data is dumped into temporary tables which inturn is looped using cursor's and gets finally updated to the actual tables.
Now, the problem I am facing is how do I update columns of the actual table with the data (i.e NON NULL values) available in the temporary table without tampering the data allready present in actual table.
Ideally what required is, update the actual table column values with the corresponding columns of temporary table ONLY for Non NUll column values of temporary table.
Temporary and Destination tables have 85 columns each. I don't want to write 85 update queries.
The scenario which I am facing is given below with 2 columns as an example.
1. Table 1 :- tbl_source (Temporary Table) has two columns src_Col1 & src_Col2
2. Table 2 :- tbl_destination (Actual Table) has two columns dest_Col1 & des_Col2
Scenario -1
---------------
tbl_Source Sample Data (after excel import to the temporary table)
------------------------
src_Col1 src_Col2
------------------
50 NULL
tbl_Destination Sample Data
------------------------
dest_Col1 dest_Col2
------------------
50 NULL
Scenario -2
---------------
tbl_Source Sample Data
------------------------
src_Col1 src_Col2
------------------
NULL 100
tbl_Destination Sample Data
------------------------
dest_Col1 dest_Col2
------------------
50 100
One update query which handles both scenarios.
Thanking you in anticipation.
Regards
View 4 Replies
View Related
Sep 21, 2006
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
View 7 Replies
View Related
Feb 22, 2006
I need to copy the following columns from my Employee table in my Performance DB to my Employee table in my VacationRequest DB: CompanyID, FacilityID, EmployeeID, FirstName, LastName, [Password] = 'nippert', Role = 'Employee' I tried the advice on this website but to no avail:http://www.w3schools.com/sql/sql_select_into.asp
View 1 Replies
View Related
Sep 25, 2006
Is there a way to update a specific cell(s) in an excel? I have an excel with charts and graphs which use as data source a range of cells from another sheet within the same spreadsheet. Is there a way update a specific cell from within the sql using openrowset()... ?
thanks
View 1 Replies
View Related
Apr 5, 2007
In my datawarehouse fact table I have a column (revenue) that I want to populate based on the values of number of columns, for simplicity, say just 2 columns, 'productid' and 'affiliateid'.
I have a revenue lookup table, with those same 2 columns and the amount. So far so simple, but rather than have one row for every possible combination, I use 0 to mean default. For instance, all the affiliates have the same revenue value apart from a couple, so instead of 200 rows identical except for the affiliateid, I have one row with a '0' for the affiliateid and 4 rows with specific affiliateIDs where it differs from the default.
E.G.
AffiliateID, TypeID, Revenue
0, 1, £50
22, 1, £55
33, 1, £57
0,2,£60
22, 2, £66
33, 2, £69
To update the values, I join to the revenues table twice, one for both columns matching, and once for the default. I.E.
UPDATE facttable SET revenue = ISNULL(rev1.revenue, ISNULL(rev2.revenue,0))
FROM facttable FT
LEFT OUTER JOIN revenues rev1 ON FT.AffiliateID = rev1.AffilateID and FT.TypeID = rev1.TypeID
LEFT OUTER JOIN revenues rev2 ON rev1.AffilateID = 0 and FT.TypeID = rev1.TypeID
(In fact, this is over-simplified, because in fact there are 3 columns, so I have to have 8 joins like this).
This works very well, and cuts down the management of revenues significantly, there are a few 100 rows instead of the more than 100,000 there would be if I put every possible combination of values in its own row.
However, now there is a requirement to increase the granularity of the revenue allocation up to 5 columns, which makes 36 joins and there could well be more columns added later.
Has anyone come across a situation like this (and found a neater solution).
View 2 Replies
View Related
Aug 10, 2015
I am still learning T-SQL .Lets consider the table below, ID 1-3 shows our purchase transactions from various Vendors and ID 4-6 shows our payments to them
Table 1 - VendorTransactions
ID PARTY AMOUNT VOUCHER
---------------------------------------
1 A 5000 Purchase
2 B 3000 Purchase
3 C 2000 Purchase
4 A 3000 Payment
5 B 1000 Payment
6 C 2000 Payment
7 A 1000 Payment
Now we have a blank table Table 2 - Liabilities
ID PARTY AMOUNT
I want that SQL should look for each individual party from Table 1 and Calculate TOTAL PURCHASE and TOTAL PAYMENTS and then deduct TOTAL PAYMENTS from TOTAL PURCHASE so we get the remaining balance due for each party and then add the DIFFERENCE AMOUNT alongwith PARTY to the TABLE 2 so I can get the desired result like below
ID PARTY AMOUNT
-------------------------
1 A 1000
2 B 2000
3 C 0
View 3 Replies
View Related
Nov 27, 2015
I have an table with duplicate data. I need to delete the duplicate records based on the RequestId.
I want to delete the records based on the RequestID 1001, Delete the duplicates in 1002, 1003 .
DECLARE @table TABLE ([Employee_SID] [int] NOT NULL,
[CalculatedTotalSalesYear] [numeric](19, 2) NULL,
[CalculatedTotalSalesYearAnnualized] [numeric](19, 2) NULL,
[RequestID] [int] NOT NULL)
[Code] ....
Expected Result
select * from @table
View 4 Replies
View Related
Mar 7, 2007
Hi All,
I am new to this forum and had checked this forum for this problem that I am having. Although there were a lot of informative articles that I found useful, I still couldn't resolve this problem.
I am having a little trouble trying to use the Sum function to get the totals that I need for a project that I am trying to accomplish. The scenario is below:-
This is the report image that I want to produce.
Region1 policy1 policy2
AgentType1 Total Total
Agenttype2 Total Total
AgentType3 Total Total
Region1's policy1 Total Region1 Policy2 Total
Region2 policy1 policy2
AgentType1 Total Total
Agenttype2 Total Total
AgentType3 Total Total
Region2's policy1 Total Region2 Policy2 Total
(this is where i am having the problem)
AgentTotals
AllAgentType1Totals Policy1Totals Policy2Totals
AllAgentType2Totals Policy1Totals Policy2Totals
TotalforallRegions RegionsTotals
I managed to get all the information I needed in the
Report except for Agent Totals. I am having a little
bit of a logic problem on how to get the totals for a
particular agent type with each policy.
Right now my layout only has ONE grouping which holds
the region types. my dataset basically is how the
report is going to look like without the totals.
Is there an expression I can use that would identify
what type of agent that is and then add the total ?
kinda like the following:-
=IIF(agenttype = "AgentType1", Sum(all agent totals
for policy 1), "")
Am I heading in the right direction ?
Any tips or hints would be greatly appreciated.
Sincerely,
Bernard Ong
View 1 Replies
View Related
Oct 10, 2007
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
View 4 Replies
View Related
Jun 4, 2008
Hi All,
I have a Problem while updating one table data from another table's data using sql server 2000.
I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms)
I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
View 2 Replies
View Related
Jun 30, 2015
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
View 2 Replies
View Related
Apr 19, 2006
I am relatively new to SQL and am having trouble with an update. I know I cannot use a join update and have to use subquery. I am not sure of the syntax on how to do this. Below is basically what I want to do, but cannot figure out how to do it with a subquery. Any help would be appreciated.
update revisedapps
set alternateapp = t.alternateapp
from tempalternateapps t
join revisedapps r on t.appname = r.softwarename
View 3 Replies
View Related
Jan 28, 2008
I'm an sql/SSMSE novice, but have successfully connected to my sql database on a webserver.
My question is: Can I update the table data "directly" using SSMSE's interface, or do i have to run an sql update statement? (By updating table data directly, i mean: can i view the contents of a table, and simply edit the data in a field, like with in an MS Access table?) If I can update the data "directly," how does one do that?
Thanks a lot!
Murray
View 3 Replies
View Related
Dec 8, 2006
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
here is an example:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 67 | 23 | 67
so I want to set any contact_id_N = 0 where contact_id_N is = 67
so in the end, the table will look like this:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 0 | 23 | 0
is there a way to do it in one statement?
View 2 Replies
View Related
Feb 25, 2008
I have a question. I know its possible to create a dynamic query and do an exec @dynamicquery. Question: Is there a simpler way to update specific columns in a table at run time without doing if else for each column to determine which is null and which has a value because i'm running into a design dilemna on how to go about it.
FYI: All columns in the table design are set to null by default.
Thanks in advance,
Dimeji
View 4 Replies
View Related
Oct 19, 2007
I am trying to update a table in one database with data from a temporary table which i created in the tempdb.
I want to update field1 in the table with the tempfield1 from the #temp_table
The code looks something like this:
Use master
UPDATE [dbname].dbo.table
SET [dbname].dbo.table.field1 = [tempdb].dbo.#temp_table.tempfield1
WHERE ( [dbname].dbo.table.field2= [tempdb].dbo.#temp_table.tempfield2
AND [dbname].dbo.table.field3= [tempdb].dbo.#temp_table.tempfield3
AND [dbname].dbo.table.field4= [tempdb].dbo.#temp_table.tempfield4)
I get the following error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield2" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield3" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield4" could not be bound.
What is wrong?
View 1 Replies
View Related
Apr 20, 2003
I'm not sure if this could be done, but if anyone has any insight on how to do this please let me know...
Currently, I have a table that has a field of Categories. I recently created a Category table in which each category has it's own ID. I need to replace the Data that was in my original table with the new ID's based on the actual category name... Is there any way of doing this without manaul data entry?
View 2 Replies
View Related
Apr 20, 2003
I'm not sure if this could be done, but if anyone has any insight on how to do this please let me know...
Currently, I have a table that has a field of Categories. I recently created a Category table in which each category has it's own ID. I need to replace the Data that was in my original table with the new ID's based on the actual category name... Is there any way of doing this without manaul data entry?
View 2 Replies
View Related
Feb 14, 2007
Hello
Kindly i need support in this issue, i create task flow import from flat file and store in database but i need to save all result for task into specific table
Like Record count transferred
Destination table name
Time ..........etc
thanks
View 3 Replies
View Related
Aug 8, 2015
I'm looking for a quick script that someone has already written to update statistics (not to rebuild or re-organise) on specific indices in specific databases - I guess loop though a table comprising of a list of databases and the indices.
I know Ola has one but I'm not look for something that is that complicated. If I cannot find one I'm going to have to write one myself - I want to try and avoid re-inventing the wheel as tomorrow I have to do this work and it's about 7K plus indices in about 10+ databases.
View 2 Replies
View Related
Mar 21, 2008
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
View 1 Replies
View Related
May 29, 2002
Hi,
Is there any system tables or something , which one can query to get when a specific procedure was last run.
Thanks for you help.
View 1 Replies
View Related
Apr 17, 2008
Hi Newbie here,
i am trying to make a highscore table and i would like when the user enters a score for them to be able to see what position they are in the database without having to take out all the data. i have looked at a few methods and was wondering which one is the best solution and how i would go about implementing it
count()
rank()
Indexes
Any response would be greatly appreciated
Puzzled
View 3 Replies
View Related
Mar 27, 2008
We're looking at optimizing some of our tables because we have indexes on columns that are not used. So for example we might have a table that has 6GB of data and 4GB in indexes (according to sp_spaceused). We need to know how much of the 4GB of indexes is consumed by each of the indexes individually. I've tried dbcc showcontig, but that doesn't tell me the amount of space used by the index that I run it for.
Second (and by far not as important), is there a way to determine how much space is being consumed by data in a specific column. The original creator of some of the tables added an incrementing identity column that is used no where in the system. I'd like to be able to say..."If we drop this column we'll save XXX in space."
Knowing the index space is more critical, the column space would be nice but not necessary.
View 2 Replies
View Related
Jul 28, 2006
I want to write SQL that will search the tables in a database for a specific column, like this. For instance, I have a column "Unique_ID" that is in many of our tables (hundreds) but not in others and want to find out the tables it is in. It is always the first column.
I tried to find a system stored procdure to do this but couldn't and tried to create a script using the sysobjects and syscolumns tables in the Master db, but came to a roadblock because they don't seem to be related at all.
I would surely appreciate if someone else has already done this!
Thanks!
View 6 Replies
View Related
Dec 4, 2007
Hi,
I could use some help on how to save the result of my data-flow in an existing table.
I have created a data flow. I have no trouble storing the result of the flow in a new table. But I'm trying to store the result of my data flow in an existing table. (The flow involves fuzzy grouping, and i would like to store the result in the original table on which the fuzzy grouping was performed). But I cannot get it to work.
I can select the existing table as OLE DB destination. I can write an SQL to manipulate this table, but how do i address the result of the flow in the SQL?
Help is welcome here!
View 6 Replies
View Related
Feb 14, 2007
I have a dataset that was created via a source + lookups + derived columns.
I wish to take this dataset and treat is as a table within a sql statement so that I can update a permanent table with the a specific value within the temp dataset.
In sql this is what I am trying to do:
SELECT COUNT(*) AS Count_of_Employees, DEPT
FROM Employees
GROUP BY DEPT
UPDATE Departments
SET Number_of_Employees = Count_of_Employees
WHERE Dept = dept.
View 1 Replies
View Related
Mar 23, 2007
Ok, please pardon my complete ignorance; but I am extremely SQL challenged. I manage the server right now and I am the closest thing to a DBA at the moment with this server.
Here is the problem. I have a job that runs every hour and until last week it was running without any problems. Now, once a day it hangs, and I end up having to restart sql to get it to be functional again. I guess I could just kill the spid, but regardless, it's fails.
It doesn't happen at the same time each day. I thought it was from the backups doing a SQL backup job, but I eliminated that possibility. I can't find what is causing the problem, because the SQL logs just stop at the time that this happens. It's like some other job/query is running and taking all the sql server resources. But I can never catch it when it's happening and I have no event logs or SQL logs to go on.
Is there any query that I can run to find what happened around the specific time the job failed to run? I just need to find what process did what when this job stopped working.
Again, I apologize if my lack of SQL knowledge is making me ask dumb questions, but I am really lost.
Thanks in advanced!
View 3 Replies
View Related
Dec 19, 2013
I need a query to get the first value greater than a specific threshold value
LoadTemp1 LoadTemp2 LoadTemp3 LoadTemp4 TimeStamp
300 200 100 320 12-10-2013 13:30:29
100 250 113 340 12-10-2013 14:20:12
114 339 209 345 12-10-2013 14:45:01
I need to get the first value >= 340 threshold
I need to make a Benchmark on when the first LoadTemp crosses Threshold of 340 and capture the time.
View 9 Replies
View Related
Dec 28, 2014
This is my table:
use tempdb
go
if object_id('Data', 'u') is not null drop table Data
go
with temp as (
select top 10000 row_number() over (order by c1.object_id) Id
from sys.columns c1 cross join sys.columns c2
[code]....
What index would be best for these three queries? With best I mean the execution time, I don't care about additional space.
This is the index I currently use:
create nonclustered index Ix_Data on Data (StateId, PalletId, BoxId, Id)
The execution plan is SELECT (0%) - Stream Aggregate (10%) - Index Scan (90%).
Can this be optimized (maybe to use Index Seek method)?
View 7 Replies
View Related
Sep 18, 2015
I need to build a CTE query to find for the same Cabstamp (document) where i have different Origin.
I know that i can build this with a correlated subquery, but i´am curious about using CTE.
I post sample create Script:
create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)
insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc)
select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110'
union
select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110'
union
select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110'
union
[code]....
View 5 Replies
View Related
