Hi Luis, that was very helpful.
Below script for a similar situation.
-- DROP THE TABLE
IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'DRPToewijzingen_STAGING')
DROP TABLE dbo.DRPToewijzingen_STAGING
GO
USE WZH
GO
CREATE TABLE DRPToewijzingen_STAGING(
[Wrkn Werknemernummer] [varchar](50) NULL,
[Wrkn Volledige naam (rooster)] [varchar](50) NULL,
[Oe Id] [varchar](50) NULL,
[Toewijzing begindatum] [varchar](50) NULL,
[Toewijzing einddatum] [varchar](50) NULL,
[Toewijzing uren (voltijd)] [varchar](50) NULL,
[Toewijzing percentage] [varchar](50) NULL,
[Toewijzing functiecode] [varchar](50) NULL,
[Toewijzing status] [varchar](50) NULL,
[Dvb Externkenmerk] [varchar](50) NULL,
[Dvb Datumindienst] [varchar](50) NULL,
[Dvb Datumuitdienst] [varchar](50) NULL,
[Medewerker Id] [varchar](50) NULL,
[Toewijzing Orgeenheid code] [varchar](50) NULL,
[Toewijzing Orgeenheid] [varchar](50) NULL
)
GO
-- DROP THE TABLE
IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'DRPToewijzingen')
DROP TABLE dbo.DRPToewijzingen
GO
CREATE TABLE DRPToewijzingen
(
IDDRPTOEWIJZINGEN BIGINT identity (1,1),
[Wrkn Werknemernummer] [varchar](50) NULL,
[Wrkn Volledige naam (rooster)] [varchar](50) NULL,
[Oe Id] [varchar](50) NULL,
[Toewijzing begindatum] DATETIME NULL,
[Toewijzing einddatum] DATETIME NULL,
[Toewijzing uren (voltijd)] DECIMAL(18,2) NULL,
[Toewijzing percentage] DECIMAL(18,2) NULL,
[Toewijzing functiecode] [varchar](50) NULL,
[Toewijzing status] [varchar](50) NULL,
[Dvb Externkenmerk] [varchar](50) NULL,
[Dvb Datumindienst] [varchar](50) NULL,
[Dvb Datumuitdienst] [varchar](50) NULL,
[Medewerker Id] [varchar](50) NULL,
[Toewijzing Orgeenheid code] [varchar](50) NULL,
[Toewijzing Orgeenheid] [varchar](50) NULL,
[LocatieNr] INT NULL,
[LocatieCode] varchar(4) NULL,
[LocatieNaam] varchar(50) NULL,
[LocatieNrFeitelijk] int NULL,
LocatieCodeFeitelijk varchar(4) NULL
)
GO
and this
USE [WZH]
GO
/**********************************
DROP CREATE spIMPORTDRPTOEW
***********************************/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spIMPORTDRPTOEW]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spIMPORTDRPTOEW]
GO
USE [WZH]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spIMPORTDRPTOEW]
AS
BEGIN
/*
JJR 20140409
IMPORT DRPToewijzingen CSV File
CONVERT STRINGS TO DATE AND INT
TRUNCATE Removes all rows from a table without logging the individual row deletions.
TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause,
however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
20140423Added columns LocatieNaam,Nr,Code etc
20140423Added column for OEGroep similar to DRPData
20140427Used Staging table
*/
TRUNCATE TABLE dbo.DRPToewijzingen
TRUNCATE TABLE dbo.DRPToewijzingen_STAGING
-- INSERT DATA FROM CSV FILE INTO STAGING table
BULK
INSERT DRPToewijzingen_STAGING
FROM 'D:\xxx\drptoewijzingen.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '',
--ERRORFILE = 'D:\ErrorRows.csv',
TABLOCK
)
UPDATE DRPToewijzingen_STAGING
SET [Toewijzing begindatum] = CONVERT(DATETIME,[Toewijzing begindatum],111)
UPDATE DRPToewijzingen_STAGING
SET [Toewijzing einddatum] = CONVERT(DATETIME,[Toewijzing einddatum],111)
-- INSERT DATA FROM STAGING TABLE INTO DRPToewijzingen table
INSERT INTO DRPToewijzingen
(
[Wrkn Werknemernummer],
[Wrkn Volledige naam (rooster)],
[Oe Id],
[Toewijzing begindatum],
[Toewijzing einddatum],
[Toewijzing uren (voltijd)],
[Toewijzing percentage],
[Toewijzing functiecode],
[Toewijzing status],
[Dvb Externkenmerk],
[Dvb Datumindienst],
[Dvb Datumuitdienst],
[Medewerker Id],
[Toewijzing Orgeenheid code],
[Toewijzing Orgeenheid],
[LocatieNr],
[LocatieCode],
[LocatieNaam],
[LocatieNrFeitelijk],
LocatieCodeFeitelijk
)
SELECT
[Wrkn Werknemernummer],
[Wrkn Volledige naam (rooster)],
[Oe Id],
[Toewijzing begindatum],
CASE
WHEN ISDATE([Toewijzing einddatum]) = 0 THEN CONVERT(DATE, '22220131', 111)
--ELSE [Toewijzing einddatum]
ELSE CONVERT(DATE, [Toewijzing einddatum], 111)
END AS
[Toewijzing einddatum],
CASE
WHEN ISNUMERIC([Toewijzing uren (voltijd)]) = 1
THEN CAST(replace([Toewijzing uren (voltijd)],',','.') AS DECIMAL(18,2))
ELSE 0
END AS
[Toewijzing uren (voltijd)],
CASE
WHEN ISNUMERIC([Toewijzing percentage]) = 1
THEN CAST(replace([Toewijzing percentage],',','.') AS DECIMAL(18,2))
ELSE 0
END AS
[Toewijzing percentage],
[Toewijzing functiecode],
[Toewijzing status],
[Dvb Externkenmerk],
[Dvb Datumindienst],
[Dvb Datumuitdienst],
[Medewerker Id],
[Toewijzing Orgeenheid code],
[Toewijzing Orgeenheid],
CASE
WHEN SUBSTRING([Toewijzing Orgeenheid code],1,2) IN ('91','92','93') THEN 91
ELSE SUBSTRING([Toewijzing Orgeenheid code],3,2)
END AS
[LocatieNr]
,NULL AS [LocatieCode]
,NULL AS [LocatieNaam]
,NULL AS [LocatieNrFeitelijk]
,NULL AS LocatieCodeFeitelijk
FROM DRPToewijzingen_STAGING
/*
UPDATE VARIOUS FIELDS IN TABLE
*/
-- LOCATIE CODE
-- Change Locatie CODE for 91,92,93 (first 2 positions of [org eenheid code] to BZ
-- Otherwise use 'normal' rule: Extract from table Huizen
UPDATE DRPToewijzingen
SET DRPToewijzingen.LocatieCode = 'BZ'
WHERE SUBSTRING([Toewijzing Orgeenheid code],1,2) IN ('91','92','93');
UPDATE DRPToewijzingen
SET DRPToewijzingen.LocatieCode = Huizen.IDHuis
FROM DRPToewijzingen INNER JOIN Huizen ON DRPToewijzingen.LocatieNr = Huizen.NrHuis
WHERE SUBSTRING([Toewijzing Orgeenheid code],1,2) NOT IN ('91','92','93');
-- LOCATIE NAAM
-- Change Locatie NAME for 91,92,93 (first 2 positions of [org eenheid code] to BEHANDELZAKEN
UPDATE DRPToewijzingen
SET DRPToewijzingen.LocatieNaam = 'Behandelzaken'
WHERE SUBSTRING([Toewijzing Orgeenheid code],1,2) IN ('91','92','93');
UPDATE DRPToewijzingen
SET DRPToewijzingen.LocatieNaam = Huizen.Naam
FROM DRPToewijzingen INNER JOIN Huizen ON DRPToewijzingen.LocatieNr = Huizen.NrHuis
WHERE SUBSTRING([Toewijzing Orgeenheid code],1,2) NOT IN ('91','92','93');
-- LOCATIE NR FEITELIJK
-- Voor behandelaars van belang. Locatie = Behandelzaken, Feitelijk kunnen ze in een huis werken
UPDATE DRPToewijzingen
SET DRPToewijzingen.LocatieNrFeitelijk = SUBSTRING(DRPToewijzingen.[Toewijzing Orgeenheid code],3,2);
-- LOCATIE CODE FEITELIJK
-- Voor behandelaars van belang. Locatie = Behandelzaken, Feitelijk kunnen ze in een huis werken
UPDATE DRPToewijzingen
SET DRPToewijzingen.LocatieCodeFeitelijk = Huizen.IDHuis
FROM DRPToewijzingen INNER JOIN Huizen ON SUBSTRING(DRPToewijzingen.[Toewijzing Orgeenheid code],3,2) = Huizen.NrHuis;
END
GO
The CSV dates were causing me some problems: day and mont got switched.
The text data looks like this:
25440;P.M. vd E;378;2003-06-01;;0,00;0,00;399001;A;25440|1;2003-02-01;;4855;5353999;Vrijwilligers
Dates are like yyyy-mm-dd. I used CONVERT(DATETIME, myDate,111) which seems to work. Still not very confident about conversion of datestrings to datetime.
Thanks for your help,
Cheers, Julian