Transact SQL :: Failure Update Table Column Script

Jul 17, 2015

I am dynamically creating a job using sql script and it does work fine(It creates the job and when it's done it gets deleted as it's created dynamically. so I won't be having any job history at all in the system.). I want to update an existing table if the jobs fails and I am not sure how I can do that using t-sql script. Is that possible?I have an idea but not sure whether it works. In the job create script, can I include the code for if the job fails then go to step 2 and update the table column with the error??? If so, how can I retrieve the error???

View 3 Replies


ADVERTISEMENT

Transact SQL :: Column Update With Comparison With Another Table

Nov 10, 2015

I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.

Txt_Temp - Table

I tried this code with no succes and only one row is updating

update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber

OR Add this code in view 

Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code 
from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber

Vin
123456
123123
123789

Resrve_Temp1 - Table
asddfghjklk123654
asddfghjklk123456
asddfghjklk321564
asddfghjklk123123
asddfghjklk123789
asddfghjklk654655
asddfghjklk456465

My Result can be in Txt_Temp table or new table or with one or two columns

asddfghjklk123456 123456
asddfghjklk123123 123123
asddfghjklk123789 123789

View 10 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

Transact SQL :: Update Table With Max Value And Row Number (based On 2 Column Partitions)

Sep 15, 2015

I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null

job_cd permit_nbr reference_nbr

ABC1 990 100002
ABC1 990 100002
ABC1 991 100003
ABC1 992 100004
ABC1 993 100005
ABC2 880 100006
ABC2 881 100007
ABC2 881 100007
ABC2 882 100008
ABC2 882 100008

View 3 Replies View Related

Transact SQL :: How To Update New Table By Separating Single Date Column

Oct 11, 2013

I am trying to build a DIM table using a source table that has the following setup...

CREATE TABLE [dbo].[APPL_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[APPL_STATUS_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO

[code]....

What I am trying to do is to break out the APPL_STATUS_DATE into a STATUS_START_DATE and an STATUS_END_DATE in a new table.  I also need to be able to update the STATUS_END_DATE based on the previous day's date. Like so...

CREATE TABLE [dbo].[APPL_APPLICANT_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[STATUS_START_DATE] [datetime] NULL,
[STATUS_END_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO

[code]...

View 10 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

Transact SQL :: How To Update A Column

May 19, 2015

I have a table that has two columns. One column has ID from 1 to 1800. The other column is null. I want to update the second column with values from 29 to 43. So for ID 1, value will be 29, ID value will 30 and it goes to 43. Then after it will start from 29 again and goes to 43. It goes all the way to the highest ID, i.e. 1800.I have added a script for better clarity 

declare @t table (stuID int, valueID int)
insert into @t (stuID, valueID)
values (1,null), (2,null), (3,null), (4,null)

the null part is what I want to have from 29 to 43 .... all the way untill the ID reachs to 1800

View 5 Replies View Related

Transact SQL :: Update Element Name For XML Column

Jun 2, 2015

I have sample XML data which is shown below in my table. I need to update one of the column names in this field.

Changing <Desc> to <Description>

Input:

DECLARE @myxml
XML
SET @myxml
= '<Cust id="1">           
<Name>aaaaaaaaaa</Name>
             
<Desc>bbbbbbbbbb</Desc>
</Cust>'

Output:

DECLARE @myxml
XML
SET @myxml
= '<Cust id="1">
             
<Name>aaaaaaaaaa</Name>
<Description>bbbbbbbbbb</Desc>
</Cust>'

View 4 Replies View Related

Transact SQL :: Update Column With Row Number

Jul 23, 2015

I have a Users Table. It is full of users already and I would like to start using the UserPIN in the software (this is an nvarchar column).

I would like to update the UserPIN column with the row_number. All of my efforts have resulted in setting the UserPIN to 1 for every record.  I just want an update query that fill the UserPIN column in sequential order.

View 6 Replies View Related

Transact SQL :: Cannot Update Identity Column (ItemID)

Jun 1, 2015

By mistake i deleted a record form prod table.

how can i enter that record back? i get this error.

INSERT INTO item_details(ItemID,Item_Name,price) VALUES(201, bag,10)

ItemID is  identity column. can i disable  identity column and enter and then enable using sql statement?

View 3 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 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

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 :: ALTER Vs UPDATE When Creating A New Column With Default Value

May 8, 2015

I have a table with ~30M records. I'm trying to add a column to the existing table with default value and have noticed following ... When using alter with default value- (Executes more than 45 min and killed forcefully)

ex:  
ALTER TABLE dbo.Table_X Add is_Active BIT CONSTRAINT DF_Table_X_is_Active DEFAULT 'FALSE' NOT NULL
GO

When using update command after adding column with alter (without default value) it completes is 5 min.

ex:  
ALTER TABLE dbo.Table_X Add is_Active BIT NULL
GO
UPDATE Table_X SET is_Active = 0 WHERE is_Active IS NULL
GO

Why there is so much of difference in execution times ? I was just trying to understand internal behavior of the SQL in these two scenarios. 

View 4 Replies View Related

Transact SQL :: Get Identity Column Value When Update Statement Fails?

Sep 9, 2015

My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.

However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.

Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.Now, I was asked to log the specific record where the failure was occured.Something like log the identity column value or primary key value which record has failed.

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

Transact SQL :: Update Table With Its Value And Data From Row In Temp Table For Matching Record?

Oct 25, 2015

I have a temp table like this

CREATE TABLE #Temp
 (
  ID int,
  Source varchar(50),
  Date datetime,
  CID varchar(50),
  Segments int,
  Air_Date datetime,

[code]....

Getting Error

Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.

View 4 Replies View Related

Transact SQL :: How To Update Subsequent Columns If Proceeding Column Is Not Null

Jul 7, 2015

I've got a table with 6 fields :

EmployeeAccess
(MasterID, LoginID, AccessID, Storage1, Storage2, Storage3)
that needs to be updated using the data in the following spreadsheet
NewEmployeeAccessData
(ID, MasterID, AccessID1, LoginID1)

There is a 1:1 relationship between the two tables..I'm trying to code a pair of update statements on the EmployeeAccess table (1 for LoginID, 1 for AccessID) with the following logic:

If LoginID is NULL, then Update LoginID with new LoginID1 value,
If LoginID is not null, and Storage1 is NULL then Update Storage1 with New LoginID1 values
If LoginID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New LoginID1 values
etc etc...

The same applies when trying to populate the AccessID column

If AccessID is NULL, then Update AccessID with new AccessID1 value,
If AccessID is not null, and Storage1 is NULL then Update Storage1 with New AccessID1 values
If AccessID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New AccessID1 values
etc etc.

I have no control over the schema of this table  so I'm trying to work the logic on how to update the columns in my table only if the corresponding column data is NULL, else update the next non NULL Storage column.

View 7 Replies View Related

Transact SQL :: Get Table And Column Name In Separate Column Using PIVOT

Jul 16, 2015

Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:

Text                                                     QueryPlan             Plan_handle  
         Name         Value

select id,name,Address from person     <showPlznXML...   010101                 Table            Person
select id,name,Address from person     <showPlznXML...   010101                 column         id
select id,name,Address from person     <showPlznXML...   010101                 Table            Person

[code]....

View 26 Replies View Related

Transact SQL :: Update Table Based On Available Date Range In Same Table

Dec 2, 2015

I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.

Update TableName
SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)'
Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range

Note: ROW is Partition By ColumnA,ColumnB

TableName: PromotionList

ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 0 NULL
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 0 NULL
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL

Expected outcome after performing update on the table

ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 1 Overlap with row ID 4
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 Overlap with row ID 5
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL

View 4 Replies View Related

Transact SQL :: Update Multiple Table Referencing New Table Data

Aug 4, 2015

I have a table called ADSCHL which contains the school_code as Primary key and other two table as

RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.

I tried using Merge Update function not sure if this is going to work.

Update dbo.ADSCHL
SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD
INNER JOIN TESTCEP FD
ON AD.SCHOOL_NAME = FD.School_Name

View 10 Replies View Related

Transact SQL :: Update Table From Linked Table If Something Has Changed?

May 20, 2015

Is it possible to check/notify if something (record) has changed in the linked table (from linked server) and automatically update table in local one?

View 4 Replies View Related

Transact SQL :: Update A Field In Table Based On Another Table

Nov 17, 2015

I have a daily record table (has field Date1, field2Update) and have another table(has field Date2, Flag (value: 0 and non 0 values)

I want to update table1 and set field2Update as somevalue where table2.Flag <> 0

View 4 Replies View Related

Transact SQL :: Update Table From Another Table For Multiple Rows

Jun 10, 2015

Matrix table has ID column and data below.

ID        Flag     TestDate         Value     Comment                                                                  
111          2       12/15/2014     7.5             null
222         2            Null                10          received

Matrix_Current table could have 1 or multiple rows as below.

ID        Flag               TestDate           Value         Comment
111         2                  01/26/2015        7.9                                                                      
111         2                  02/23/2015        7.9                                                      
111         2                  04/07/2015        6.8
222        1                   null                   8               test comment 1
222        3                   null                   9               test comment 2

When I run below update

 UPDATE  AM
 SET  M.Flag = MC.Flag, M.TestDate = MC.TestDate,
M.Value = MC.Value, M.comment = MC.Comment
 FROM dbo.Matrix M inner join dbo.Matrix_Current MC on M.ID = MC.ID

Matrix table has value below:

ID        Flag     TestDate         Value     Comment                                                                  
111          2       01/26/2015      7.9             
222         1            Null               8            test comment 1

I want to update Matrix table from all row from Matrix_Current, final table would like below:

ID        Flag     TestDate        Value     Comment                                                                  
111          2        04/07/2015      6.8             
222         3            Null                9         test comment 2

View 3 Replies View Related

Transact SQL :: How To Alter Existing Table Column As Identity Without Dropping Table

Nov 20, 2013

I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.

create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)

View 7 Replies View Related

Transact SQL :: Delete Records From Table (Table1) Which Has A Foreign Key Column In Related Table (Table2)?

Jun 29, 2015

I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).

Table1 columns are: table1Id; Name.  Table2 columns include Table2.table1Id which is the foreign key to Table1.

What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?

View 11 Replies View Related

Transact SQL :: Update One Table With Records From Another Table

Sep 5, 2015

How to update tblA with records from tblB (see below expected results) when they have common column DrawingNo?

View 4 Replies View Related

Transact SQL Question: How Do I Update A Table With Just One Row From Another Table.

Mar 14, 2008



I have 2 tables which with one-many relation. Table B has three child records based on Table A. I want to update value in Table from one of the records in table B.

Right now I am thinking of creating 2 temp table importing values there but does not seem to work. Can someone please help thanks. Armoghan

View 4 Replies View Related

Transact SQL :: Update Table From Another With Conditions

Dec 2, 2015

I am trying to do a simple update in one sql table from another where a certain condition in both tables are met.I am missing something basic and have tried numerous posted examples, without success.Here is my code (that does not update) :

        opdragaliasnaaminsit.Connection = konneksie
        opdragaliasnaaminsit.CommandText = "UPDATE vyfjaarontledings " & _
        "SET aliasnaam = T2.aliasnaam" & _
        " FROM  vyfjaarontledings T1" & _
        " INNER " & _
        "JOIN blokke T2 " & _
        " ON T1.plaasno = T2.plaasno " & _
         "WHERE T1.plaasno = T2.plaasno"
opdragaliasnaaminsit.ExecuteNonQuery()

I am trying to update aliasnaam in vyfjaarontledings from blokke.

View 10 Replies View Related

Transact SQL :: How To Update Table Without Using While Loop

Nov 15, 2015

I have two tables i have to update table2 using table1 without using while loop.

example given below.

Table1 

rid
id
amt
firdate
lastdate

1
1
500

[code]....

View 7 Replies View Related

Transact SQL :: Table Doesn't Get Update Through Job

Sep 28, 2015

I have following T-SQL statement:- 

insert into [dbo].[tbl_FG_Alert_Count_All_Report] ([Date] ,[Count] ,[Rule Type])
   SELECT TOP 10 [Date]
      ,[Count]
      ,[Rule Type]
  FROM [dbo].[tbl_FG_Alert_Count_All] where Count <>'0' and DATEDIFF(dy,date,GETDATE())  = 1 order by Date desc

When I ran this T-SQL statement in SSMS; I don't get any error and as expected, I can see new data in [dbo].

[tbl_FG_Alert_Count_All_Report] table. 

Now I created one job with same T-SQL Statement. Job completes successfully with out giving any error message; But unfortunately I don't see any new data in [dbo].[tbl_FG_Alert_Count_All_Report] table.  What would be the reason that I don't see new data when job completes successfully but I can see new data after executing same T-SQL statement in SSMS?

View 3 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

Transact SQL :: How To Update Table In Many To Many Relationship

Nov 30, 2015

I am having challenge to update the redemption table from the multiple card activation table.  I want to update the redemption table with the activation date closest to the redeem date.  

For example:  Redeem date 20071223, I need to update the top row Date, Year, Period fields from the Card activation table.

Redeem date 20071228, I want to refer to the second row in the Card activation table date 20071228.  
Redeem date 20080316 or later, I want to use the last row in the card activation table date 20080316.

How to modify the update query to select the right activation row accordingly?

Below is my partial code I used but it always pick the 20071223 date to update my redemption table.

 CREATE TABLE #Card
 (
       [CardNumber] varchar(20)
 ,[ Date] int
 ,[ Year] int
 ,[ Month] int
 ,[ Period] int
  )  
 
[Code] ....

View 13 Replies View Related







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