SQL: A stored procedure used to import CSV file data to a specified ms SQL table

Source: Internet
Author: User

Recently, due to work needs, from an applicationProgramExport a CSV data table and then import it to the ms SQL data table of another application environment, because it involves the import of multiple CSV export files
Therefore, a stored procedure is written to give it some flexibility. You can specify the path where the CSV file is stored, the name of the CSV file, and the name of the imported ms SQL data table. In this way, we
In. netCodeTo import the specified CSV file at the specified position to the specified ms SQL data table.
Note: before importing a CSV file, you must create a data table corresponding to the CSV file in ms SQL.
For example:
CSV file content:
Area machine Job
Plant 1 l02m35 1774-dp5156
Plant 1 l02m67 1831-dp5156
Plant 1 l02m02 1832-dp5156
Plant 1 l03m44 1792-d115
Plant 1 l05m29 1725-dp4975
Plant 1 l05m29 1723-dp4975
Plant 1 l05m29 1820-dp4975
Then we need to create a ms SQL data table with fields:

Area, machine, job

The fields of the ms SQL data table correspond one to one with the fields of the CSV file content.

The stored procedure can be used for import. The content of the stored procedure is as follows:

Create procedure [DBO]. [my_import_csvfile]
@ Sourcefilepath nvarchar ( 200 ), -- The folder Where You upload the CSV file on your server. eg: ' D: \ dataexchange \ csvtmp '
@ Sourcefilename nvarchar ( 100 ), -- The CSV filename that you want to import. eg: ' Exportfileone.csv '
@ Desttablename nvarchar ( 100 ) -- The destination table name eg: ' Mssqlexportfiletableone '
As
Begin

Declare @ delsqlstr nvarchar (600)
Declare @ impsqlstr nvarchar (600)

--Clear the content of the destination table
Set @ delsqlstr='Delete from' +@ Desttablename
Exec sp_executesql @ delsqlstr

-- Import CSV data into the destination table
Set @ impsqlstr = ' Insert ' + @ Desttablename
+ ' Select * From OpenRowSet ( '' Msdasql '' , '' Driver = {Microsoft text Driver (*. txt; *. CSV )};
Defaultdir = ' + @ Sourcefilepath + ' ; Extensions = CSV; '' , '' Select * From " '+ @ Sourcefilename +' "'' ) '

Exec sp_executesql @ impsqlstr

Select @ rowcount

End

-- ================ Excute example ==================================
--
-- Use [mytestdb]
-- Go
--
-- Declare @ return_value Int
--
-- Exec @ return_value = [DBO]. [my_import_csvfile]
-- @ Sourcefilepath = N ' D: \ dataexchange \ csvtmp ' ,
-- @ Sourcefilename = N ' Exportfileone.csv ' ,
-- @ Desttablename = N ' Mssqlexportfiletableone '
--
-- Select ' Return Value '   = @ Return_value
--
-- Go
--
-- Select * From DBO. mssqlexportfiletableone

 

This code is successfully debugged in MS sql2005.

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.