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


ADVERTISEMENT

Transact SQL :: Dynamically Alter Schema Of Stage Table When Source Table Structure Changed?

Oct 25, 2015

we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.

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

Insert / Update In Master Table And Also Save A History Of Changed Records : Using Data Flow/simple Sql Queries

Feb 9, 2007

Hi,

My scenario:

I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to

1) separate the master (static) data from the flat files,

2) check whether that data is present in the master table, if not then insert that data into the master table

3) If data present then move that existing record to an history table and then update the main master table.

All the 7 fields need to be checked to uniquely identify a single record in the master table.

How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.

Thanks in advance for your help.

Regards,

$wapnil

View 4 Replies View Related

Transact SQL :: Want To Update One Table Using Other Table

Nov 23, 2015

I have two table  i want to update one table using other table.query given below

--table 1

create table #temp_P
(P_id int null,
p_name varchar(50),
h_id int)

--table 2

which is to be updated,check the p_name from above table( like 1 has same name like 4 so 1 will replace by 4)
create table #temp_t
(t_id int null,
p_id varchar(50),
r_id int)

[code]...

View 5 Replies View Related

Transact SQL :: Temporary Table Created In Linked Server

Sep 20, 2015

I'm executing a sp name  [uspGeneral_Getinfo]. It return a global temporary table's name and I want to get data from that table.

[192.168.2.11] = Linked Server

Declare@LinkedServer varchar(40)='[192.168.2.11].DBPharm.dbo.',@OutPutTableName varchar(50)=' ',@SQL nvarchar(max)Set@LinkedServer=Rtrim(Ltrim(@LinkedServer))Set@SQL=' Declare @OutPutTableName varchar(50),@SQL nvarchar(max) EXEC '+@LinkedServer+'[uspGeneral_GetDomainDataNew] 9,null, @OutPutTableName OutPut '+' Set @SQL='''+'Select * from '''+'+ @OutPutTableName'+' Exec sp_executesql @SQL 'Select@SQL

Above code are executing from another server [192.168.2.10].But getting an error that .I can't able to search where the temp table created in linked serve tempdb or ...?

Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Msg 208, Level 16, State 0, Line 38
Invalid object name '##table1FD1B81Bx4EAFx4FFDx9F6Fx15B77B6445F'.

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

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related

Update Table From Linked Excel Sheet

Mar 20, 2008

I cant seem to reference columns within a linked Excel server
using MS SQL express

Code:

UPDATE dbo.Items
SET CCC=XL_SERVER.CCC
FROM XL_SERVER...sheet1$
WHERE BBB LIKE '7%'

i.e I want to update column CCC in my Items table from an Excel table column CCC where column BBB begins with 7

I have set up XL_SERVER correctly.

Help!

View 3 Replies View Related

Linked Server Update Table Error

Jul 31, 2015

We have 2 instances. One is sqlgpscluster and another is [sqlcdsclustersqlcds]. We have created linked server between sqlgpscluster and [sqlcdsclustersqlcds]. We run the below query on sqlgpscluster instance

UPDATE [sqlcdsclustersqlcds].CDSBusiness.dbo.tblInsertNowAppMonitoring
SET NoCabsDateTime = GETDATE(),City=@City
,Area = dbo.fn_FindAreaSubAreaBasedOnLatLong(@PickUpLat,@PickUpLng)
WHERE CustomerMobileNo=@CustomerMobileNo

We are seeing the below error:
The OLE DB provider "SQLNCLI" for linked server "sqlcdsclustersqlcds" could not UPDATE table "[sqlcdsclustersqlcds].[CDSBusiness].[dbo][tblInsertNow AppMonitoring]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

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

The OLE DB Provider MSDAORA For Linked Server .... Does Not Contain The Table COUNTRY. The Table Either Does Not Exist Or The Current User Does Not Have Permissions On That Table.

Jun 13, 2006

I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
 
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.

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

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 Table Based On Join

Sep 7, 2015

I have table A with colums ID and Product. I have table B with ID (same as table A) and revenue of product.

I need to update the field Product of table A with the revenue of table B.

I'm confuse with the update joining both tables. I tried this, but obviously has an error:

Update A set Product=B.Revenue where A.ID=B.ID

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

Transact SQL :: Nested Cursors To Update Table?

May 15, 2015

I have been wrestling with the code all day to accomplish the following: I need to update a table based on values from another table. So far, I have been able to do the below:

DECLARE @LookUpTerm VARCHAR(25)
, @SearchCol VARCHAR(255)
, @LogonIDToProcess VARCHAR(50)
, @Matched CHAR
, @Cycle INT = 1
IF OBJECT_ID('tempdb..#Glossary','U') IS NOT NULL DROP TABLE #Glossary
IF OBJECT_ID('tempdb..#Employees','U') IS NOT NULL DROP TABLE #Employees

[code]...

View 7 Replies View Related

Transact SQL :: Iterate Through Table Rows And Do Conditional Update

Aug 11, 2015

I have a table with the following fields:

ID (int, identity)
Name (nvarchar(255))
Block (nvarchar(50))
Street (nvarchar(255))
Floor (nvarchar(50))
Unit (nvarchar(50))
Address1 (nvarchar(255))
Address2 (nvarchar(255))

I want to iterate through the table and populate Address1 as [Block] [Street] #[Floor]-[Unit].If the 'Floor' field contain a number < 10 (e.g., '8'), I want to add a '0' before it (e.g., '08'). Same for Unit.How would I do this using cursors (or other recommended method)?

View 4 Replies View Related

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 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 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 :: Query To Update A Table With More Than 150 Million Rows Of Data?

Sep 17, 2015

I have been tasked with writing an update query to update a table with more than 150 million rows of data. Here are the table structures:

Source Tables :

OC
CREATE TABLE [dbo].[OC](
[OC] [nvarchar](255) NULL,
[DATE DEBUT] [date] NULL,
[DATE FIN] [date] NULL,
[Code Article] [nvarchar](255) NULL,
[INSERTION] [nvarchar](255) NULL,

[Code] ....

The update requirement is as follows:

DECLARE @Counter INT=0 --This causes the @@rowcount to be > 0
while @@rowcount>0
BEGIN
    SET rowcount 10000
    update r
    set Comp=t.Comp

[Code] ....

The update took more than 48h and didn't terminate , how to accelerate it ?

View 6 Replies View Related







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