# Thread: Inserting a Count into a Table

1. Registered User
Join Date
Sep 2003
Posts
102

## Unanswered: 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.

thanks

2. Registered User
Join Date
Dec 2004
Location
CA, USA
Posts
63
What do 2.A and 2.B hold? Is 1.A and 1.B relevant?

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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.

4. Registered User
Join Date
Oct 2004
Location
In cousin's house
Posts
303
Try this but I not sure my coding.
INSERT INTO table1 SELECT DISTINCT *FROM table2

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
a counter, not a count

what sequence governs this counter, A and B?

could you perhaps explain why you want this weird data?

6. Registered User
Join Date
Oct 2003
Posts
357
Try this

Code:
```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```

7. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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')

8. Registered User
Join Date
Oct 2003
Posts
357
r937

try this with different combinations
Code:
```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```
Last edited by Madhivanan; 02-04-05 at 07:05.

9. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
sorry, that's not right either

add this to your data and see what happens --

insert into @t values('y','y','x','x')

10. Registered User
Join Date
Oct 2003
Posts
357
r937,

Can you post the expected outcome for these data?

Code:
```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')```

11. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

12. Registered User
Join Date
Sep 2003
Posts
102
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

13. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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'.

14. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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')

15. Registered User
Join Date
Sep 2003
Posts
102
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•