Bulk insert cell phone numbers

Well here is two approaches I quickly wrote for you. A loop and a straight bulk insert. Before ever doing this I would highly recommend you test, test, test. I only ran it just a couple times with a hew adhoc of phones (Both ran fine.).This example assumes you are going to use the import task in ssms and create a new local table to select from. 

I create a preview parameter so you can see what would be inserted into t_phone. Y = Preview and N = will insert. The @phone_id parameter needs to be set with your cell phone type id or a newly created id. MY_FANCY_IMPORTED_TABLE_NAME needs to be updated to actual imported table name. 

Again, please review carefully. I have no knowledge of your business practice. Use at your own risk. 

Travis

Eaxmple 1 

/**************************************************************************************'
Script for adding some new phones.
--loop
*****************************************************************************************/

declare
@phone_type int = # --ID #: create some new type or use and existing type.
,@preview char = 'Y'

declare @NewPhones table (
customer_no int
,phone varchar(50)
,row_num INT IDENTITY(1,1)
)

insert into @NewPhones
select
a.customer_no
,a.phone
from MY_FANCY_IMPORTED_TABLE_NAME a

declare @max int,
@min int,
@next_id int,
@next_PhoneId int,
@id_count int

select @id_count = (select max(row_num) from @NewPhones) --for updating the id in t_next_id.
select @max = (select MAX(row_num) from @NewPhones)
select @min = (select MIN(row_num) from @NewPhones)
select @next_PhoneId = (select next_id from T_NEXT_ID where type = 'AD')

if (@preview = 'N')--''PH' was changed to 'AD' = phone 
begin
--updates the phone id
Update t_next_id
set next_id = @next_PhoneId+@id_count+20 -- the next plus the count of your list + 20 just in case we need a buffer.
where type = 'AD'
end

while @max >= @min
begin
/**Updates**/
if(@preview = 'N')
begin

insert into T_PHONE
select
@next_PhoneId
,customer_no
,null phone_address_no
,phone
,@phone_type as phone_type
,null as day_ind
,'Y' as tele_ind
,getdate() as ph_create_dt
,'dbo' as ph_created_by
,GETDATE()
,'dbo' as ph_last_updated_by
, null as ph_create_loc
from @NewPhones
where row_num = @min

end

/**Preview**/
if(@preview = 'Y')
begin

select
@next_PhoneId
,customer_no
,null phone_address_no
,phone
,@phone_type as phone_type
,null as day_ind
,'Y' as tele_ind
,getdate() as ph_create_dt
,'dbo' as ph_created_by
,GETDATE()
,'dbo' as ph_last_updated_by
, null as ph_create_loc
from @NewPhones
where row_num = @min

end

--set vars
set @next_PhoneId = @next_PhoneId + 1
set @min = (select MIN(row_num) from @NewPhones where row_num > @min)

end

Example 2

/**************************************************************************************'
Script for adding some new phones.
--bulk insert
*****************************************************************************************/

declare
@phone_type int = ### --ID #: create some new type or use and existing type.
,@preview char = 'N'

declare @NewPhones table (
customer_no int
,phone varchar(50)
,row_num INT IDENTITY(1,1)
)

insert into @NewPhones
select
a.customer_no
,a.phone
from MY_FANCY_IMPORTED_TABLE_NAME a

declare @next_PhoneId int,@id_count int

select @id_count = (select max(row_num) from @NewPhones) --for updating the id in t_next_id.
select @next_PhoneId = (select next_id from T_NEXT_ID where type = 'AD')

if (@preview = 'N')--'PH' was changed to 'AD' = phone
begin
--updates the phone id
Update t_next_id
set next_id = @next_PhoneId+@id_count+20 -- the next plus the count of your list + 20 just in case we need a buffer.
where type = 'AD'
end

if(@preview = 'N')
begin

insert into T_PHONE
select
@next_PhoneId + row_num
,customer_no
,null phone_address_no
,phone
,@phone_type as phone_type
,null as day_ind
,'Y' as tele_ind
,getdate() as ph_create_dt
,'dbo' as ph_created_by
,GETDATE()
,'dbo' as ph_last_updated_by
, null as ph_create_loc
from @NewPhones

end

/**Preview**/
if(@preview = 'Y')
begin

select
@next_PhoneId +row_num
,customer_no
,null phone_address_no
,phone
,@phone_type as phone_type
,null as day_ind
,'Y' as tele_ind
,getdate() as ph_create_dt
,'dbo' as ph_created_by
,GETDATE()
,'dbo' as ph_last_updated_by
, null as ph_create_loc
from @NewPhones

end

This was exactly what I was looking for. I was on the right track, but this was stretching my skills a little. My data entry clerk (and I) thank you!

Travis Armbuster I think the second option also needs a T_NEXT_ID update line.

I've never used it: are there any limitations on the SSMS "Import" Task (e.g. maximum number of rows)?

The second example has it. You probably glazed over it since its squished in there. I have imported over 400k in one shot. Never hit a limit, but I am sure there is. 

if (@preview = 'N')--'PH' was changed to 'AD' = phone 
begin
--updates the phone id
Update t_next_id
set next_id = @next_PhoneId+@id_count+20 -- the next plus the count of your list + 20 just in case we need a buffer.
where type = 'AD'
end

I use the SSMS import pretty regularly and have imported up to 50,000 lines with no issues. We have a custom NCOA import so I've become very familiar with how it works.

Oh, okay, didn't think to look for it there.  I usually do it after the operation:

update T_NEXT_ID = (select MAX(phone_no) + 1 from T_PHONE) where type = 'AD'.

Adding a buffer is probably wise, especially with shared id tables (ugh), and blocking out your set first seems like not a bad idea either, although I'm not sure what the ramifications are.

Process A: [count the number of rows needed]

Process B: insert new row into T_ADDRESS, update T_NEXT_ID for AD

Process A: update T_NEXT_ID with computed row count + buffer

Process A: add new phone, first id conflicts with new address_no in T_ADDRESS

Not _worse_ than the other way around to be sure.  I assume it would actually balk at doing the import, so you'd at least get a warning before you did anything.  The other way around I guess the other user (Process B) would get an obtuse warning, but then would be able to proceed after.

Re: generating IDs on the fly, no buffer needed, please see:

https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_technical-9/1621/procedure-frustration/5663

E.g. based on this, I now regularly import new e-mails from a temp table containing everything but the new eaddress_no's, like so:

declare @number_needed int
    ,@new_id_no int

select @number_needed = count(*)
from #new_eaddresses

exec @new_id_no = [dbo].ap_get_nextid_function @type = 'AD'
    ,@increment = @number_needed

insert into T_EADDRESS
(eaddress_no, customer_no, address, eaddress_type, primary_ind, inactive, market_ind, html_ind)
select
    (row_number() over (
            partition by 1 order by customer_no
                )
            ) + (@new_id_no -1)
    ,x.*
from #new_eaddresses x