selective forward of datas - SQL Server Forums

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

Transact-SQL (2000)

selective forward of datas

Author

Topic

Finarfin
Starting Member

28 Posts

Posted - 2004-06-02 : 10:11:04

Hi all,

I want to fill a new table (table2) from an old one (table1).
table1: name1(primary key)
table2: code(unique), name2(primary key)

the code is the 3 first letters from name1,
and name2 = name1.

I tried

insert into Table2 (code, name)
select LEFT(name,3), name from Table1
where name NOT IN (select name from table2)

But the problem is that the code is not unique in this case.
for example, if:

Table1
---------
Bouboule
Isocarpe
Isocruche
Grafic

I will have:

Table2
---------
BOU Bouboule
ISO Isocarpe
ISO Isocruche
GRA Grafic

=> My problem is to differency the two ISO codes.

What do you think about it?

Thank you all

Finarfin
Starting Member

28 Posts

Posted - 2004-06-02 : 11:49:21

I forgot to say what I wanted like result:

Table2
-----------
BOU Bouboule
IS1 Isocarpe
IS2 Isocruche
GRA Grafic

Thank you all

Finarfin
Starting Member

28 Posts

Posted - 2004-06-03 : 03:57:43

Please help me, may be you need more informations... tell me if it is the case.

Thank you all

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-03 : 11:18:40

from the top of my head :))

put the data u want to insert into temp table with duplicate code values.

than update same code values in temp table, and insert data from temp table into the table2

and what happens if u have more than 9 same names or more than 99 same names? will your code be I18,I19,... or something different?

Go with the flow & have fun! Else fight the flow :)

Finarfin
Starting Member

28 Posts

Posted - 2004-06-03 : 11:26:14

I already tried that method, but isn't there anyway to avoid the tem table use?

Thank you all,
Romain

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-03 : 11:39:14

other than a cursor i doubt it... but i'm still young :)))

Go with the flow & have fun! Else fight the flow :)

Finarfin
Starting Member

28 Posts

Posted - 2004-06-03 : 11:42:42

ok thanx, i will see ...

Thank you all,
Romain