Problems with INSERT

Hello,
I have a problem with creating INSERT statement.
I have 3 tables, which look something like these:
PLANS
--
id_plan
...
TasksOfPlan
--
id_tasksofplan
id_plan
task
DETAILS
--
id_details
id_tasksofplan
date
hours
All ID's are autonumbers.
I want to write an INSERT statement, which create a new plan with the
same tasks and details like existing one with given id_plan.
For inserting data to TasksOfPlan for new plan (after creating record
for new plan in PLANS and getting it ID to @.plan) I did it:
insert TasksOfPlan ([id_plan],[id_task])
select @.id_plan_new, TasksOfPlan.[id_task]
FROM Plans INNER JOIN TasksOfPlan
ON Plans.id_plan = TasksOfPlan.id_plan
where Plans=@.plan
But I don't know how to insert data from DETAILS.
Thank you for any help!narina
Why no just to add id_plan column to the Details table .
"narina" <eeelllaaa@.gazeta.pl> wrote in message
news:1126086954.051806.310100@.g44g2000cwa.googlegroups.com...
> Hello,
> I have a problem with creating INSERT statement.
> I have 3 tables, which look something like these:
> PLANS
> --
> id_plan
> ...
> TasksOfPlan
> --
> id_tasksofplan
> id_plan
> task
> DETAILS
> --
> id_details
> id_tasksofplan
> date
> hours
> All ID's are autonumbers.
> I want to write an INSERT statement, which create a new plan with the
> same tasks and details like existing one with given id_plan.
> For inserting data to TasksOfPlan for new plan (after creating record
> for new plan in PLANS and getting it ID to @.plan) I did it:
> insert TasksOfPlan ([id_plan],[id_task])
> select @.id_plan_new, TasksOfPlan.[id_task]
> FROM Plans INNER JOIN TasksOfPlan
> ON Plans.id_plan = TasksOfPlan.id_plan
> where Plans=@.plan
> But I don't know how to insert data from DETAILS.
> Thank you for any help!
>|||hmm, but I don't know what that column will help me in?
'Details' table is related with 'TasksOfPlan', and 'TasksOfPlans' with
'Plan'. So, I can catch to id_plan from Details through TasksOfPlan.
(Maybe your solution is good, but I think today very slowly because of
lot of work, so I ask for patience ;)
I give an example:
PLANS
--
id_plan ...
1 plan1
TasksOfPlan
--
id_tasksof id_plan task
1 1 task1
2 1 task2
3 1 task55
...
DETAILS
--
id_details id_tasksof date hours
1 1 2005-05-05 6
2 1 2005-05-06 2
3 2 2005-05-05 2
4 3 2005-05-06 8
......
I make a new record for PLANS: id_plan=2. I'm inserting data for this
plan to TasksOfPlans, which are the same like for plan nr 1 but with
new id_plan=2, and I do it with statement INSERT which I wrote in first
post. And after that I should insert data to DETAILS for all tasks from
TasksOfPlan for plan nr 2 which are the same like for tasks in plan1,
but they have different id_tasksofplans.
(I hope that I write clearly ).|||check your statement it is not good, and use scope_identity or @.@.identity to
get indentity value for last inserted row.
"narina" <eeelllaaa@.gazeta.pl> wrote in message
news:1126086954.051806.310100@.g44g2000cwa.googlegroups.com...
> Hello,
> I have a problem with creating INSERT statement.
> I have 3 tables, which look something like these:
> PLANS
> --
> id_plan
> ...
> TasksOfPlan
> --
> id_tasksofplan
> id_plan
> task
> DETAILS
> --
> id_details
> id_tasksofplan
> date
> hours
> All ID's are autonumbers.
> I want to write an INSERT statement, which create a new plan with the
> same tasks and details like existing one with given id_plan.
> For inserting data to TasksOfPlan for new plan (after creating record
> for new plan in PLANS and getting it ID to @.plan) I did it:
> insert TasksOfPlan ([id_plan],[id_task])
> select @.id_plan_new, TasksOfPlan.[id_task]
> FROM Plans INNER JOIN TasksOfPlan
> ON Plans.id_plan = TasksOfPlan.id_plan
> where Plans=@.plan
> But I don't know how to insert data from DETAILS.
> Thank you for any help!
>|||On 7 Sep 2005 02:55:54 -0700, narina wrote:

>Hello,
>I have a problem with creating INSERT statement.
>I have 3 tables, which look something like these:
>PLANS
>--
>id_plan
>...
>TasksOfPlan
>--
>id_tasksofplan
>id_plan
>task
>DETAILS
>--
>id_details
>id_tasksofplan
>date
>hours
>All ID's are autonumbers.
>I want to write an INSERT statement, which create a new plan with the
>same tasks and details like existing one with given id_plan.
>For inserting data to TasksOfPlan for new plan (after creating record
>for new plan in PLANS and getting it ID to @.plan) I did it:
>insert TasksOfPlan ([id_plan],[id_task])
> select @.id_plan_new, TasksOfPlan.[id_task]
> FROM Plans INNER JOIN TasksOfPlan
> ON Plans.id_plan = TasksOfPlan.id_plan
> where Plans=@.plan
>But I don't know how to insert data from DETAILS.
>Thank you for any help!
Hi narina,
That's one of the downsides of using IDENTITY. :-)
Fortunately, it's easy to work around by using the real key. I'll assume
that the combination of (id_plan, task) is the real key for the table
TasksOfPlan (a shame you didn't post the complete DDL, as I'd have been
able to see the UNIQUE constraint - now, I have to guess).
First, the insert for TasksOfPlan. The code you posted won't even run,
since it uses non-existant columns. It also has an unneeded join. Here's
what I'd use (based on the coplumn names in the table description, not
on the column names in your INSERT statement - and assuming that
@.id_plan_new holds the new id_plan value, and @.id_plan_old holds the
id_plan value of the plan to be copied):
INSERT INTO TasksOfPlan (id_plan, task)
SELECT @.id_plan_new, task
FROM TasksOfPlan
WHERE id_plan = @.id_plan_old
Next comes the insert for the Details table. We will need a join this
time, to find the values for id_tasksofplan in the rows just added.
INSERT INTO Details (id_tasksofplan, [date], hours)
SELECT topNew.id_tasksofplan, d.[date], d.hours
FROM TasksOfPlan AS topOld
INNER JOIN Details AS d
ON d.id_tasksofplan = topOld.id_tasksofplan
INNER JOIN TasksOfPlan AS topNew
ON topNew.id_plan = @.id_plan_new
AND topNew.task = topOld.task
WHERE topOld.id_plan = @.id_plan_old
(both the above queries are untested - see www.aspfaq.com/5006 for the
reason why)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||hi Hugo,
thank you for your solution! it works ;)
sorry for not giving DDL, I will remember to give it next time.
I made mistakes in my first Insert (id_tasks instead of 'task'),
because my original tables and it's columns have different names, and I
wrote new names to make understanding easier.
regards,
narina