CODEWITHSUNDEEP
sqlsql-servercsv
corewania
corewania

Reputation: 43

Importing csv file to SQL Server Management Studio and updating existing entries

I'm running SQL Server 2012 and have a database with about 20,000 entries. I need to update a few columns for about 5,000 of them.

I have the columns in a csv and it basically looks like this:

ClientNumber    ClientName              TaxYear       OutputFileField
9455            James Bond and Company   2016          \local\cndps\93985.esx

I need to update the first 3 columns based on the 4th column match

So for instance I need to update the above to be

ClientNumber    ClientName              TaxYear       OutputFileField
1111            Luis Fandora            2044          \local\cndps\93985.esx

I tried importing thru SQL Server Management Studio, but that did not work.

Any help would be appreciated.

Upvotes: 1

Views: 2677

Answers (1)

FutbolFan
FutbolFan

Reputation: 13753

Like @Shreyas Chavan suggested, you could do this:

  • Create a temp table and load the new spreadsheet data.

  • Next, join the existing table to your temp table on that 4th field (OutputField).

  • Update your first three columns based on that join.

Here is a sample code:

CREATE TABLE #ClientInfo(
ClientNumber int NOT NULL,
ClientName nvarchar(255) NULL,
Tax_Year int NULL, 
OutputField nvarchar(255) NULL)
GO

BULK INSERT #ClientInfo
FROM 'C:\Documents and Settings\corewania\Desktop\ClientInfo.csv'    
       --change this to match your directory
WITH(  
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')
GO


UPDATE oci
SET oci.ClientNumber = nci.ClientNumber,
    oci.ClientName = nci.ClientName,
    oci.Tax_Year = nci.Tax_Year 
FROM orig_Clientinfo oci
JOIN #ClientInfo nci on nci.OutputField = oci.OutputField;

Upvotes: 6

Related Questions