About the good''ole Junction Table...
-
ok...
i'm trying to create junction tables/composit keys as a matter of practice.
apparently i'm not that good at it, and well put simply... i 'want' to be
better at it.
just looking for some help with it from the SQL community.
lets say you have 2 tables. table1 & table2.
table1 (col1[pk], col2, col3) values are what ever you want.
table2 (colA, colB, colC) values are what ever you want.
here is where i want to create a junction table, and it's subsequent
composite key.
after that is completed... i would simply like to populate values from table1 & table2.
how would the 'insert' operation go exactly?
_________________________
-
I did this pretty quickly so I'm not sure if it's exactly what you're looking for. You can plug variables into the where clauses to make it a little more flexible.
create table table1
(col1 int identity(1,1) not null,
col2 varchar(10) null,
col3 varchar(20) null
)
truncate table table1
GO
insert into table1 values ('ABC', 'BigText')
insert into table1 values ('DEF', 'MoreBigText')
insert into table1 values ('GHI', 'LessBigText')
insert into table1 values ('JKL', 'ThisBigText')
insert into table1 values ('MNO', 'ThatBigText')
create table table2
(colA int identity(1,1) not null,
colB varchar(10) null,
colC varchar(20) null
)
truncate table table2
GO
insert into table2 values ('abc', 'BigValue')
insert into table2 values ('def', 'MoreBigValue')
insert into table2 values ('ghi', 'LessBigValue')
insert into table2 values ('jkl', 'ThisBigValue')
insert into table2 values ('mno', 'ThatBigValue')
create table tableXREF
(XrefID int identity(1,1) not null,
col1 int not null,
colA int not null
)
alter table dbo.tableXREF add constraint
IX_tableXREF unique nonclustered
(
col1,
colA
 
on [PRIMARY]GO
insert into tableXREF
(col1, colA)
select t1.col1,
t2.colA
from (select col1 from table1 where col2 = 'ABC') as t1
cross join (select colA from table2 where colB = 'ghi') as t2
Hope this helps!!!
MarkA
-
oh man... thanks! the picture is starting to clear up now.
question though... i'm not that good at tsql, and would
like to know why this is all about --> identity(1,1)
i know what (int identity) is for, but not aware of the "(1,1).
what is that doing exactly?
by the way... thanks for the join statement. i'm still working
those out now.
i also notice you ar running a truncation after you create each table. why is that?
_________________________
-
identity(1,1)
Identity seed (=start) = 1 (first parameter)
Increase by 1 (second)
-
don't think i like joins. trying
to know them better at the moment.
they are critical. it's the only
type of operation that makes many-to-many
worth-while.
_________________________
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply