Updating Table Referencing 2nd Table Using Case
Feb 9, 2008
Hi
Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong...
Using T-sql!!!
update import set import.gone =
from import
inner join stat
ON stat.id = import.id
CASE
WHEN stat.A = import.field2 THEN import.gone = sec.A
WHEN stat.B = import.field2 THEN import.gone = sec.B
WHEN stat.C = import.field2 THEN import.gone = sec.C
WHEN stat.D = import.field2 THEN import.gone = sec.D
WHEN stat.E = import.field2 THEN import.gone = sec.E
WHEN stat.F = import.field2 THEN import.gone = sec.F
ELSE import.gone = null
END
Any help would be greatly appreciated
View 3 Replies
ADVERTISEMENT
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
Sep 16, 2004
I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.
Here is a basic breakdown of what I am trying to do:
Table1 (the table where the rows need to be deleted from)
Column_x (Holds the id that is unique to the various rows of data - User ID)
Table2 (Holds the user information & has the associated ID)
Column_z (holds the User ID)
I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.
Code:
SELECT Column_z
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z <> Column_x
This did not seem to do what I needed, it did not delete any rows at all.
I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2
Then I tried another scenerio that I also needed to do:
Code:
SELECT Column_z, Column_a
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z = Column_x AND Column_a='0'
'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.
Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.
Any ideas on what I am doing wrong here?
View 3 Replies
View Related
Mar 19, 2015
IF OBJECT_ID('tTable') IS NOT NULL
DROP TABLE tTable
GO
CREATE TABLE tTable
(nRow_IdINTEGER IDENTITY NOT NULL PRIMARY KEY,
nParent_IdINTEGERNULL,
[Code] ....
gives:
nRow_Id nParent_Id cGroup cValue
----------- ----------- ------- ------
1 1 One A
2 1 One B
3 2 One C
I want to insert a copy of this data, but w/ group = 'TWO', so the table will contain the additional rows
4 4 Two A
5 4 Two B
6 5 Tow C
View 5 Replies
View Related
May 12, 2008
How do i insert data into multiple tables. Lets say i have 2 tables: Schedules and Event
Schedules data is entered into the Schedules Table first
then now i need to insert Event table's data by refrencing the (PK ID) from the schedules table.
How do i insert data into Event table referencing the (PK ID) from Schedules Table ?
Fields inside each of the tables can be found below:
Event Table
(PK,FK) ScheduleID
EventTitle
AccountManager
Presenter
EventStatus
Comment
Schedule Table
(PK) ID
AletrnateID
name
UserID
UserName
StartTime
EndTime
ReserveSource
Status
StatusRetry
NextStatusDateTime
StatusRemarks
View 2 Replies
View Related
Sep 1, 2007
Hi,
I'm using MS SQL 2005 Express.
CREATE TABLE Folder (
iD int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
folderName varchar(50) NOT NULL,
parentFolderID int NULL
FOREIGN KEY REFERENCES Folder (iD)
)
GO
if I add an ON DELETE CASCADE to the foreign key, then i get an error... which is annoying. If a folder is deleted, then all its sub-folders should also be automatically deleted.
The error is: 'Introducing FOREIGN KEY constraint 'FK__Folder__parentFo__7D78A4E7' on table 'Folder' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'
Anyone got any advice?
View 1 Replies
View Related
May 25, 2005
I have a table that holds a ParentID and the RecordID. There is a column called IsEnabled which is a bit field indicating if a folder can be displayed or not. 0 = NO, 1 = YESThe table is for a directory structure which is virtual and displays folders on a web page.Root----- 1---------- 1-1---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4---------- 1- 3---------- 1- 4I need a query that will not select any children that are under a Parent that is disabled. So if ' 1- 2 ' is disabled then:---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4SHOULD NOT SHOW.Can anyone give me a query that will overcome this problem i have.-J
View 4 Replies
View Related
Jul 18, 2000
I would like a stored proc to be fed the table name as a paramater.
I tried below
create procedure testit as
set nocount on
declare @tblnm varchar(50)
select * from @tblnm
but it get Server: Msg 170, Level 15, State 1, Procedure testit, Line 5
Line 5: Incorrect syntax near '@tblnm'.
Any idea
View 1 Replies
View Related
Jul 22, 2015
I have a very simple bit of code.
SELECT dbo.MF_PATIENT.Forename,
dbo.MF_PATIENT.Surname,
dbo.MF_PATIENT.DOB,
dbo.MF_PATIENT.Postcode,
Count(dbo.MF_PATIENT.HEYNo) AS CountOfHEYNo
[Code] ....
My issue is I want to run this bit of code but only if dbo_MF_PATIENT.MFPatientID appears in any of the 3 tables below:
dbo_ED_ATTENDANCE, dbo_OP_APPOINTMENT, dbo_IP_ADMISSION
Suppose im unsure on the joining because there is only ever one patient in the
dbo_MF_PATIENT table but they could appear dozens of times in any of the other 3 tables.
View 18 Replies
View Related
Dec 16, 2007
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
View 5 Replies
View Related
Feb 28, 2008
I have an Employee table that has
EmployeeID (PK)
SupervisorID (which is really just another EmployeeID)
..random junk...
Now that part makes sense, everyone gets one and only one boss.
Their boss can change, and therefore the SupervisorID would be updated.
Now I have an EmployeeEvals table that has quarterly evaluation data.
I want to relate these two tables.
Eval table has
EvalID (PK)
ReviewedEmployeeID (the one being evaluated)
SupervisorID (the one doing the evaluation)
Now I need to link this back to the employee table (at least I think I do).
So I would want to relate it by the ReviewedEmployeeID going back to EmployeeID in the employee table and I also want the SupervisorID to do the same...
But of course that won't work because that would seem to indicate that a single record on the Employees table (say EmployeeID 55) should have a matching (or could) record in the Eval table that would look like
EvalID: 12345
ReviewedEmployeeID: 55
SupervisorID: 55
which of course wouldn't happen as an employee wouldn't evaluate themself.
How do I handle the relationships for this properly?
Do I just not link the SupervisorID back to anything?
View 2 Replies
View Related
May 6, 2008
I've recently finished an application for a small company with perhaps two hundred employees. Each employee was set up in a Users table in the database, against which application logins were processed.
For just about every other table in the database, other than pure lookup tables, we created columns to indicate the user who created the entry, and the user who last modified the entry. This was done using FK references back to the Users table. Each table contains two references back to the Users table, and there are over 150 tables now that follow this scheme. At first I was not concerned, other than the fact that it makes a visual picture of the data model look very confusing (almost every table has a pair of links back to the Users table), until I encountered an issue where I could no longer delete from the Users table. Upon surpassing 253 FK references to Users, I can no longer delete users, as the Query Optimizer can't complete the query.
Now, all of that so far is really not a big deal. Deleting users was never my intent anyway. The only real question I have is whether this is the standard way of maintaining history for table records. Have others used this method? Is there a better way?
View 1 Replies
View Related
May 1, 2006
Setup:
Using SQL 2005
TableA
ID INT PK
Name VARCHAR
RefID FK
ID Name RefID
1 Test1 <NULL>
2 Test1a 1
3 Test1b 1
4 Test1b1 3
5 Test1b1a 4
CREATE TRIGGER Update_ID
ON TableA INSTEAD OF UPDATE
AS
IF UPDATE(ID)
BEGIN
DECLARE @old_ID INT
DECLARE @new_ID INT
SELECT @old_ID = ID FROM DELETED
SELECT @new_ID = ID FROM INSERTED
UPDATE TableA
SET ID = @new_ID
WHERE ID = @old_ID
UPDATE TableA
SET RefID = @new_ID
WHERE RefID = @old_ID
END
SPROC:
UPDATE TableA
SET ID=6
WHERE ID=2
RefID is the FK to ID
ID is also the PK to another table and has the relationship use cascading Delete and Update.
Problem:
"The UPDATE statement conflicted with the SAME TABLE REFERENCE constraint"
This is referring to the first UPDATE of the Trigger.
It was my understanding that Instead Of checks the constraints after it is all done yet it errs with the first Trigger UPDATE.
I was expecting to overcome the self-reference constraint issue by using Instead Of and with the first Trigger UPDATE, change the ID(PK) and then change the RefID(FK) with the second Trigger UPDATE.
Once that was done, it should have not had any constraint problems. Thoughts?
Thanks, Nathan
View 7 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
Jul 30, 2015
If the id1 will change in table1 it should also change the corresponding id1 field in table2 it does not do anything.
CREATE TRIGGER [dbo].[IDCHANGE]
ON [dbo].[table1]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
View 1 Replies
View Related
Dec 23, 2013
We have two tables with names X and Y.
X has a,b columns. And Y has c,d columns.
I want to update b column in X table with the values from d column in Y table on condition X.a=Y.c.
View 3 Replies
View Related
May 30, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?
Hope it is clear for your help.
Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 4 Replies
View Related
Jul 14, 2014
We are in the conversion process of making the database ints.This is a change from a guid PK to an integer based PK that uses an int Identity. The program still uses the guid, and we are trying to map that guid to the databases int.We insert using TVPs passed from code. Since the identity is being set upon insert I have three things to accomplish:
1) Insert all the data into the dbo table
2) Update the parent Id in the table
3) Pass the SetsId guid, Sets_Id int, ParentSets_Id int back to the program
This is a high transaction table that will have a lot of records (millions).
--Sample table creation. There is a FK between Sets_Id to ParentSets_Id, Clustered PK on the Sets_Id
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JSets]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JSets](
[SetsID] [uniqueidentifier] NOT NULL,
[Sets_Id] bigint Identity (1,1) NOT NULL,
[code]...
View 0 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 15, 2008
Hi everybody,
I know that it is possible for one column to reference two different parent tables by defining two foreign keys on a same column, I have done it and SQL Server does give any error. But I want to know that is it advisable to define multiple foreign key on a column referncing two differnt parent table's primary key in differnt case... ? Means If Case 1 then It should reference to column1 or table1 otherwise it should reference to table2? How is it practicle...?
View 2 Replies
View Related
May 21, 2014
An example of what I am talking about is the employee table in the Adventureworks database. This has employeeID and then ManagerID, ManagerID just being the EmployeeID of the person whom the original reports to.
I know the queries for querying this type of data and even making recursive common table expressions. What I cannot seem to find is how one goes about BUILDING said table. I see all sorts of examples where people are just doing INSERT table VALUES () manually to load the table. The problem is, I need to create a table that has potentially thousands of records.
It will essentially be a dimensional map. Don't even get me started as to they why, I will just suffice to say that is what the client and project want . I have a process that will do this now, but it is not very dynamic and very hard coded. To me, there seems like there should be some sort of standardized methodology for handling this.
View 9 Replies
View Related
Jul 23, 2005
Hi SQL GurusCould anyone please explain how the following stored procedure can beamended to retrieve Subordinates in alphabetical order ?The example below simply retrives records in the order in which theywere entered.It sounds very easy but I can't sort it out ?The following code was taken from Narayana Vyas Kondreddi's website(http://vyaskn.tripod.com/index.htm)Consider the employee table of an organization, that stores all theemployee records. Each employee is linked to his/her manager by amanger ID.CREATE TABLE dbo.Emp(EmpIDintPRIMARY KEY,EmpNamevarchar(30),MgrIDintFOREIGN KEY REFERENCES Emp(EmpID))GOCREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)GOINSERT dbo.Emp SELECT 1, 'President', NULLINSERT dbo.Emp SELECT 2, 'Vice President', 1INSERT dbo.Emp SELECT 3, 'CEO', 2INSERT dbo.Emp SELECT 4, 'CTO', 2INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6INSERT dbo.Emp SELECT 12, 'Tester 1', 11INSERT dbo.Emp SELECT 13, 'Tester 2', 11INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7INSERT dbo.Emp SELECT 18, 'Tester 3', 17INSERT dbo.Emp SELECT 19, 'Tester 4', 17INSERT dbo.Emp SELECT 20, 'Tester 5', 17GOCREATE PROC dbo.ShowHierarchy(@Root int)ASBEGINSET NOCOUNT ONDECLARE @EmpID int, @EmpName varchar(30)SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpNameSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)WHILE @EmpID IS NOT NULLBEGINEXEC dbo.ShowHierarchy @EmpIDSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root ANDEmpID > @EmpID)ENDENDGOEXEC dbo.ShowHierarchy 1GO---President------Vice President---------CEO---------CTO------------Group Project Manager---------------Project Manager 1------------------Team Leader 1---------------------Software Engineer 1---------------------Software Engineer 2------------------Test Lead 1---------------------Tester 1---------------------Tester 2---------------Project Manager 2------------------Team Leader 2---------------------Software Engineer 3---------------------Software Engineer 4------------------Test Lead 2---------------------Tester 3---------------------Tester 4---------------------Tester 5
View 3 Replies
View Related
Apr 23, 2006
Hello All
I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.
declare @THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable
insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable
UPDATE @THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable
Update @THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable
- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt
UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt
FROM @THeader, mtblTransactionHeader
WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table
Thanks in advance
smHaig
View 1 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
Jun 5, 2007
Hi, all here,
As we are allowed to select one table as both case table and nested table, however what is the benefit of using one table as both case table and nested table? Thanks in advance for your advices.
I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 3 Replies
View Related
Jul 30, 2007
I'm creating a new Integration Services Project that copies data out of a SQL 7 server, transforms it, and places the data on a SQL 2005 (SP 2) Server. When defining a lookup transformation, if I specify an OLE DB Connection to my server running SQL 7 as the reference table, as soon as I click on the Colums tab, Visual Studio closes / crashes and dumps me to windows. I don't get an error message. If however I specify a connection to a server running SQL 8, or SQL 2005, no problems.
Is this supposed to happen?
My workstation is running Windows XP Pro SP2, Visual Studio 2005 Pro.
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00
The server that doesn't work for a reference table is running Windows 2000 Server SP4
SQL 7.00.623
Thanks for your help,
Kirk
View 6 Replies
View Related
Aug 29, 2007
I’m a complete newbie so forgive this question. If i have 2 tables Table 1 has the columns for NNAME, PHNUM, SSN, DOB, JOB, UPDATED however the Job description is emptyTable2 has the columns for SSN and JOB
The question is, how I write an update statement that looks at the SSN in table2 matches it to SSN in table 1 and fills in the job description for that person in table1 and adds yes to the UPDATED field.
I have somewhere in the neighborhood of 25,000 records to update.any help would be greatly appreciated.Thank YouAdam
View 2 Replies
View Related
Feb 10, 2005
If I have a table with three fields (A, B, C) and 'A' is a primary key, 'B' and 'C' do not have any content and another table with the same number of fields contains the same fields(A, B, C) and the fields do contain data.
I want to populate the first table with the data from the second table where the primary key 'A' is equal.
How would the sql work?
View 4 Replies
View Related
Dec 23, 2013
We have two tables with names X and Y.
X has a,b columns. And Y has c,d columns.
I want to update b column in X table with the values from d column in Y table on condition X.a=Y.c.
View 1 Replies
View Related
Jun 6, 2007
Hello all.
I have a temp table called #JOB which i know contains data (i can select from it and it shows about 30 rows). The problem is when i am trying to update another table with the contents of the temp table...........query analyser tells me no rows have been updated.
here is the code i am using:
UPDATE JOB
SET JOB.JOB_REF = #JOB.JOB_REF
FROM #JOB
Does anyone have any ideas where i am going wrong? Cheers in advance.
View 5 Replies
View Related
Apr 11, 2008
Hi,
I have source table , fact table and four dim. tables , I have to update a field in fact table from source table.
How can I do it?
thanks...
View 6 Replies
View Related
Dec 2, 2013
have two tables. One table "Seg" divides the day into 3 times segments for each studyID (SID). Each time segment start and ends at different times for each study ID.
Code:
SIDSeg1 Seg2 Seg3
109:00:0011:10:0014:00:00
209:30:0012:00:0016:00:00
Table 2 "Data" holds data collected at every minute for each study id. I would like to update this table (Data) so it specifies which time segment each minute falls into based on table "Seg". So it would have to read in the times from "Seg" and update the column Segment in "data" table to reflect which segment category it falls under. I would like my "data" table to look like this after the query:
Code:
SIDTime Segment
109:01:00Seg1
109:02:00Seg1
115:00:00Seg3
209:30:01Seg1
212:01:00Seg2
I know I can do this in Python using cursors, but can this be done in SQL?
View 3 Replies
View Related
Dec 28, 2006
I'm trying to update Field4 and Field5 of a TableToUpdate when some fields of Table_Guide are responding to a logic x
Table_Guide has Field1 and Field2 as primary key an so has TableToUpdate
I made the following ActiveX in the DTS transformation between
DTSsource and DTS destination
Functin Main()
'Pao: LA LOGICA DELLA TRASFORMAZIONE
if (DTSSource("PTF")) > "0" THEN
IF (DTSSource("SEGMENTO")) > "0" THEN
DTSDestination("CAMPO_N1") = DTSSource("SEGMENTO")
DTSDestination("PTF_CLIENTE_OLD") = DTSSource("PTF")
ELSE
if (DTSSource("SEGMENTO")) = "0" THEN
DTSDestination("CAMPO_N1") = DSDestination("SEGMENTO_CLIENTE")
DTSDestination("PTF_CLIENTE_OLD") = DTSSource("PTF")
ELSE msgbox "errore: segmento non valido, da sviluppare(inserisci il record scartato in una Tabella_Scartati)"
END IF
END IF
ELSE
IF (DTSSource("PTF")) = "0" THEN
IF (DTSSource("SEGMENTO")) > "0" THEN
DTSDestination("CAMPO_N1") = DTSSource("SEGMENTO")
DTSDestination("PTF_CLIENTE_OLD") = "0"
ELSE
if (DTSSource("SEGMENTO")) = "0" THEN
DTSDestination("CAMPO_N1") = TSDestination("SEGMENTO_CLIENTE")
DTSDestination("PTF_CLIENTE_OLD") = DTSDestination("PTF_CLIENTE_OLD")
ELSE msgbox "errore: segmento non valido, da sviluppare(inserisci il record scartato in una Tabella_Scartati) "
END IF
END IF
ELSE
msgbox "errore: PTF non valido, da sviluppare(inserisci il record scartato in una Tabella_Scartati) "
END IF
END IF
'END IF
'Main = DTSTaskExecResult_Success
Main = DTSTransformStat_OK
End Function
-----------
It works without errors but don't update. It simply add as much rows as Table_Guide
How can I do?
Alternatively, I try to write an update query but it seems I can't refer to Table_Guide in the -----update instruction...
Help!
Thank you.
View 1 Replies
View Related
