Re: generate sequence col on existing table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 04/04/04 

Date: Sun, 4 Apr 2004 13:19:07 +0530

hi john,

try insert query as shown in the following example.

--source table
create table table1(Col1 char(7),Col2 char(7))
insert into table1 (col1)
select 5550001 union all
select 5550007 union all
select 5550101

--destination table.
create table table2(Col1 char(7),Col2 char(7))

--insert statement
--(somehow concatenation operator (+) has got missed in earlier post.
however, try following.)

INSERT INTO Table2
(Col1,Col2)
select col1,'666' + right('0000' +
cast ((select count(*) from table1 a where LEFT(Col1,3) = '555'
       and a.col1 < table1.col1) as varchar(5)), 4)
from table1
WHERE (Col1 IS NOT NULL)
and LEFT(Col1,3) = '555'

--check the data
select * from table2 

--
Vishal Parkar
vgparkar@yahoo.co.in

Relevant Pages

  • Re: Next step in using Equivalent of a Full Outer Join
    ... second half of the union. ... from table1 left join table2 ... problem with that is it seems on becuase that would state a many to ...
    (microsoft.public.access.queries)
  • RE: where Klausel
    ... > kann mir einer sagen, wie ich einen right/left join in einer where Klausel ... CREATE TABLE #Table1 ... CREATE TABLE #Table2 ... UNION ALL ...
    (microsoft.public.de.sqlserver)
  • Re: ProCobol Outer join
    ... from Table1 A, Table2 B ... UNION for the select with the matching criteria together with a select ...
    (comp.lang.cobol)
  • Re: Display Changed Records
    ... that last clause should have been: ... -- Also use table1 values to report rows deleted from table2 ... UNION ALL ...
    (microsoft.public.sqlserver.programming)
  • Re: Optimizing SQL - Union
    ... select 'col1' as field, col1 as val from table1 ... select 'col2' as field, ...
    (comp.databases.ms-sqlserver)