Adding sequenced records - SQL Server Forums

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)

Adding sequenced records

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-23 : 10:37:20

Sarcos writes "I'm needing to update a database table (dbo_inc_act) with values from another table (dbo_N_incident). The problem I'm running into is that the table containing the data has three separate fields (type_of_action_1, type_of_action_2, Type_of_action_3) that need to be added to the dbo_inc_act table but with a defining sequence number of either 1, 2, or 3. Example: In dbo_inc_act.code I need to insert FROM dbo_N_incident type_of_action_1 with sequence of 1, type_of_action_2 with sequence of 2 and so forth...
Moreoever, to complicate matters the table I'm updating may contain some or all of the values I'm attempting to update. So I need to integrate some form of logic to verify that each of the codes exist or not - if so ignore, if not add.

Here are the table example values for a better undertanding:
dbo_inc_act
inci_id sequence code
_0ZB0U8AHE 1 41
_0ZB0U8AHE 2 42
_0ZB0U8AHE 3 55

dbo_N_incident
inci_id type_of_Action_1 type_of_Action_2 type_of_Action_3
_0ZB0VJ75H 11 21 86

Any feedback or direction on this matter would be greatly appreciated! I'm sure I'm making a bigger deal out of this than need be but I'm beating my head on the desk trying to figure it out."

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-23 : 10:48:53

It *sounds* like you want to use the CASE statement, but I would need to see a bit more... can you show your query (or pseudo-code), and the table structures ?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

sarcos
Starting Member

1 Post

Posted - 2004-06-23 : 12:00:06

Below is the query code that I have thus far. It's generating errors for me and I'm not certain as to why.
I placed the tables in the original post but the field names seem to have run together. FYI, there are no spaces in any of the field or data values. All field data types are text with length of 10, save sequence which is a data type of number with a length of 'long integer'.

Here is the code that I have thus far:

INSERT into dbo_inc_act (inci_id, sequence, code) 
SELECT A.inci_id, 1, A.type_of_Action_1 from dbo_N_incident as A
UNION
SELECT  B.inci_id, 2, B.type_of_Action_2 from dbo_N_incident as B
UNION
SELECT C.inci_id, 3, C.type_of_Action_3 from dbo_N_incident as C

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-23 : 12:54:01

What error does it give you??

Corey

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-24 : 07:22:21

hmm - I don't see a specific problem.


set nocount on
go
create table dbo_n_incident
(inci_id varchar(10),
type_of_Action_1 varchar(10),
type_of_Action_2 varchar(10),
type_of_Action_3 varchar(10),
)
go
create table dbo_inc_act
(inci_id varchar(10),
[sequence] bigint,
code varchar(10)
)
go



insert into dbo_n_incident values ('i1','i1a1','i1a2','i1a3')
insert into dbo_n_incident values ('i2','i2a1','i2a2','i2a3')
insert into dbo_n_incident values ('i3','i3a1','i3a2','i3a3')
insert into dbo_n_incident values ('i4','i4a1','i4a2','i4a3')
go

INSERT into dbo_inc_act (inci_id, sequence, code) 
SELECT A.inci_id, 1, A.type_of_Action_1 from dbo_N_incident as A
UNION
SELECT  B.inci_id, 2, B.type_of_Action_2 from dbo_N_incident as B
UNION
SELECT C.inci_id, 3, C.type_of_Action_3 from dbo_N_incident as C

select * from dbo_inc_act

drop table dbo_n_incident
drop table dbo_inc_act

--I get the following results
inci_id    sequence             code       
---------- -------------------- ---------- 
i1         1                    i1a1
i1         2                    i1a2
i1         3                    i1a3
i2         1                    i2a1
i2         2                    i2a2
i2         3                    i2a3
i3         1                    i3a1
i3         2                    i3a2
i3         3                    i3a3
i4         1                    i4a1
i4         2                    i4a2
i4         3                    i4a3

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources