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.

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.