Script to import data from a CSV file into an existing MS-SQL table via bulk operation. Creation of a temporary table, import, transfer to productive table. The assignment of the data to the correct keys takes place via an inner join.

The only important thing to note here is that the database user performing the operation has the right for “bulk operations”.

SQL All-in-One For Dummies 2e
SQL All-in-One For Dummies 2e*
  • Taylor, Allen G. (Author)
47,14 EUR
Amazon

CREATE TABLE TEMP (

  [personalnr] varchar(15),

  [Emailadresse] varchar(200),

  [Ort] varchar(40)

— Here we create a temporary table to accept and store the data of the CSV file for the first time.

— The order of the column names must also be kept in the CSV file.

)

GO

BULK INSERT TEMP– Fill temporary table with CSV data

FROM ‘J:\WORK\import\mail.csv’– This is the path to the file being imported. IMPORTANT: The path refers to the path in the server, not to the machine running the management studio.

WITH

 (

    FIELDTERMINATOR=‘,’,    — Make sure that the CSV file really separates the columns with a comma and not with a semicolon.

    rowterminator=‘\n’     — In most cases –\n is correct, at least if the file was created from a German Excel version as CSV and if necessary was edited again with the editor.

  )    

GO

UPDATE mitarbeiter– The contents of the temporary table are copied to the productive table. The personnel number, which we “join” in the temporary table, functions as the assignment characteristic.

SET Emailadresse=temp.Emailadresse,Ort=temp.Ort

FROM mitarbeiter

INNERJOIN temp

ON mitarbeiter.personalnr=temp.personalnr

GO

DROP TABLE TEMP  — After all previous operations have been completed, we can delete the temporary table.

GO

All the links in this text are Amazon Affiliate Links, and by clicking on an affiliate link I will earn a percentage of your subsequent Amazon purchases. However, this circumstance does not influence the background of my product recommendation. This product recommendation is independent, honest and sincere.

Bei allen genannten Links handelt es sich um Amazon-Affiliate-Links. Durch einen Klick auf einen Affiliate-Link werde ich prozentual an Euren darauf folgenden Amazon-Einkäufen beteiligt. Dieser Umstand beeinflusst aber nicht die Hintergründe meiner Produktempfehlung. Diese Produktempfehlung erfolgt unabhängig, ehrlich und aufrichtig.