Transact SQL :: Undo Update And Bring Back Records To Their Previous Values

Aug 7, 2015

I have a table with 1 million records. I want to update only 400 records. The update statement is provided by a 3rd party vendor. Once i run the update statement it will update all the 400 records. Once the table is updated the users will validate the table

if the update is successful or not. What i'm looking for is:

1) Is there a way to identify what records were updated.
2) If the update done is not what the users wanted i need to undo and bring back the 400 records to their previous values.

I'm on sql server 2008.

View 34 Replies


ADVERTISEMENT

How To Bring Back The Distinct Values In Single Column From Two Tables

Jul 20, 2005

12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim

View 1 Replies View Related

Transact SQL :: Comparing Previous Records Based On Each ID Column

Aug 21, 2015

I have a scenario to compare previous records based on each ID columns. For each ID, there would be few records, I have a column called "compare", We have to compare all Compare 1 records with Compare 0 Records. If Dt is lesser or equal to comparing DT, then show 0. Else 1

We always only one Compare 0 records in my table, so all compare 1 columns will compare with only one row per ID

My tables look like

Declare @tab1 table (ID Varchar(3), Dt Date, Compare Int)
Insert Into @tab1 values ('101','2015-07-01',0)
Insert Into @tab1 values ('101','2015-07-02',1)
Insert Into @tab1 values ('101','2015-07-03',1)
Insert Into @tab1 values ('101','2015-07-01',1)
Insert Into @tab1 values ('101','2015-06-30',1)

Insert Into @tab1 values ('102','2015-07-01',0)
Insert Into @tab1 values ('102','2015-07-02',1)
Insert Into @tab1 values ('102','2015-07-01',1)

select * from @tab1

1.) In the above scenario for ID = '101', we have 5 records, first record has Compare value 0, which mean all other 4 records need to compare with this record only

2.) If Compare 1 record's Dt is less or equal to Compare 0's DT, then show 0 in next column 

3.) If Compare 1 record's Dt is greater than Compare 0's DT, then show 1 in next column 

My expected result set should be like ....

View 10 Replies View Related

Transact SQL :: Pull Records For Current And Previous Calendar Year

Jun 16, 2015

I am looking to pull all records for current & previous calendar year in one query. I know how to pull the current calendar year, but how would I pull current & previous?

select id, list_date

from tableA

where list_date > DATEADD(year,-1,GETDATE())

View 5 Replies View Related

Transact SQL :: Update Column Based On Row Number For Previous Row

Jul 25, 2015

Below is the resultset I got using the following SQL statement

SELECT   ROW_NUMBER() OVER (PARTITION BY ID ORDER BY create_date DESC) AS RowNum
,ID
,create_date
,NULL AS end_date
FROM dbo.Table_1

Resultset:

RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 NULL

[Code] ....

Now, I want to update the end_date column with the create_date's values for the next row_number. Desired output is shown below:

RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 2015-02-18

[Code] ....

View 4 Replies View Related

Transact SQL :: Insert Records And Write Back Identity Value

Jun 19, 2015

I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.

So for example I want to send 

BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.

No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions. 

View 5 Replies View Related

Bring A Table Back

Feb 25, 2004

Hello, everyone:

I have deleted a static table accidentally in production. How will I bring it back? Thanks a lot.

ZYT

View 2 Replies View Related

Bring Back Excluded Item

Jul 20, 2005

Hi all,I have been wrestling with this problem all morning with no success sofar where I have a need to bring back an excluded field.Basically I have a list of order numbers. Each order number can havemany order types attached one of which is a ‘P’ type. Most order typeshave an account number attached in its own field however when a ‘P’ typeis selected the account number is not brought back.Is there someway I can get this brought back for each P type or do Ihave to do some fancy insert in a data warehouse to get this done (i.e.insert account numbers into all P types)?Many thanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Count Need To Bring Back A Zero When No Matches

Sep 13, 2007

I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches.
This is my code:

select d.lwia,

cast((d.closegoal + d1.opengoal) as float)denominator

from

(

select yg.lwia,

cast(count(yg.appid)as float) closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

)d,

(

-- Closure with open goal

select cast(count(yg.appid)as float) opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @RWB

group by yg.lwia

)d1

)d2

View 3 Replies View Related

Restoring Master Did Not Bring Back Users

Jun 28, 2000

I'm trying to recreate a sql database on another server and I installed sql server 6.5 on a server and then restored the master db using the Sql Setup program. It worked without any errors, but it did not create the loginthat will use the database that I have not restored yet. Aren't the Logins storied in the master database?

Thanks.

View 2 Replies View Related

How To Bring Back A List Of Duplicates From A Column

Jul 20, 2005

I have a column that has 75 values, 50 are unique/25 are duplicates. Ineed to be able to bring back a list of the duplicates, listing allrows even if more than two have the same value. I need to be able todo this using t-sql.Thanks,Tim

View 5 Replies View Related

MSDE Database Is Offline, How Do I Bring It Back Online?

Nov 29, 2007



Dear All,

One of my users created a MSDE database and did not realise that the limit is 2GB. I can not access the database. Is there anyway I can recover it?

They do not have a backup.

Could I create a new database in Standard Edition and then attach the Data and log files from the MSDE database?
Would that work?

View 4 Replies View Related

Unable To Bring Database Back Into MULTI_USER Mode.

Sep 24, 2007

I'm wondering if somebody could provide insight into a problem I'm having with SQL Server 2005. Although the problem is happening wthin an SSIS ETL, I don't think this problem is SSIS related.

In the ETL I need to rename a database, so I first put the database into single-user mode by issuing the command:

ALTER DATABASE foobar SET SINGLE_USER WITH ROLLBACK 30


The database then goes into single-user mode, and after the renaming occurs, I attempt to put the same database back into multi-user mode:

ALTER DATABASE foobar SET MULTI_USER WITH ROLLBACK IMMEDIATE

However, whenever I have a query pane opened against the same database in SQL Server Management Studio, the ETL fails and I get this error message:

"Error: Changes to the state or options of database 'foobar' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it."

I'm wondering why the ALTER DATABASE command does not kill off the active connections? This is on my development box, and I'm the only one connected to the database. I've tried with ROLLBACK 30 as well, same thing. If I cut and paste the same command into Mangement Studio, the command succeeds so I don't think its a permission issue (using Windows Authentication both Management Studio and the ETL are executed by the same login). If I close the query pane the ETL succeeds at restoring multi-user mode. Is there something I am missing? Thanks in advance!

View 8 Replies View Related

Using Membership Db To Lookup Data In Another Db And Bring Back Into A Grid View (total Newbie To .net)

May 11, 2007

I have the membership stuff up and running.  I've added a field to the membership table called custnmbr.  Once a user logs in, I want store his custnbmr in the session and use that to lookup data in another db.
ie: Joe logs in and his custnumbr is 001, he goes to the login success page and sees his list of service calls which is:
select top 10 * from svc00200 where custnmbr = 001 (the membership.custnmbr for the logged in user)
I know how to do this in old ASP using session variables....but I have no idea where to even start with .Net.
Many thanks

View 7 Replies View Related

How To Undo And Update To Field

Jan 17, 2014

I have update a table field and I like to know how do you "undo" the update? I set the field_length from 6 to 7 and like to undo this.Here is my command:

table = template_fileds
column= field_length.
filed name= field_name.
update template_fields set field_length='7' where field_name='assesscode9'

View 7 Replies View Related

Undo Insert/ Update

Nov 29, 2007



Hi
Can somebody advice me on this:

I am trying to update a row, which is being pulled from another table.

i.e., I have two tables T1 and T2

I am getting a row from T2 and inserting it in T1
then I am updating that row in T1

What i want is if in case these is an error while updating this row I want to remove the row from T1
T1 and T2 are same.

declare @err1 int

--BEGIN TRANSACTION

INSERT INTO dbo.T1 SELECT * FROM dbo.T2 WHERE UpdateFlag = 'Y' AND ID = '12345'

BEGIN TRY

UPDATE dbo.T1 set ReportDate= '9910-12-2006'

SET @Err1 = @@error

END TRY

BEGIN CATCH

insert into errorlog values (error_message())

END CATCH



--if @err1<>0

--begin

--Rollback transaction

--end


This work fine but I am not able to roll back the transaction

When I activate the codes fro roll back, it not performing the Logging operation in CATCH also terminating

REQUIREMENT

1) Get the record in T1 from T2
2) Update the record in T1
3) IF step:3 good commit it, else remove the only the row which was pulled from T2. It must do this also log in dbo.ErrorLog the errored system message error_message()

Thanks a lot in advance









View 3 Replies View Related

Transact SQL :: How To UPDATE More Than 500 Records In One Call

Aug 13, 2015

I have following query which is created dynamically as -

UPDATE  BUILDTABLE  SET BUILD_ID =  '984137'  WHERE SET_NUMBER = '1889147436' AND SEND_DATE = '1941-03-04'; 
UPDATE  BUILDTABLE  SET BUILD_ID =  '984137'  WHERE SET_NUMBER = '1115509374' AND SEND_DATE = '1991-09-01'; 
UPDATE  BUILDTABLE  SET BUILD_ID =  '984137'  WHERE SET_NUMBER = '1515579671' AND SEND_DATE = '1941-05-24'; 
UPDATE  BUILDTABLE  SET BUILD_ID =  '984137'  WHERE SET_NUMBER = '1795509670' AND SEND_DATE = '1958-01-14';
UPDATE  BUILDTABLE  SET BUILD_ID =  '984137'  WHERE SET_NUMBER = '1915508672' AND SEND_DATE = '1961-09-07';

Here till " UPDATE BUILDTABLE SET BUILD_ID =  '984137'" is the same clause for all queries, but "where" condition is different for all queries. I have to update more than 500 UPDATE statements(like above) in one call. Currently I am concatenating all the queries in string Builder which is time consuming.I want to increase performance of application.Any other class like BulkCopy ?

View 18 Replies View Related

Transact SQL :: Update All Records In Table?

Oct 22, 2015

Every day the timestamp is changed on all rows in one of the table(table has 1 mio records).

What is the best way to find out which query is doing this?

Could be also query which updates only one record(or couple of them) but is executed for each record once.

I have been looking sys.dm_exec_query_stats and sys.dm_exec_sql_text for past but couldn't find.

I can't use event notifications since there is service broker disabled.

View 5 Replies View Related

Cannot Update Records That Contain NULL Values!!!

Oct 19, 2006

Hi,  I am trying to use a formView with an update button to update individual records in an sql database. (when i click update it doesnt perform the update and just refreshes the page. ) One of the fields in my records is a NULL - this is also one of the fields that i need to update. When i manually go into the database and enter some data, and then go back to my form, it updates fine, but as soon as i delete the data from the field, it returns to NULL and im back to square one. Any Ideas on how to get around this problem?THanks

View 4 Replies View Related

Transact SQL :: Update Null Values

Jun 23, 2015

i want to display the max Date in place of NULL when Indicator is "1"

INPUT

Emp  Num
Date
Indicator
1
Null
1

[code]....

View 3 Replies View Related

Transact SQL :: How To Update Multiple Records In A Table

Jul 23, 2015

I need to update more than one record at once. I have ~ 100 records that I have to update and don't want to execute query 100 times.

My query looks like this:

Update Table1
Set Table1.field1 = ( select Table2.field1 from Table2 where Table2.field2 IN ('a', 'b', 'c')
where Table1.field2 IN ( 'a', 'b', 'c')

It obviously failed because subquery returned more than one value and error message stated that I can't use '=' operator in this case.

My question: how could I update the same column from many records in one execution?

View 10 Replies View Related

Query To Bring Only Records That Have 2 Char

May 9, 2007

I am trying to get all the records where a particular field ( description) has no more that two characters

something like this

select * from
mytable
where description.lenght < 3

View 3 Replies View Related

Transact SQL :: Update Unique Records Count For Subquery?

Sep 2, 2015

updating the # of Payer from below query to match with the # of rows for each payer record. See the Current and desired results below. The query is currently counting the # of rows for all payers together and updating 3 as # of payers. I need it to count # of rows for each payer like shown inDesired result below. It should be showing 1 for first payer and 2 for 2nd & 3rd based on # of times each payer is repeated..

SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN,
(Select Count(*) From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c where a.CLAIM_ICN_NU =
c.CLAIM_ICN and b.TPL_TS = c.TPL_TS and a.CLAIM_TYPE_CD = 'X' 

[Code] ....

Current Result

FILING_IND
PYR_CD
PAYER_ID
PAYER_NAME
CLAIM_ICN
#_OF_PAYER

[code]....

View 4 Replies View Related

Transact SQL :: Update Records With Minimum Date In Table?

Nov 19, 2015

I want to update the STATUS column based on min date in my table.

For example – If minimum BOOKING_DATE of any RecieptID is below to 2015-10-01, then Status of that RecieptID should be 0 for all records pertaining to dedicated RecieptID So I need an output in this way.

View 3 Replies View Related

Transact SQL :: Update Statement To Include Multiple Records At Once

Apr 20, 2015

I have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.

update
mklopt
set
 FRMDAT =
'12/31/2014'
where
 JOBCOD =
'PH14789' 

I also want to include the following instead of running it one at a time

PH17523    
PH17524    
PH17525    
PH17553    
PH17555    
PH17556    
PH17557    
PH17558    
PH17571    
PH17573    
PH17574    
PH17575    
PH17576    
PH17577    
PH1757

View 9 Replies View Related

Transact SQL :: Update One Table Based On Another Table Values For Multiple Values

Apr 26, 2015

I have two tables  A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B. 

View 5 Replies View Related

Transact SQL :: Need To Update That Column Several Times With New Values For Different Nodes

May 28, 2015

I have a column with XML data stored in it. I need to update that column several times with new values for different nodes. I've written a CLR function to update the XML quickly but the update is always based on the initial value of the xmlData column. I was hoping that the subsequent updates would be based on the new data from the prior update (each xmlTable has several newData rows). Do I have to make this a table valued function and use cross apply?

UPDATE xmlTable
SET xmlTable.xmlData = Underwriting.UpdateByDynamicValue(xmlTable.xmlData,newData.NodeID,newData.NewValue)
FROM xmlTable
JOIN newData
ON xmlTable.ID = newData.fkXmlTableID

View 2 Replies View Related

Can We Pause Log Shipping, Bring Primary Db To Simple Recovery Model And Then Back To Full R Model?

Apr 25, 2008



We have the following scenario,

We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.

SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.

Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.

It it possible, if yes how can we achieve this.

If not what could be another DR solution in this scenario.

Thanks Much
Tejinder

View 6 Replies View Related

Transact SQL :: Update A Column Randomly Based On Values From Another Table

Oct 27, 2015

I'm trying to Update a column table based on values from another table but I need these values are random.My query looks like this and doesn´t work

DECLARE @Rowini int,
DECLARE @lastrow int
SET @Rowini = 1
SET @Lastrow = 80000

[code]...

View 8 Replies View Related

Reporting Services :: Query To Bring Through Today Values Only

Sep 8, 2015

I have a query where:

WHERE a.jobdate >=GETDATE()-1

This brings though data from today and yesterday; I understand this is because GETDATE = now.

However, all I want is to see is data from today only.

Is there any way the above query can be amended - probably not using GETDATE - so I can achieve this?

View 3 Replies View Related

How To Sum Previous Row Values?

May 15, 2008

Hi,

I need your help for my SQL query. I have a table like this

ClmA ClmB ClmType
------+------+----------
1 | 10 | 0
2 | 20 | 0
3 | 30 | 1
4 | 40 | 0
5 | 50 | 1

And its the result that i want to get.

ClmA ClmB ClmType ClmResult
------+------+---------+-------
1 | 10 | 0 | 10
2 | 20 | 0 | 30
3 | 30 | 1 | 30
4 | 40 | 0 | 70
5 | 50 | 1 | 80


Let me explain. When retrieving a row, an extra column should be added.It's value should be the sum of previous rows whose type is the same with the encountered one. I made it with a function but it's performance was terible with large tables. I have tables larger then fifty housands rows.

View 7 Replies View Related

Next / Previous Records From Table

Jan 9, 2008

I have got a table with GUID (PK), COMPANY, CONTACT. There are going to be instances where the company name is the same on multiple records.

What i am trying to do is work out what is the next and previous record

The data is going to be sorted, by COMPANY then GUID.

I am think a stored procedure would be the best to combact this, but very usure how to go about writing it, so i passed the present company and GUID values to it.

Any help would be appreciated, and thanks in advance.

View 20 Replies View Related

Getting Previous Values Of A Field

Mar 15, 2006

Hi,In oracle I have a LAG function using which I could get the previousvalue of a field.Do we have anything similar to that in SQL Server or access?ThanksDevi

View 4 Replies View Related







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