Import data from an Excel table to a database (stored procedures, database jobs)

[Title]
Import data from an Excel table to a database

[Requirement]
To implement a spot price website, you must import the data in the Excel table to the site bit table periodically. Here we use the database job implementation.

[STEP]

1. generate an Excel table
Worksheet Name:Website
Included columns:Spid, spname, spiniprice, spincextent, spnowprice, spimage, spbidnum, spauctype, spbaucdt, speaucdt, spstat, buyerid, sitename, siteurl
 
Part:
 

2. Stored Procedure
 
-- 1. involved tables: wl_siteposition, wl_comsite, and Excel)
-- 2. function: If the website bit table does not have a record, import the record from the Excel table; otherwise, import the record from the website bit table to the spot price website table,
-- At the same time, clear the website bit table, and then import the data in the Excel table to the table wl_siteposition;

Create procedure wl_job_websiteauction

Declare @ err1 int
Declare @ err2 int
Declare @ err3 int
Declare @ err4 int
Declare @ err5 int
Declare @ err6 int

Set @ err1 = 0
Set @ err2 = 0
Set @ err3 = 0
Set @ err4 = 0
Set @ err5 = 0
Set @ err6 = 0

Set nocount on
-- Set the transaction isolation level
-- SET transaction isolation level serializable
-- OLE/DB provider does not support isolation layer
Begin tran -- start transaction

-- Set xact_abort on --

If (select count (*) from wl_siteposition)> 0
Begin
-- Import data from the bid table to the bid table
Truncate table wl_comsite -- clear the spot price website table
Set @ err1 =error

-- Import data from the bid table to the bid table
Insert into wl_comsite (csitename, csiteurl, csitevdt, spid, buyerid)
Select sitename, siteurl, dateadd (day, 7, speaucdt) as csitevdt, spid, buyerid
From wl_siteposition order by spid
Set @ err2 = @ Error

Truncate table wl_siteposition -- clear the website bit table
Set @ err3 =@@ Error

-- Import data from an Excel table to a site location table
Insert into wl_siteposition (spid, spname, spiniprice, spincextent, spnowprice, spimage, spbidnum, spauctype, spbaucdt, speaucdt, spstat, buyerid, sitename, siteurl)
Select spid, spname, spiniprice, spincextent, spnowprice, spimage, spbidnum, spauctype, spbaucdt, speaucdt, spstat, buyerid, sitename, siteurl
FromOpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "D: \ work \ I am proud \ bidding design \ websiteposition_data.xls"; extended properties = "Excel 8.0"; persist Security info = false ')... [website $]
Set @ err4 =@@ Error

End
Else
Begin


-- Import data from an Excel table to a site location table
Insert into wl_siteposition (spid, spname, spiniprice, spincextent, spnowprice, spimage, spbidnum, spauctype, spbaucdt, speaucdt, spstat, buyerid, sitename, siteurl)
Select spid, spname, spiniprice, spincextent, spnowprice, spimage, spbidnum, spauctype, spbaucdt, speaucdt, spstat, buyerid, sitename, siteurl
From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "D: \ work \ I am proud \ bidding design \ websiteposition_data.xls"; extended properties = "Excel 8.0"; persist Security info = false ')... [website $]
Set @ err5 = @ Error

-- Import data from the bid table to the bid table
Insert into wl_comsite (csitename, csiteurl, csitevdt, spid, buyerid)
Select sitename, siteurl, dateadd (day, 7, speaucdt) as csitevdt, spid, buyerid
From wl_siteposition order by spid
Set @ err6 =@@ Error

End

If @ err1 = 0 and @ err2 = 0 and @ err3 = 0 and @ err4 = 0 and @ err5 = 0 and @ err6 = 0

Commit tran
Else
Rollback tran

Set nocount off
Go

3. Create a database job

Note: the database server must start the SQL Server proxy.

Note]
1. Use absolute paths for Excel tables
2. the worksheet name must be consistent. Here we use website.