Too tricky for me! Can you help. Simplish question

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)

Too tricky for me! Can you help. Simplish question

Author

Topic

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2005-10-07 : 06:18:22

[code]
-- I have an import table like this one.
-- I will be inserting into the actual table.
-- The actual table has an identity column.
-- I'd like to do this with an insert statement and NOT loop through the items (that is easy!).
-- Question is, how???

--import table
census_return_item_id parent_census_return_item_id
--------------------- ----------------------------
-1 null
-2 -1
-3 -1
-4 -1
-5 null
-6 -5
-6 -5
-6 -5

--actual table -- census_return_item_id is an identity column
census_return_item_id parent_census_return_item_id
--------------------- ----------------------------
2 null
3 2
4 2
5 2
6 null
7 6
8 6
9 6
[/code]

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2005-10-07 : 06:21:11

Oh yes, and there'll be lots of nesting here (this is actually a very cut down representation of an xml file with parents and children).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-07 : 06:24:27

Did you mean this?

Insert into Table1(columns)
Select columns from Table2

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-07 : 06:27:27

insert into actual_table
(
parent_census_return_item_id
)
select
case when parent_census_return_item_id is not null then
parent_census_return_item_id * (0 - 1) + 1
else null
from
import_table
order by
census_return_item_id desc

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-07 : 06:30:56

insert into actual_table
(
parent_census_return_item_id
)
select
case when parent_census_return_item_id is not null then
parent_census_return_item_id * (0 - 1) + 1
else null end
from
import_table
order by
census_return_item_id desc

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-07 : 06:32:51

ahhh yes - thanks for that my freind

- don't forget the end.

Duane.

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2005-10-11 : 08:20:07

Thanks very much I got there!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources