• Replies 3 replies
  • Subscribers 336 subscribers
  • Views 88 views
  • Users 0 members are here
Related

Bulk update of t_eaddress

Graeme Ellis
$organization

Hello everyone,

I'm preparing to do a bulk update of our t_eaddress table and have tried the following code to add a new e-mail, however, it runs OK but when I try to look at t_eaddress I can't load the table.

USE

 

[impresario]
GO

set

 

ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

declare

 

@eaddress_no int

-- get a new address id

Begin

 

Transaction
--insert into t_eaddress
insert [dbo].t_eaddress(
   eaddress_no,
   customer_no,
   eaddress_type,
   address,
   start_dt,
   end_dt,
   months,
   primary_ind,
   inactive,
   market_ind,
   alt_signor,
   mail_purposes,
   create_loc,
   created_by,
   create_dt,
   last_updated_by,
   last_update_dt,
   html_ind)

values

 

(
   @eaddress_no,
   '95265',
   '9',
   'test@youngvic.org',
   NULL,
   NULL,
   'YYYYYYYYYYYY',
   'N',
   'N',
   'Y',
   NULL,
   NULL,
   'YVD024',
   'gellis',
   '2011-04-06 12:00:00.000',
   NULL,
   NULL,
   'Y');

exec @eaddress_no = [dbo].ap_get_nextid_function @type = 'AD'
select @eaddress_no

Am I missing something really obvious?

Thanks ever so much for your help

Graeme

Young Vic Theatre

Parents
  • $organization

    Forgetting the guts of the query itself, the reason you can't load the table is because you have started the transaction with a BEGIN TRANSACTION statement without a corresponding COMMIT TRANSACTION at the end.  It puts the transaction in a temporary state of being neither committed nor rolled back.  You could either say COMMIT TRANSACTION at the end or exclude the BEGIN TRANSACTION.

    http://msdn.microsoft.com/en-us/library/ms188929.aspx

    -Michael

  • Former Member
    Former Member $organization in reply to Michael Reisman

    .. And in the script itself, you need to get the next_id before you INSERT the row.

    If you do it something like this below, you can keep checking the results until you're happy, without changing anything; and then comment the 'Rollback transaction' line out and uncomment the 'commit transaction' line when you're ready to make the result permanent.

    -----------------------------------------------------------------------

    USE [impresario]

    GO

    set

     

    ANSI_NULLS ON

    set

     

    QUOTED_IDENTIFIER ON

    go

     

     

    declare

     

    @eaddress_no int

    Begin

     

    Transaction

    -- get a new address id

    exec

     

    @eaddress_no = [dbo].ap_get_nextid_function @type = 'AD'

    insert

     

    [dbo].t_eaddress

    (eaddress_no,

    customer_no

    ,

    eaddress_type

    ,

     

     

    address,

    start_dt

    ,

    end_dt

    ,

    months

    ,

    primary_ind,

    inactive

    ,

    market_ind

    ,

    alt_signor

    ,

    mail_purposes

    ,

    create_loc

    ,

    created_by

    ,

    create_dt

    ,

    last_updated_by

    last_update_dt

    ,

    html_ind)

    values 

     

    (

    @eaddress_no,

     

     

    '2',

     

     

    '9',

     

     

    'test@youngvic.org',

     

     

    NULL,

     

     

    NULL,

     

     

    'YYYYYYYYYYYY',

     

     

    'N',

     

     

    'N',

     

     

    'Y',

     

     

    NULL,

     

     

    NULL,

     

     

    'YVD024',

     

     

    'gellis',

     

     

    '2011-04-06 12:00:00.000',

     

     

    NULL,

     

     

    NULL,

     

     

    'Y');

    -- run a quick select to check the result

    select  * from t_eaddress where eaddress_no = @eaddress_no

    -- then roll it back

    rollback

     

    transaction

    --commit transaction

    ----------------------------------------------------

     

     

     

    Ken

     

Reply
  • Former Member
    Former Member $organization in reply to Michael Reisman

    .. And in the script itself, you need to get the next_id before you INSERT the row.

    If you do it something like this below, you can keep checking the results until you're happy, without changing anything; and then comment the 'Rollback transaction' line out and uncomment the 'commit transaction' line when you're ready to make the result permanent.

    -----------------------------------------------------------------------

    USE [impresario]

    GO

    set

     

    ANSI_NULLS ON

    set

     

    QUOTED_IDENTIFIER ON

    go

     

     

    declare

     

    @eaddress_no int

    Begin

     

    Transaction

    -- get a new address id

    exec

     

    @eaddress_no = [dbo].ap_get_nextid_function @type = 'AD'

    insert

     

    [dbo].t_eaddress

    (eaddress_no,

    customer_no

    ,

    eaddress_type

    ,

     

     

    address,

    start_dt

    ,

    end_dt

    ,

    months

    ,

    primary_ind,

    inactive

    ,

    market_ind

    ,

    alt_signor

    ,

    mail_purposes

    ,

    create_loc

    ,

    created_by

    ,

    create_dt

    ,

    last_updated_by

    last_update_dt

    ,

    html_ind)

    values 

     

    (

    @eaddress_no,

     

     

    '2',

     

     

    '9',

     

     

    'test@youngvic.org',

     

     

    NULL,

     

     

    NULL,

     

     

    'YYYYYYYYYYYY',

     

     

    'N',

     

     

    'N',

     

     

    'Y',

     

     

    NULL,

     

     

    NULL,

     

     

    'YVD024',

     

     

    'gellis',

     

     

    '2011-04-06 12:00:00.000',

     

     

    NULL,

     

     

    NULL,

     

     

    'Y');

    -- run a quick select to check the result

    select  * from t_eaddress where eaddress_no = @eaddress_no

    -- then roll it back

    rollback

     

    transaction

    --commit transaction

    ----------------------------------------------------

     

     

     

    Ken

     

Children
No Data