Re: insert rows : generating sequence numbers

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Tenaya (ct_at_ct.ct)
Date: 04/04/04 

Date: Sun, 4 Apr 2004 07:55:28 -0400

John,

Well, never say die :-)

If you are willing to create an auxiliary table, then there is a
set-oriented approach.

For the sake of simplicity and clarity, I've eliminated the character
manipulation part of the code, and have simply concentrated on what I
believe is the difficult part ... namely, backfilling sequence numbers. I've
also created views to (substantially) simplify the code. Views are never
necessary, but can often times be very helpful in understanding the logic
... and, I think, particularly in this case:

create table Numbers (n int NOT NULL primary key)
go

insert into Numbers values (0)
insert into Numbers values (1)
insert into Numbers values (2)
insert into Numbers values (3)
insert into Numbers values (4)
insert into Numbers values (5)
insert into Numbers values (6)
insert into Numbers values (7)
insert into Numbers values (8)
insert into Numbers values (9)
go

create table Table1 (Col1 int NOT NULL unique)
create table Table2
(
 Col1 int NOT NULL,
 Col2 int NOT NULL
)
go

insert into Table2 values (2, 2)
insert into Table2 values (4, 4)

insert into Table1 values (3)
insert into Table1 values (5)
insert into Table1 values (100)
insert into Table1 values (111)
go

create view linear_Table1 as
  select Col1, s = (select count(*)
                    from Table1 as t2
                    where t2.Col1 < t1.Col1)
  from Table1 as t1
go

create view backfill_Numbers as
  select backfill = n
  from Numbers
  where not exists (select *
                    from Table2 as t2
                    where n = t2.Col2)
go

create view seq_backfill_Numbers as
  select backfill,
         n = (select count(*)
              from backfill_Numbers as b2
              where b2.backfill < b1.backfill)
  from backfill_Numbers as b1
go

insert into Table2
  select t1.Col1, b.backfill
  from linear_Table1 as t1
  join seq_backfill_Numbers as b
    on (t1.s = b.n)
go

select * from Table2
go

Chief Tenaya

"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:%23MsAnmhGEHA.712@tk2msftngp13.phx.gbl...
> Regarding generating sequence numbers ... if the table inserted into
> (Table2) has pre-existing rows , then the problem becomes more complex ...
>
> I need to insert into Table2 from Table1.
>
> Table1 contains some rows where Table1.Col1 char(7) matches the pattern
> '555mmmm' -- but 'mmmm' is not in an increment-by-1 sequence (sample
data
> : '5550001' , '5550017' , '5550100' )
>
> Table2 contains some rows where Table2.Col2 char(7) matches the pattern
> '666nnnn' -- but 'nnnn' is not in an increment-by-1 sequence (sample
data
> : '5550001' , '5550017' , '5550100' )
>
> I need to insert one Col2 = '666nnnn' row into Table2 for each Col1 =
> '555mmmm' row in Table1 -- and I need to sequentially fill the Col2
> sequence gaps , in the order given by Col1
>
> sample data :
>
> Table2.Col2
> '6660002'
> '6660004'
>
> Table1.Col1
> '5550003' -- want to translate to Table2.Col2 = '6660000'
> '5550005' -- want to translate to Table2.Col2 = '6660001'
> '5550100' -- want to translate to Table2.Col2 = '6660003'
> '5550111' -- want to translate to Table2.Col2 = '6660005'
>
>
>

Relevant Pages

  • Re: need help with LEFT JOIN statemen
    ... DECLARE @Table1 TABLE (Col1 int) ... DECLARE @Table2 TABLE ...
    (comp.databases.ms-sqlserver)
  • Re: Not In and Not Exists
    ... > SQL Server will try to optimize query plans so neither technique ought to ... Col1 int NULL ... > INSERT INTO Table1 VALUES ... > INSERT INTO Table2 VALUES ...
    (microsoft.public.sqlserver.programming)
  • Re: simple stored procedure
    ... CREATE TABLE table1 (col1 int) ... INSERT INTO table1 VALUES ... CREATE TABLE table2 ...
    (microsoft.public.inetserver.asp.db)
  • Re: Tricky Visual Basic Code help...
    ... moves the current record into a new record in table2. ... this with a little VBA code and a couple custom queries. ... Set a "pushFlag" column in the record in table1 to a specific ... If a duplicate is not found, ...
    (microsoft.public.access.formscoding)
  • Re: Help needed with importing XML
    ... You could add a gratuitous identity column to #temp and use ... Those examples showed how to decompose arbitrary XML in multiple ... table1 and table2. ... But there has to be something in the relational schema tying table1 ...
    (microsoft.public.sqlserver.xml)