May 2, 2011 at 2:00 am
Hi everyone,
I have currently the problem that I want to make an data import from one source table. I have the following definition of tables:
create table SourceTable(
SourceTableID int not null primary key,
Name nvarchar(50)
)
insert into SourceTable
select 1,
'test'
create table TargetTable(
TargetTableID int identity(1,1) not null primary key,
Name nvarchar(50)
)
create table TargetConnection(
TargetTableID int not null,
SourceTableID int not null
)
I want to import all information columns of SourceTable into TargetTable but without taking SourceTableID also in this table. But I also want to add the connection to the SourceTable in an extra table TargetConnection.
The SourceTable and the TargetTable will have more columns and therefore a linking of the information columns isn't an option for me.
I have tried it with an insert and the output clause but it wouldn't work. I tried it with the following statement:
insert into TargetTable(
Name
)
output inserted.TargetTableID,
st.SourceTableID
select st.Name
from SourceTable st
Unfortunably the output clause doesn't support columns from the "from-part".
Has anyone a nice solution?
Thanks a lot.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
www.fiedler-sql-consulting.de
[/font]
May 2, 2011 at 2:28 am
A 2-part INSERT might help you..
First INSERT - SourceTable data (only Name col) into Target
Second INSERT - JOIN SourceTable and TargetTable and insert into TargetConnection..
May 2, 2011 at 2:36 am
ColdCoffee (5/2/2011)
A 2-part INSERT might help you..First INSERT - SourceTable data (only Name col) into Target
Second INSERT - JOIN SourceTable and TargetTable and insert into TargetConnection..
Thanks for the fast answer, I have this solution already implemented but I am unhappy with this. Imagine that there is a source table with more than 50 columns its more work to write the code and to maintain the code when the source table columns are changing or their data types.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
www.fiedler-sql-consulting.de
[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply