Inserting a Count into a Table

I am inserting data from one table into another. To simplify:

Table 1 has columns:
1.A, 1.B, 1.C, 1.D

Table 2 has columns
2.A, 2.B, 2.C, 2.D, 2.Tag

where 2.Tag is a counter of all unique combinations of 1.C and 1.D.
Not a count of how many records for each combination.

Therefore, if Table 1 is

x - x - x - x
x - x - x - x
x - x - x - x
x - x - x - y
x - x - x - z

then, table 2 is

x - x - x - x - 1
x - x - x - y - 2
x - x - x - z - 3

anyone help me w/ the sql on that ?

i was thinking of putting a variable in the select statement, but was unable to increment it.

thanksWhat do 2.A and 2.B hold? Is 1.A and 1.B relevant?|||This could be done in a single select statements, but I refuse to think about it further until you explain why you would want to do such a loopy thing.|||Try this but I not sure my coding.
INSERT INTO table1 SELECT DISTINCT *FROM table2|||a counter, not a count

what sequence governs this counter, A and B?

could you perhaps explain why you want this weird data?|||Try this

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
Select Distinct *,Tag=(Select count(*) from (Select Distinct D from @.t) T1 where T1.D<=T2.D) from @.t T2

Madhivanan|||madhivanan, very nice try, but not quite right

add the following to your test data and see what happens :) :) :)

insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')|||r937

try this with different combinations

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

Select Distinct *,Tag=(Select count(*) from (Select Distinct * from @.t) T1
where T1.A+T1.B+T1.C+T1.D<=T2.A+T2.B+T2.C+T2.D) from @.t T2

Madhivanan|||sorry, that's not right either ;)

add this to your data and see what happens --

insert into @.t values('y','y','x','x')|||r937,

Can you post the expected outcome for these data?

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

Madhivanan|||sure

x x x x 1
x x x x 2
x x x x 3
x x x y 1
x x x z 1
x x y y 1
x x z y 1|||Purpose of craziness: i want to order the data when i select from that table.

for the below
Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

the new table should have:

x-x-x-x-1
x-x-x-y-2
x-x-z-y-3|||First, I don't understand why your output isn't:

x-x-x-x-1
x-x-x-y-2
x-x-x-z-3
x-x-y-y-4
x-x-z-y-5

Second, if you are relying on the order of the data in the table for your logic, you are letting yourself in for a heap-o-hurtin'.|||aargh, i think my understanding of this has been wrong all along

it should be like in post #13!!

okay, what about if you add these rows, then what do you get --

insert into @.t values('b','b','x','y')
insert into @.t values('c','c','x','z')|||oh ya..
1. the order should be that...(sorry, it is a friday)

2. i want it ordered as such when i do an extract and display into a report.l

thanks!
C