copying a column's data to another table/column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

copying a column's data to another table/column

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-07-30 : 19:31:40

I'm not really sure how to go about doing this but if someone can help me out or point me in right direction.

I would like the column 'rev' in the table 'iso_employwi' to automatically be populated by referencing the column 'rev' in the table 'iso_wi'. So in other words, when an insert is done on table 'iso_employwi', it fills the 'rev' column by matching the 'wi' column from the table 'iso_wi'.

Table: iso_wi
Primary Key: wi

wi | wi_title | rev

Table: iso_employwi
Primary key: empWiD

empWiD | pin | wi | rev

The table 'iso_employwi' would look like this if it can be done:

empWiD | pin | wi | rev

001 | 1 | WI001 | B
002 | 1 | WI002 | A
003 | 2 | WI001 | B

etc

using SQL2000

JLM

Kristen
Test

22859 Posts

Posted - 2004-07-31 : 02:06:01

You could create an INSERT & UPDATE trigger on iso_employwi that performs an UPDATE on iso_wi

Something like:


CREATE TRIGGER dbo.iso_employwi_TR
ON dbo.iso_employwi
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON

	UPDATE	iso_wi
	SET	rev = iso_wi.rev
	FROM	inserted I
		JOIN dbo.iso_wi
			 ON iso_wi.wi = I.wi
GO

Kristen

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-08-02 : 12:43:21

Thanks Kristen,
I am not getting the results I was looking for. Maybe I didn't explain myself too good. =(

After I insert or update a specific employee record, the trigger is getting the correct value from 'iso_wi.rev', but it's updating all the rows in the column 'iso_employwi.rev' with that data even if 'iso_wi.wi' are not equal. I only want to update the record(s) where '.wi' matches from both tables.

Here is the results on what it's doing:

empWiD | pin | wi | rev

001 | 1 | WI001 | B
002 | 1 | WI002 | B
003 | 2 | WI001 | B
004 | 2 | WI034 | B
005 | 2 | WI120 | B
006 | 3 | WI002 | B

And it should look like this:

001 | 1 | WI001 | B
002 | 1 | WI002 | A
003 | 2 | WI001 | B
004 | 2 | WI034 | D
005 | 2 | WI120 | H
006 | 3 | WI002 | A

An example of what the 'iso_wi' table looks like:

wi | wi_title | rev
WI001 | Test A | B
WI002 | Test B | A
WI001 | Test C | B
WI034 | Test D | D
WI120 | Test E | H
WI002 | Test F | A

Also, could you help me with another trigger (or would it be setting up a relationship?). When an update is done in 'iso_wi', I would like it to also update all the matching data in the table 'iso_employwi'. So if the columns 'wi or rev' in 'iso_wi' get updated, it should also make the same change in 'iso_employwi'.

JLM

Update:

I setup a relationship between both tables but I can't figure out how to make it work for two sets of columns. I only have it working for the 'wi' columns bewteen both tables.

******
ALTER TABLE [dbo].[iso_employwi] ADD
CONSTRAINT [FK_iso_employwi_iso_wi] FOREIGN KEY
(
[wi]
) REFERENCES [dbo].[iso_wi] (
[wi]
) ON DELETE CASCADE ON UPDATE CASCADE
******

So if I delete a row, or update a field in the column 'wi' in iso_wi, then the other table 'iso_employwi' also gets update which is what I want. If I can only get it to also do the 'rev' area then maybe I don't need triggers, right? I'm just not sure how to go about doing this. =(

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-08-05 : 13:16:40

Can anyone else help me figure this out. I'm still trying different things but they just don't seem to work. :/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources