Bulk insert and upadate
USE [archive]
GO
/****** Object: UserDefinedTableType [dbo].[mdCvLibraryType] Script Date: 3/21/2017 9:45:16 AM ******/
DROP TYPE [dbo].[mdCvLibraryType]
GO
/****** Object: UserDefinedTableType [dbo].[mdCvLibraryType] Script Date: 3/21/2017 9:45:17 AM ******/
CREATE TYPE [dbo].[mdCvLibraryType] AS TABLE(
[JobRef] [int] NOT NULL,
[Date] [datetime] NULL,
[Title] [nvarchar](100) NULL,
[Company] [nvarchar](100) NULL,
[Email] [nvarchar](100) NULL,
[Url] [nvarchar](1000) NULL,
[SalaryMin] [nvarchar](50) NULL,
[SalaryMax] [nvarchar](50) NULL,
[Benefits] [nvarchar](500) NULL,
[Salary] [nvarchar](100) NULL,
[JobType] [nvarchar](100) NULL,
[Full_Part] [nvarchar](100) NULL,
[Salary_Per] [nvarchar](100) NULL,
[Location] [nvarchar](500) NULL,
[Country] [nvarchar](100) NULL,
[Description] [nvarchar](max) NULL,
[Category] [nvarchar](500) NULL,
[Image] [nvarchar](1000) NULL
)
GO
USE [staging]
GO
/****** Object: StoredProcedure [dbo].[mdCvLibraryBulkAdd] Script Date: 3/21/2017 9:46:42 AM ******/
DROP PROCEDURE [dbo].[mdCvLibraryBulkAdd]
GO
/****** Object: StoredProcedure [dbo].[mdCvLibraryBulkAdd] Script Date: 3/21/2017 9:46:42 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--more info
-- https://www.aspsnippets.com/Articles/SqlBulkCopy--Bulk-Insert-records-and-Update-existing-rows-if-record-exists-using-C-and-VBNet.aspx
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[mdCvLibraryBulkAdd]
@CvlJobList mdCvLibraryType READONLY
AS
BEGIN
SET NOCOUNT ON;
MERGE INTO mdCvLibrary as cvl
USING @CvlJobList as jobList
ON cvl.JobRef = jobList.JobRef
WHEN MATCHED THEN
UPDATE set
cvl.Title = jobList.Title,
cvl.SalaryMin = jobList.SalaryMin,
cvl.SalaryMax = jobList.SalaryMax,
cvl.Benefits = jobList.Benefits,
cvl.Salary = jobList.Salary,
cvl.CvDescription = jobList.Description,
cvl.ImageUrl = jobList.Image,
cvl.Updated = GETDATE()
WHEN NOT MATCHED THEN
INSERT VALUES (jobList.JobRef,
jobList.Date,
jobList.Title,
jobList.Company,
jobList.Email,
jobList.Url,
jobList.SalaryMin,
jobList.SalaryMax,
jobList.Benefits,
jobList.Salary,
jobList.JobType,
jobList.Full_Part,
jobList.Salary_Per,
jobList.Location,
jobList.Country,
jobList.Description,
jobList.Category,
jobList.Image,
GETDATE(),
NULL);
END
GO