May 30, 2012 at 2:16 pm
I have to do a complex insert statement from one source table, which defines the relationship between data) to two destination tables. It is complex because the value I insert into ProjID of destinationB has to match the ID value in an already populated destinationA table based on logic run against OriginalTable source table.
Will this require dynamic sql?
To simulate my enviroment I've created three tables:
OriginalTable contains data to be moved and defines the relationships between data
destinationB is empty
destinationA has data
rules I must adhere to:
for each DataTwelve column value found in destinationA table (eg. bike)
(1) get it's DataOne value from OriginalTable (eg. red)
(2) Insert it into DataOne column in destinationB table
(3) and also Insert 'red's corresponding destinationA.ID (eg. 1) value into destinationB.ProjID column
you will note that projID is not an identity column and will accept inserts.
---currently the tables look like this
declare @OriginalTable table
(StuffID int
, DataOne varchar(20)
, Channel varchar(20)
, DataTwelve varchar(20));
insert into @OriginalTable
select 16, 'red', 'distxyz', 'bike'
union all select 25, 'blue', 'distabc', 'auto';
declare @destinationB table
(ID int identity
, projID int
, DateOne varchar(20));
declare @destinationA table
(ID int primary key
, DataTwelve varchar(20));
insert into @destinationA
select 1, 'bike'
union all select 23, 'auto';
select * from @OriginalTable;
select * from @destinationB;
select * from @destinationA;
---when destinationB is populated it will look like this
declare @OriginalTable table
(StuffID int
, DataOne varchar(20)
, Channel varchar(20)
, DataTwelve varchar(20));
insert into @OriginalTable
select 16, 'red', 'distxyz', 'bike'
union all select 25, 'blue', 'distabc', 'auto';
declare @destinationB table
(ID int identity
, projID int
, DateOne varchar(20));
insert into @destinationB
select 1, 'red'
union all select 23, 'blue';
declare @destinationA table
(ID int primary key
, DataTwelve varchar(20));
insert into @destinationA
select 1, 'bike'
union all select 23, 'auto';
select * from @OriginalTable;
select * from @destinationB;
select * from @destinationA;
---DDL
create table OriginalTable
(StuffID int
, DataOne varchar(20)
, Channel varchar(20));
insert into OriginalTable
values
(16, 'red', 'distxyz'),
(25, 'blue', 'distabc');
create table destinationB
(ID int identity
, projID int
, DateOne varchar(20));
alter table destinationB
add foreign key (ID) references destinationA
create table destinationA
(ID int primary key
, DataTwelve varchar(20));
insert into destinationA
values
(1, 'bike'),
(23, 'auto');
--Quote me
May 30, 2012 at 2:25 pm
I'm sorry... but I don't see a question. 🙂 EDIT: I see the question now:
Will this require dynamic SQL?
My guess is no, but I have to look it over once more.
Jared
CE - Microsoft
May 30, 2012 at 2:34 pm
Is this a 1-time migration of data or something that has to be done regularly? I believe you can just do a separate insert into each table. You are basically splitting 1 table into 2 to normalize the data?
Jared
CE - Microsoft
May 30, 2012 at 2:37 pm
yes, this will be one time and I am seperating the migrations into two batches. destination A is already populated, but destinationB is populated based on more complex logic. That is the crux of my question.
How to populate destinationB based on relationships defined in OriginalTable AND ID value in destinationA table associated with DataTwelve column.
--Quote me
May 30, 2012 at 2:43 pm
Ok, so this?
INSERT INTO @destinationB (ProjID, DataOne)
SELECT a.ID, o.DataOne
FROM @destinationA a
INNER JOIN @OriginalTable o
ON a.DataTwelve = o.DataTwelve
Jared
CE - Microsoft
May 30, 2012 at 2:58 pm
thanksk Know-It-All.
that worked great. Let me apply what you just taught me to my real life scenario. Thanks a lot!
Note*
I had to correct my FK constraint to make it work. But, this is how it is supposed to be anyway:
alter table destinationB
add foreign key (ProjID) references destinationA (ID)
--Quote me
May 30, 2012 at 3:00 pm
Glad to help!
Jared
CE - Microsoft
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply