Insert with different rows Problem

[Procrash]

Hi, I want to insert different values from different rows which I get from a select statement into a table. The Problem is that all rows have the same data type. But I want to get the values of one row to another. Sounds complicated but if you see my example you probably know what I mean.

Initiating Table should look like this
bigint bigint bigint bigint
ROW A B C D
1 2 3 4

=> This entry should be added from the 1st line
4 1 2 3

next one would be like this:
3 4 1 2
Every line should be generated from a select statement of line before.

Who knows how to do this and why is a statement like this not possible?

insert into test (A,B,C,D) Values (select top 1 B from test where A=1,
select top 1 C from test where A=1
select top 1 D from test where A=1
select top 1 A from test where A=1
);

 

[rravenn]

Do you need all combinations?
Here's what I quickly produced, you can create table once or create temp table each time.
User insert into .... (...) select ... syntax to insert into final table.

/*create table Numbers (num int)

insert into numbers (num) values (1)
insert into numbers (num) values  (2)
insert into numbers (num) values  (3)
insert into numbers (num) values  (4)
*/

select n1.num,n2.num,n3.num,n4.num
from numbers n1
	cross join numbers n2 
	cross join numbers n3 
	cross join numbers n4

 

[Procrash]

Nope that's not the point.
The example was too bad sorry.
I do not want to get permuations or sth. like that.
I want to copy the contents of different rows to other rows.
To be a bit more specific i have timestamp that should be copied. But not all rows should be copied only some.

For example:

Identifier|Start datetime |end datetime | Hours worked
WORKA |20.10.2005 11:00|20.10.2005 13:00 | 2
WORKB |20.10.2005 13:00|20.10.2005 16:00 | 3
...

So the problem is:

First copy the end time from enddatetime to starttime row in the next entry. And modify Identifier field to WORKB and
calculate Horsworked time newly. enddatetime should be given from userinput or sth. like that.

I wanted to pack this into one single sql statement which I
execute from a C-Coded Program.

My aim is to stay as far as possible in sql code to reduce date conversions or sth. like that....

 

[rravenn]

Ok.
In this query I assume ID is auto increment, and that there's no distinction between the rows in the table, e.g. all works follow one another subsequently and belong to the same "group" if that's not the case and the table stores stuff for many users, add it to where clause of the select statement.

Insert into Works (Start,End,Hours)
SELECT TOP 1 End,'...User entered date..',DATEDIFF(hour,End,'...user entered date...') FROM Works

 

[rravenn]

err... ORDER BY ID desc