April 13, 2020 at 6:28 pm
Would this work to only have to hit run and it would import and create the table?
if object_id('DE_IDENTIFIED.dbo.test') is not null
drop table DE_IDENTIFIED.dbo.test
if object_id('DE_IDENTIFIED.dbo.PROVIDER_INFO_1') is not null
drop table DE_IDENTIFIED.dbo.PROVIDER_INFO_1
create table DE_IDENTIFIED.dbo.test
(record varchar(500)
)
bulk insert DE_IDENTIFIED.dbo.test
from 'C:\dident\PRV414WSW\PRV414SW.txt'
with (ROWTERMINATOR = '0x0a')
select rtrim(substring(record, 1,1)) AS RECORD_TYPE
, rtrim(substring(record, 2,9)) AS PROMISE_NUMBER
, rtrim(substring(record, 11,4)) AS LOCATION
, convert(date, rtrim(substring(record, 15,8))) AS START_DATE
, convert(date, rtrim(substring(record, 23,8))) AS END_DATE
, rtrim(substring(record, 31,50)) AS PROVIDER_NAME
, rtrim(substring(record, 81,9)) AS IRS
, rtrim(substring(record, 90,1)) AS IRS_INDICATOR
, rtrim(substring(record, 91,10)) AS MEDICARE_NUMBER
, rtrim(substring(record, 101,2)) AS COUNTY_CODE
, rtrim(substring(record, 103,60)) AS ADDRESS
, rtrim(substring(record, 163,18)) AS CITY
, rtrim(substring(record, 181,2)) AS STATE
, rtrim(substring(record, 183,5)) AS ZIP
, rtrim(substring(record, 188,4)) AS BOX_NUMBER
, rtrim(substring(record, 192,10)) AS PHONE
, rtrim(substring(record, 202,9)) AS DEA_NUMBER
, rtrim(substring(record, 211,6)) AS UPIN
, rtrim(substring(record, 217,10)) AS NPI_MAMIS
INTO DE_IDENTIFIED.DBO.PROVIDER_INFO_1
from DE_IDENTIFIED.dbo.test
where record like '1%'?
Viewing post 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy