January 12, 2008 at 11:43 am
This one has been puzzling me for about 2 weeks now, and I don't have a solution. Maybe somebody here will have some ideas.
I had written a .NET program that would take about 2.2 million rows of data, clean it up, cross check some of it, and then use a stored procedure to insert the data into a database. This worked well, but it was slow. Running it on the application server (different machine from database server) would take about 9 hours. When running the .NET program on a laptop with the database locally, it would run in about 2 hours.
Our Plan B was to create a single, large SQL script that would create the stored procedure, then execute it for every insert we needed to perform. This script was run on the database server. When running this under the Query Analyzer in SQL2000, it takes nearly 40 hours to complete. It does not peg the CPU, memory, or hard drive.
I guess what I'm looking for is any ideas, something I missed in SQL configuration or something that is contributing to this script running this slowly.
January 12, 2008 at 12:02 pm
It sounds like you're getting data from a file, cleaning it up, and then inserting it into a database... For 2.2 million rows, this shouldn't take more than a minute or two.
Best thing to do would be to post or attach some of the data, identify what you want to do to the data to "clean" it up, and what you want inserted in which table (include the create for the table, please).
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2008 at 1:02 pm
Thanks for the quick response.
The data that I'm trying to clean up is the NPPES Data Dessimination files released by Medicaid and Medicare (http://www.cms.hhs.gov/NationalProvIdentStand/06a_DataDissemination.asp). Cleaning them up is the easy part -- this I do in a separate program that just generates the insert scripts. How long this process takes is irrelevant (~3 - 5 minutes), because I do it once a month, and then apply the script to our server.
When I run this on my local machine, it pegs my box and finishes in a reasonable period of time, but when I run it on a server, it takes far too long.
Bear with me, I'm not sure how much of the code I'm allowed to post, we had somebody 'let go' recently for posting source code online.
January 12, 2008 at 3:51 pm
Ok... you certainly shouldn't post that data... it contains private information and understood about the source code.
Here's the real key... you're using an application to insert 2.2 million rows, one at a time... that's going to take a million years to run.
You get the data from a file... so should SQL Server using something like Bulk Insert or BCP. Bulk Insert will import 5.1 million 20 column rows in about 60 seconds. Ya see where I'm going with this?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2008 at 4:44 pm
I may largely be echoing Jeff here, but why create insert statements?
Pull it in directly if you can. If you do not have a reason to use T-SQL pure then SSIS or DTS is probably the way to go and you can arrange scripts within them for more sophisticated processing. If you need T-SQL pure you can use bulk insert if it is cleanly formatted. If it is not cleanly formatted then opendatasource is more forgiving and fault tolerant than bulk insert, but also slower.
SQL's greatest strength is in batch processing.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
January 12, 2008 at 9:41 pm
Why not use DTS? Insert to a staging table, very quick, then run cleanup on it.
January 14, 2008 at 3:44 am
So Whitt was using extreme RBAR?:sick:
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
January 14, 2008 at 5:57 am
Heh... worse... RBAR on steriods over the "pipe".
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2008 at 11:39 am
Sorry for the delay in responding to this thread.
I really appreciate the responses, folks. Unfortunately, we had to go RBAR on this file due to the serious issues in the data integrity. This file was supposed to be a CSV, but in reality it had serious issues. Embedded single quotes, double quotes, commands, and that was just the structure.
The actual data itself was accumulated from what physicians wrote on the forms and submitted. There was no validation, no cleaning up of the data (eg; Germany is not a valid state, nor is 90210).
It was passed on to me by people who understand better that this was beyond the capabilities of DTS to handle. The thing is, we need to clean up this data then distribute it to around 40 client sites for them to add this data to their database, so whatever solution I go with, it needs to be run in a 'simple' manner.
January 15, 2008 at 1:07 pm
With regards to using the DTS -- is DTS capable of taking a large record (187 fields), cutting up portions of it, performing an insert, taking an identity from that insert and keying other records to that identity? Also -- DTS in SQL2000 vs SQL2005, if I generate a DTS script/program, will both systems be able to use the same script?
I'm running out of options (that I know of) on how to clean up, parse, and distribute this data to the clients. It is very frustrating, because the performance that I see running on the database on my local laptop is not the same as the performance that I see running on servers with 2-4X the horsepower of my machine.
I'm really a novice at database transformations and stuff. I've done plenty of SQL queries from applications and stored procedures, but as a DBA, my skills are non-existant.
January 15, 2008 at 2:36 pm
I think you'd need to to two passes of the file, or perhaps some strange scripting.
Honestly Whitt, I think simpler is better. I'd pull the whole file into SQL Server as a staged table. Meaning a table that mirrors the structure of the file. Then you can write some stored procedures to handle the various parsing things that you need done. One to grab parts of the table and insert, maybe update the staging table back with the identity values. Then another procedure to move the data using that value.
January 15, 2008 at 2:58 pm
Jeff Moden (1/12/2008)
Ok... you certainly shouldn't post that data... it contains private information and understood about the source code.Here's the real key... you're using an application to insert 2.2 million rows, one at a time... that's going to take a million years to run.
You get the data from a file... so should SQL Server using something like Bulk Insert or BCP. Bulk Insert will import 5.1 million 20 column rows in about 60 seconds. Ya see where I'm going with this?
Actually - no - it's not. It's right there to be downloaded....
http://nppesdata.cms.hhs.gov/NPPES_Data_Dissemination_Dec_2007.zip
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 15, 2008 at 3:15 pm
All right - next question - what problem are you having with it?
Just finished importing it into a table it built. total time: 3:20 (as in 200 seconds, not 3 hours 20 minutes). (It's DTS - it takes its time).
Now - there might be some formatting issues I see, but still - the data is there and I can see everything.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 15, 2008 at 3:28 pm
Actually - let me correct myself - other than it being a typical flat file (meaning - OODLES of blank fields for all of the denormalized data it's carrying) - it ain't bad.
And - it's 2.49M records.
Really - what would you like to do to it? Keep in mind you're going to have to do this EVERY MONTH, so we might as well automate the goofy thing.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2008 at 12:08 am
Well.... I should have known better... as soon as I saw that it was a Government agency, I should have known that the record layout they provided wasn't going to be accurate when compared to the data. Many of the columns are, in fact, wider than their record layout states. Heh... figures... with all their fancy pants ISO standards, they can't even produce a file according to their own specs. :hehe:
Just once, I'd like to see a Government agency that practices all the ISO standards that our favorite "ISO Zealot" preaches (well, Hello Joe C. :Whistling: )! Forget all those ISO standards... the need to follow my favorite ISO standard... MIL-TP-41H which is another "Modenism" for "MAKE IT LIKE THE PRINT FOR ONCE, HUH?" 😀
Rant complete... pork chop supply depleted... regard all further alarms.
Ok... first, here's the table schema you need... I didn't take the time to rename their bloody columns (lets see an Oracle lover fit THOSE into 30 characters!!! :w00t: ). I'll leave that up to you...
CREATE TABLE dbo.NPIData
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[NPI] DECIMAL(10) NULL ,
[Entity Type Code] TINYINT NULL ,
[Replacement NPI] DECIMAL(10) NULL ,
[Employer Identification Number (EIN)] VARCHAR(9) NULL ,
[Provider Organization Name (Legal Business Name)] VARCHAR(80) NULL ,
[Provider Last Name (Legal Name)] VARCHAR(35) NULL ,
[Provider First Name] VARCHAR(20) NULL ,
[Provider Middle Name] VARCHAR(20) NULL ,
[Provider Name Prefix Text] VARCHAR(5) NULL ,
[Provider Name Suffix Text] VARCHAR(5) NULL ,
[Provider Credential Text] VARCHAR(20) NULL ,
[Provider Other Organization Name] VARCHAR(70) NULL ,
[Provider Other Organization Name Type Code] VARCHAR(1) NULL ,
[Provider Other Last Name] VARCHAR(35) NULL ,
[Provider Other First Name] VARCHAR(20) NULL ,
[Provider Other Middle Name] VARCHAR(20) NULL ,
[Provider Other Name Prefix Text] VARCHAR(5) NULL ,
[Provider Other Name Suffix Text] VARCHAR(5) NULL ,
[Provider Other Credential Text] VARCHAR(20) NULL ,
[Provider Other Last Name Type Code] CHAR(1) NULL ,
[Provider First Line Business Mailing Address] VARCHAR(60) NULL ,
[Provider Second Line Business Mailing Address] VARCHAR(60) NULL ,
[Provider Business Mailing Address City Name] VARCHAR(40) NULL ,
[Provider Business Mailing Address State Name] VARCHAR(40) NULL ,
[Provider Business Mailing Address Postal Code] VARCHAR(20) NULL ,
[Provider Business Mailing Address Country Code (If outside U.S.)] CHAR(2) NULL ,
[Provider Business Mailing Address Telephone Number] VARCHAR(20) NULL ,
[Provider Business Mailing Address Fax Number] VARCHAR(20) NULL ,
[Provider First Line Business Practice Location Address] VARCHAR(60) NULL ,
[Provider Second Line Business Practice Location Address] VARCHAR(60) NULL ,
[Provider Business Practice Location Address City Name] VARCHAR(40) NULL ,
[Provider Business Practice Location Address State Name] VARCHAR(40) NULL ,
[Provider Business Practice Location Address Postal Code] VARCHAR(20) NULL ,
[Provider Business Practice Location Address Country Code (If outside U.S.)] CHAR(2) NULL ,
[Provider Business Practice Location Address Telephone Number] VARCHAR(20) NULL ,
[Provider Business Practice Location Address Fax Number] VARCHAR(20) NULL ,
[Provider Enumeration Date] DATETIME NULL ,
[Last Update Date] DATETIME NULL ,
[NPI Deactivation Reason Code] VARCHAR(2) NULL ,
[NPI Deactivation Date] DATETIME NULL ,
[NPI Reactivation Date] DATETIME NULL ,
[Provider Gender Code] CHAR(1) NULL ,
[Authorized Official Last Name] VARCHAR(35) NULL ,
[Authorized Official First Name] VARCHAR(20) NULL ,
[Authorized Official Middle Name] VARCHAR(20) NULL ,
[Authorized Official Title or Position] VARCHAR(35) NULL ,
[Authorized Official Telephone Number] VARCHAR(20) NULL ,
[Healthcare Provider Taxonomy Code_1] VARCHAR(10) NULL ,
[Provider License Number_1] VARCHAR(20) NULL ,
[Provider License Number State Code_1] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_1] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_2] VARCHAR(10) NULL ,
[Provider License Number_2] VARCHAR(20) NULL ,
[Provider License Number State Code_2] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_2] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_3] VARCHAR(10) NULL ,
[Provider License Number_3] VARCHAR(20) NULL ,
[Provider License Number State Code_3] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_3] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_4] VARCHAR(10) NULL ,
[Provider License Number_4] VARCHAR(20) NULL ,
[Provider License Number State Code_4] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_4] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_5] VARCHAR(10) NULL ,
[Provider License Number_5] VARCHAR(20) NULL ,
[Provider License Number State Code_5] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_5] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_6] VARCHAR(10) NULL ,
[Provider License Number_6] VARCHAR(20) NULL ,
[Provider License Number State Code_6] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_6] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_7] VARCHAR(10) NULL ,
[Provider License Number_7] VARCHAR(20) NULL ,
[Provider License Number State Code_7] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_7] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_8] VARCHAR(10) NULL ,
[Provider License Number_8] VARCHAR(20) NULL ,
[Provider License Number State Code_8] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_8] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_9] VARCHAR(10) NULL ,
[Provider License Number_9] VARCHAR(20) NULL ,
[Provider License Number State Code_9] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_9] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_10] VARCHAR(10) NULL ,
[Provider License Number_10] VARCHAR(20) NULL ,
[Provider License Number State Code_10] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_10] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_11] VARCHAR(10) NULL ,
[Provider License Number_11] VARCHAR(20) NULL ,
[Provider License Number State Code_11] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_11] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_12] VARCHAR(10) NULL ,
[Provider License Number_12] VARCHAR(20) NULL ,
[Provider License Number State Code_12] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_12] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_13] VARCHAR(10) NULL ,
[Provider License Number_13] VARCHAR(20) NULL ,
[Provider License Number State Code_13] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_13] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_14] VARCHAR(10) NULL ,
[Provider License Number_14] VARCHAR(20) NULL ,
[Provider License Number State Code_14] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_14] VARCHAR(5) NULL ,
[Healthcare Provider Taxonomy Code_15] VARCHAR(10) NULL ,
[Provider License Number_15] VARCHAR(20) NULL ,
[Provider License Number State Code_15] CHAR(2) NULL ,
[Healthcare Provider Primary Taxonomy Switch_15] VARCHAR(5) NULL ,
[Other Provider Identifier_1] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_1] VARCHAR(2) NULL ,
[Other Provider Identifier State_1] CHAR(2) NULL ,
[Other Provider Identifier Issuer_1] VARCHAR(80) NULL ,
[Other Provider Identifier_2] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_2] VARCHAR(2) NULL ,
[Other Provider Identifier State_2] CHAR(2) NULL ,
[Other Provider Identifier Issuer_2] VARCHAR(80) NULL ,
[Other Provider Identifier_3] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_3] VARCHAR(2) NULL ,
[Other Provider Identifier State_3] CHAR(2) NULL ,
[Other Provider Identifier Issuer_3] VARCHAR(80) NULL ,
[Other Provider Identifier_4] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_4] VARCHAR(2) NULL ,
[Other Provider Identifier State_4] CHAR(2) NULL ,
[Other Provider Identifier Issuer_4] VARCHAR(80) NULL ,
[Other Provider Identifier_5] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_5] VARCHAR(2) NULL ,
[Other Provider Identifier State_5] CHAR(2) NULL ,
[Other Provider Identifier Issuer_5] VARCHAR(80) NULL ,
[Other Provider Identifier_6] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_6] VARCHAR(2) NULL ,
[Other Provider Identifier State_6] CHAR(2) NULL ,
[Other Provider Identifier Issuer_6] VARCHAR(80) NULL ,
[Other Provider Identifier_7] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_7] VARCHAR(2) NULL ,
[Other Provider Identifier State_7] CHAR(2) NULL ,
[Other Provider Identifier Issuer_7] VARCHAR(80) NULL ,
[Other Provider Identifier_8] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_8] VARCHAR(2) NULL ,
[Other Provider Identifier State_8] CHAR(2) NULL ,
[Other Provider Identifier Issuer_8] VARCHAR(80) NULL ,
[Other Provider Identifier_9] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_9] VARCHAR(2) NULL ,
[Other Provider Identifier State_9] CHAR(2) NULL ,
[Other Provider Identifier Issuer_9] VARCHAR(80) NULL ,
[Other Provider Identifier_10] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_10] VARCHAR(2) NULL ,
[Other Provider Identifier State_10] CHAR(2) NULL ,
[Other Provider Identifier Issuer_10] VARCHAR(80) NULL ,
[Other Provider Identifier_11] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_11] VARCHAR(2) NULL ,
[Other Provider Identifier State_11] CHAR(2) NULL ,
[Other Provider Identifier Issuer_11] VARCHAR(80) NULL ,
[Other Provider Identifier_12] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_12] VARCHAR(2) NULL ,
[Other Provider Identifier State_12] CHAR(2) NULL ,
[Other Provider Identifier Issuer_12] VARCHAR(80) NULL ,
[Other Provider Identifier_13] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_13] VARCHAR(2) NULL ,
[Other Provider Identifier State_13] CHAR(2) NULL ,
[Other Provider Identifier Issuer_13] VARCHAR(80) NULL ,
[Other Provider Identifier_14] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_14] VARCHAR(2) NULL ,
[Other Provider Identifier State_14] CHAR(2) NULL ,
[Other Provider Identifier Issuer_14] VARCHAR(80) NULL ,
[Other Provider Identifier_15] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_15] VARCHAR(2) NULL ,
[Other Provider Identifier State_15] CHAR(2) NULL ,
[Other Provider Identifier Issuer_15] VARCHAR(80) NULL ,
[Other Provider Identifier_16] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_16] VARCHAR(2) NULL ,
[Other Provider Identifier State_16] CHAR(2) NULL ,
[Other Provider Identifier Issuer_16] VARCHAR(80) NULL ,
[Other Provider Identifier_17] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_17] VARCHAR(2) NULL ,
[Other Provider Identifier State_17] CHAR(2) NULL ,
[Other Provider Identifier Issuer_17] VARCHAR(80) NULL ,
[Other Provider Identifier_18] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_18] VARCHAR(2) NULL ,
[Other Provider Identifier State_18] CHAR(2) NULL ,
[Other Provider Identifier Issuer_18] VARCHAR(80) NULL ,
[Other Provider Identifier_19] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_19] VARCHAR(2) NULL ,
[Other Provider Identifier State_19] CHAR(2) NULL ,
[Other Provider Identifier Issuer_19] VARCHAR(80) NULL ,
[Other Provider Identifier_20] VARCHAR(25) NULL ,
[Other Provider Identifier Type Code_20] VARCHAR(2) NULL ,
[Other Provider Identifier State_20] CHAR(2) NULL ,
[Other Provider Identifier Issuer_20] VARCHAR(80) NULL
)
GO
You'll also need to store the following BCP format file somewhere... I normally keep them with the data they serve so well... (EDIT - Forum code wouldn't let the required "Backslash-n" in the code window so the following isn't 100% accurate... I attached the correct format file in the post that follows this one!)
8.0
188
1 SQLCHAR 0 999 "\"" 0 IgnoreMe ""
2 SQLCHAR 0 999 "\",\"" 2 NPI ""
3 SQLCHAR 0 999 "\",\"" 3 EntityTypeCode ""
4 SQLCHAR 0 999 "\",\"" 4 ReplacementNPI ""
5 SQLCHAR 0 999 "\",\"" 5 EmployerIdentificationNumber(EIN) ""
6 SQLCHAR 0 999 "\",\"" 6 ProviderOrganizationName(LegalBusinessName) ""
7 SQLCHAR 0 999 "\",\"" 7 ProviderLastName(LegalName) ""
8 SQLCHAR 0 999 "\",\"" 8 ProviderFirstName ""
9 SQLCHAR 0 999 "\",\"" 9 ProviderMiddleName ""
10 SQLCHAR 0 999 "\",\"" 10 ProviderNamePrefixText ""
11 SQLCHAR 0 999 "\",\"" 11 ProviderNameSuffixText ""
12 SQLCHAR 0 999 "\",\"" 12 ProviderCredentialText ""
13 SQLCHAR 0 999 "\",\"" 13 ProviderOtherOrganizationName ""
14 SQLCHAR 0 999 "\",\"" 14 ProviderOtherOrganizationNameTypeCode ""
15 SQLCHAR 0 999 "\",\"" 15 ProviderOtherLastName ""
16 SQLCHAR 0 999 "\",\"" 16 ProviderOtherFirstName ""
17 SQLCHAR 0 999 "\",\"" 17 ProviderOtherMiddleName ""
18 SQLCHAR 0 999 "\",\"" 18 ProviderOtherNamePrefixText ""
19 SQLCHAR 0 999 "\",\"" 19 ProviderOtherNameSuffixText ""
20 SQLCHAR 0 999 "\",\"" 20 ProviderOtherCredentialText ""
21 SQLCHAR 0 999 "\",\"" 21 ProviderOtherLastNameTypeCode ""
22 SQLCHAR 0 999 "\",\"" 22 ProviderFirstLineBusinessMailingAddress ""
23 SQLCHAR 0 999 "\",\"" 23 ProviderSecondLineBusinessMailingAddress ""
24 SQLCHAR 0 999 "\",\"" 24 ProviderBusinessMailingAddressCityName ""
25 SQLCHAR 0 999 "\",\"" 25 ProviderBusinessMailingAddressStateName ""
26 SQLCHAR 0 999 "\",\"" 26 ProviderBusinessMailingAddressPostalCode ""
27 SQLCHAR 0 999 "\",\"" 27 ProviderBusinessMailingAddressCountryCode(IfoutsideU.S.) ""
28 SQLCHAR 0 999 "\",\"" 28 ProviderBusinessMailingAddressTelephoneNumber ""
29 SQLCHAR 0 999 "\",\"" 29 ProviderBusinessMailingAddressFaxNumber ""
30 SQLCHAR 0 999 "\",\"" 30 ProviderFirstLineBusinessPracticeLocationAddress ""
31 SQLCHAR 0 999 "\",\"" 31 ProviderSecondLineBusinessPracticeLocationAddress ""
32 SQLCHAR 0 999 "\",\"" 32 ProviderBusinessPracticeLocationAddressCityName ""
33 SQLCHAR 0 999 "\",\"" 33 ProviderBusinessPracticeLocationAddressStateName ""
34 SQLCHAR 0 999 "\",\"" 34 ProviderBusinessPracticeLocationAddressPostalCode ""
35 SQLCHAR 0 999 "\",\"" 35 ProviderBusinessPracticeLocationAddressCountryCode(IfoutsideU.S.) ""
36 SQLCHAR 0 999 "\",\"" 36 ProviderBusinessPracticeLocationAddressTelephoneNumber ""
37 SQLCHAR 0 999 "\",\"" 37 ProviderBusinessPracticeLocationAddressFaxNumber ""
38 SQLCHAR 0 999 "\",\"" 38 ProviderEnumerationDate ""
39 SQLCHAR 0 999 "\",\"" 39 LastUpdateDate ""
40 SQLCHAR 0 999 "\",\"" 40 NPIDeactivationReasonCode ""
41 SQLCHAR 0 999 "\",\"" 41 NPIDeactivationDate ""
42 SQLCHAR 0 999 "\",\"" 42 NPIReactivationDate ""
43 SQLCHAR 0 999 "\",\"" 43 ProviderGenderCode ""
44 SQLCHAR 0 999 "\",\"" 44 AuthorizedOfficialLastName ""
45 SQLCHAR 0 999 "\",\"" 45 AuthorizedOfficialFirstName ""
46 SQLCHAR 0 999 "\",\"" 46 AuthorizedOfficialMiddleName ""
47 SQLCHAR 0 999 "\",\"" 47 AuthorizedOfficialTitleorPosition ""
48 SQLCHAR 0 999 "\",\"" 48 AuthorizedOfficialTelephoneNumber ""
49 SQLCHAR 0 999 "\",\"" 49 HealthcareProviderTaxonomyCode_1 ""
50 SQLCHAR 0 999 "\",\"" 50 ProviderLicenseNumber_1 ""
51 SQLCHAR 0 999 "\",\"" 51 ProviderLicenseNumberStateCode_1 ""
52 SQLCHAR 0 999 "\",\"" 52 HealthcareProviderPrimaryTaxonomySwitch_1 ""
53 SQLCHAR 0 999 "\",\"" 53 HealthcareProviderTaxonomyCode_2 ""
54 SQLCHAR 0 999 "\",\"" 54 ProviderLicenseNumber_2 ""
55 SQLCHAR 0 999 "\",\"" 55 ProviderLicenseNumberStateCode_2 ""
56 SQLCHAR 0 999 "\",\"" 56 HealthcareProviderPrimaryTaxonomySwitch_2 ""
57 SQLCHAR 0 999 "\",\"" 57 HealthcareProviderTaxonomyCode_3 ""
58 SQLCHAR 0 999 "\",\"" 58 ProviderLicenseNumber_3 ""
59 SQLCHAR 0 999 "\",\"" 59 ProviderLicenseNumberStateCode_3 ""
60 SQLCHAR 0 999 "\",\"" 60 HealthcareProviderPrimaryTaxonomySwitch_3 ""
61 SQLCHAR 0 999 "\",\"" 61 HealthcareProviderTaxonomyCode_4 ""
62 SQLCHAR 0 999 "\",\"" 62 ProviderLicenseNumber_4 ""
63 SQLCHAR 0 999 "\",\"" 63 ProviderLicenseNumberStateCode_4 ""
64 SQLCHAR 0 999 "\",\"" 64 HealthcareProviderPrimaryTaxonomySwitch_4 ""
65 SQLCHAR 0 999 "\",\"" 65 HealthcareProviderTaxonomyCode_5 ""
66 SQLCHAR 0 999 "\",\"" 66 ProviderLicenseNumber_5 ""
67 SQLCHAR 0 999 "\",\"" 67 ProviderLicenseNumberStateCode_5 ""
68 SQLCHAR 0 999 "\",\"" 68 HealthcareProviderPrimaryTaxonomySwitch_5 ""
69 SQLCHAR 0 999 "\",\"" 69 HealthcareProviderTaxonomyCode_6 ""
70 SQLCHAR 0 999 "\",\"" 70 ProviderLicenseNumber_6 ""
71 SQLCHAR 0 999 "\",\"" 71 ProviderLicenseNumberStateCode_6 ""
72 SQLCHAR 0 999 "\",\"" 72 HealthcareProviderPrimaryTaxonomySwitch_6 ""
73 SQLCHAR 0 999 "\",\"" 73 HealthcareProviderTaxonomyCode_7 ""
74 SQLCHAR 0 999 "\",\"" 74 ProviderLicenseNumber_7 ""
75 SQLCHAR 0 999 "\",\"" 75 ProviderLicenseNumberStateCode_7 ""
76 SQLCHAR 0 999 "\",\"" 76 HealthcareProviderPrimaryTaxonomySwitch_7 ""
77 SQLCHAR 0 999 "\",\"" 77 HealthcareProviderTaxonomyCode_8 ""
78 SQLCHAR 0 999 "\",\"" 78 ProviderLicenseNumber_8 ""
79 SQLCHAR 0 999 "\",\"" 79 ProviderLicenseNumberStateCode_8 ""
80 SQLCHAR 0 999 "\",\"" 80 HealthcareProviderPrimaryTaxonomySwitch_8 ""
81 SQLCHAR 0 999 "\",\"" 81 HealthcareProviderTaxonomyCode_9 ""
82 SQLCHAR 0 999 "\",\"" 82 ProviderLicenseNumber_9 ""
83 SQLCHAR 0 999 "\",\"" 83 ProviderLicenseNumberStateCode_9 ""
84 SQLCHAR 0 999 "\",\"" 84 HealthcareProviderPrimaryTaxonomySwitch_9 ""
85 SQLCHAR 0 999 "\",\"" 85 HealthcareProviderTaxonomyCode_10 ""
86 SQLCHAR 0 999 "\",\"" 86 ProviderLicenseNumber_10 ""
87 SQLCHAR 0 999 "\",\"" 87 ProviderLicenseNumberStateCode_10 ""
88 SQLCHAR 0 999 "\",\"" 88 HealthcareProviderPrimaryTaxonomySwitch_10 ""
89 SQLCHAR 0 999 "\",\"" 89 HealthcareProviderTaxonomyCode_11 ""
90 SQLCHAR 0 999 "\",\"" 90 ProviderLicenseNumber_11 ""
91 SQLCHAR 0 999 "\",\"" 91 ProviderLicenseNumberStateCode_11 ""
92 SQLCHAR 0 999 "\",\"" 92 HealthcareProviderPrimaryTaxonomySwitch_11 ""
93 SQLCHAR 0 999 "\",\"" 93 HealthcareProviderTaxonomyCode_12 ""
94 SQLCHAR 0 999 "\",\"" 94 ProviderLicenseNumber_12 ""
95 SQLCHAR 0 999 "\",\"" 95 ProviderLicenseNumberStateCode_12 ""
96 SQLCHAR 0 999 "\",\"" 96 HealthcareProviderPrimaryTaxonomySwitch_12 ""
97 SQLCHAR 0 999 "\",\"" 97 HealthcareProviderTaxonomyCode_13 ""
98 SQLCHAR 0 999 "\",\"" 98 ProviderLicenseNumber_13 ""
99 SQLCHAR 0 999 "\",\"" 99 ProviderLicenseNumberStateCode_13 ""
100 SQLCHAR 0 999 "\",\"" 100 HealthcareProviderPrimaryTaxonomySwitch_13 ""
101 SQLCHAR 0 999 "\",\"" 101 HealthcareProviderTaxonomyCode_14 ""
102 SQLCHAR 0 999 "\",\"" 102 ProviderLicenseNumber_14 ""
103 SQLCHAR 0 999 "\",\"" 103 ProviderLicenseNumberStateCode_14 ""
104 SQLCHAR 0 999 "\",\"" 104 HealthcareProviderPrimaryTaxonomySwitch_14 ""
105 SQLCHAR 0 999 "\",\"" 105 HealthcareProviderTaxonomyCode_15 ""
106 SQLCHAR 0 999 "\",\"" 106 ProviderLicenseNumber_15 ""
107 SQLCHAR 0 999 "\",\"" 107 ProviderLicenseNumberStateCode_15 ""
108 SQLCHAR 0 999 "\",\"" 108 HealthcareProviderPrimaryTaxonomySwitch_15 ""
109 SQLCHAR 0 999 "\",\"" 109 OtherProviderIdentifier_1 ""
110 SQLCHAR 0 999 "\",\"" 110 OtherProviderIdentifierTypeCode_1 ""
111 SQLCHAR 0 999 "\",\"" 111 OtherProviderIdentifierState_1 ""
112 SQLCHAR 0 999 "\",\"" 112 OtherProviderIdentifierIssuer_1 ""
113 SQLCHAR 0 999 "\",\"" 113 OtherProviderIdentifier_2 ""
114 SQLCHAR 0 999 "\",\"" 114 OtherProviderIdentifierTypeCode_2 ""
115 SQLCHAR 0 999 "\",\"" 115 OtherProviderIdentifierState_2 ""
116 SQLCHAR 0 999 "\",\"" 116 OtherProviderIdentifierIssuer_2 ""
117 SQLCHAR 0 999 "\",\"" 117 OtherProviderIdentifier_3 ""
118 SQLCHAR 0 999 "\",\"" 118 OtherProviderIdentifierTypeCode_3 ""
119 SQLCHAR 0 999 "\",\"" 119 OtherProviderIdentifierState_3 ""
120 SQLCHAR 0 999 "\",\"" 120 OtherProviderIdentifierIssuer_3 ""
121 SQLCHAR 0 999 "\",\"" 121 OtherProviderIdentifier_4 ""
122 SQLCHAR 0 999 "\",\"" 122 OtherProviderIdentifierTypeCode_4 ""
123 SQLCHAR 0 999 "\",\"" 123 OtherProviderIdentifierState_4 ""
124 SQLCHAR 0 999 "\",\"" 124 OtherProviderIdentifierIssuer_4 ""
125 SQLCHAR 0 999 "\",\"" 125 OtherProviderIdentifier_5 ""
126 SQLCHAR 0 999 "\",\"" 126 OtherProviderIdentifierTypeCode_5 ""
127 SQLCHAR 0 999 "\",\"" 127 OtherProviderIdentifierState_5 ""
128 SQLCHAR 0 999 "\",\"" 128 OtherProviderIdentifierIssuer_5 ""
129 SQLCHAR 0 999 "\",\"" 129 OtherProviderIdentifier_6 ""
130 SQLCHAR 0 999 "\",\"" 130 OtherProviderIdentifierTypeCode_6 ""
131 SQLCHAR 0 999 "\",\"" 131 OtherProviderIdentifierState_6 ""
132 SQLCHAR 0 999 "\",\"" 132 OtherProviderIdentifierIssuer_6 ""
133 SQLCHAR 0 999 "\",\"" 133 OtherProviderIdentifier_7 ""
134 SQLCHAR 0 999 "\",\"" 134 OtherProviderIdentifierTypeCode_7 ""
135 SQLCHAR 0 999 "\",\"" 135 OtherProviderIdentifierState_7 ""
136 SQLCHAR 0 999 "\",\"" 136 OtherProviderIdentifierIssuer_7 ""
137 SQLCHAR 0 999 "\",\"" 137 OtherProviderIdentifier_8 ""
138 SQLCHAR 0 999 "\",\"" 138 OtherProviderIdentifierTypeCode_8 ""
139 SQLCHAR 0 999 "\",\"" 139 OtherProviderIdentifierState_8 ""
140 SQLCHAR 0 999 "\",\"" 140 OtherProviderIdentifierIssuer_8 ""
141 SQLCHAR 0 999 "\",\"" 141 OtherProviderIdentifier_9 ""
142 SQLCHAR 0 999 "\",\"" 142 OtherProviderIdentifierTypeCode_9 ""
143 SQLCHAR 0 999 "\",\"" 143 OtherProviderIdentifierState_9 ""
144 SQLCHAR 0 999 "\",\"" 144 OtherProviderIdentifierIssuer_9 ""
145 SQLCHAR 0 999 "\",\"" 145 OtherProviderIdentifier_10 ""
146 SQLCHAR 0 999 "\",\"" 146 OtherProviderIdentifierTypeCode_10 ""
147 SQLCHAR 0 999 "\",\"" 147 OtherProviderIdentifierState_10 ""
148 SQLCHAR 0 999 "\",\"" 148 OtherProviderIdentifierIssuer_10 ""
149 SQLCHAR 0 999 "\",\"" 149 OtherProviderIdentifier_11 ""
150 SQLCHAR 0 999 "\",\"" 150 OtherProviderIdentifierTypeCode_11 ""
151 SQLCHAR 0 999 "\",\"" 151 OtherProviderIdentifierState_11 ""
152 SQLCHAR 0 999 "\",\"" 152 OtherProviderIdentifierIssuer_11 ""
153 SQLCHAR 0 999 "\",\"" 153 OtherProviderIdentifier_12 ""
154 SQLCHAR 0 999 "\",\"" 154 OtherProviderIdentifierTypeCode_12 ""
155 SQLCHAR 0 999 "\",\"" 155 OtherProviderIdentifierState_12 ""
156 SQLCHAR 0 999 "\",\"" 156 OtherProviderIdentifierIssuer_12 ""
157 SQLCHAR 0 999 "\",\"" 157 OtherProviderIdentifier_13 ""
158 SQLCHAR 0 999 "\",\"" 158 OtherProviderIdentifierTypeCode_13 ""
159 SQLCHAR 0 999 "\",\"" 159 OtherProviderIdentifierState_13 ""
160 SQLCHAR 0 999 "\",\"" 160 OtherProviderIdentifierIssuer_13 ""
161 SQLCHAR 0 999 "\",\"" 161 OtherProviderIdentifier_14 ""
162 SQLCHAR 0 999 "\",\"" 162 OtherProviderIdentifierTypeCode_14 ""
163 SQLCHAR 0 999 "\",\"" 163 OtherProviderIdentifierState_14 ""
164 SQLCHAR 0 999 "\",\"" 164 OtherProviderIdentifierIssuer_14 ""
165 SQLCHAR 0 999 "\",\"" 165 OtherProviderIdentifier_15 ""
166 SQLCHAR 0 999 "\",\"" 166 OtherProviderIdentifierTypeCode_15 ""
167 SQLCHAR 0 999 "\",\"" 167 OtherProviderIdentifierState_15 ""
168 SQLCHAR 0 999 "\",\"" 168 OtherProviderIdentifierIssuer_15 ""
169 SQLCHAR 0 999 "\",\"" 169 OtherProviderIdentifier_16 ""
170 SQLCHAR 0 999 "\",\"" 170 OtherProviderIdentifierTypeCode_16 ""
171 SQLCHAR 0 999 "\",\"" 171 OtherProviderIdentifierState_16 ""
172 SQLCHAR 0 999 "\",\"" 172 OtherProviderIdentifierIssuer_16 ""
173 SQLCHAR 0 999 "\",\"" 173 OtherProviderIdentifier_17 ""
174 SQLCHAR 0 999 "\",\"" 174 OtherProviderIdentifierTypeCode_17 ""
175 SQLCHAR 0 999 "\",\"" 175 OtherProviderIdentifierState_17 ""
176 SQLCHAR 0 999 "\",\"" 176 OtherProviderIdentifierIssuer_17 ""
177 SQLCHAR 0 999 "\",\"" 177 OtherProviderIdentifier_18 ""
178 SQLCHAR 0 999 "\",\"" 178 OtherProviderIdentifierTypeCode_18 ""
179 SQLCHAR 0 999 "\",\"" 179 OtherProviderIdentifierState_18 ""
180 SQLCHAR 0 999 "\",\"" 180 OtherProviderIdentifierIssuer_18 ""
181 SQLCHAR 0 999 "\",\"" 181 OtherProviderIdentifier_19 ""
182 SQLCHAR 0 999 "\",\"" 182 OtherProviderIdentifierTypeCode_19 ""
183 SQLCHAR 0 999 "\",\"" 183 OtherProviderIdentifierState_19 ""
184 SQLCHAR 0 999 "\",\"" 184 OtherProviderIdentifierIssuer_19 ""
185 SQLCHAR 0 999 "\",\"" 185 OtherProviderIdentifier_20 ""
186 SQLCHAR 0 999 "\",\"" 186 OtherProviderIdentifierTypeCode_20 ""
187 SQLCHAR 0 999 "\",\"" 187 OtherProviderIdentifierState_20 ""
188 SQLCHAR 0 999 "\"" 188 OtherProviderIdentifierIssuer_20 ""
And, last but not least... you need a snippet of code to do the actual import...
BULK INSERT Medicaid.dbo.NPIData
FROM 'D:\Medicaidpidata_20050523-20071210.csv'
WITH (
CODEPAGE = 'RAW',
DATAFILETYPE = 'CHAR',
FORMATFILE = 'D:\Medicaid\NPIData.fmt',
FIRSTROW = 2,
BATCHSIZE = 50000,
TABLOCK
)
Of course, you'll need to change the path and file name for both the import file and the format file... you could turn this into Dynamic SQL and, using a little magic from the undocumented xp_DirTree command (works in 2k, 2k5, and 2k8), you could automate the whole ball of wax.
The code takes about 10 minutes to run on my poor ol' 6 year old desktop... should do better on a real server with a real disk.
Lemme know if that helps...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply