April 26, 2014 at 10:41 am
New at SQL Server 2008 R2 ...
The script below generates a stored procedure. Just recently I start getting an error message when running this script. The error message points to this line:
SET [Duur]= round(([Duur (in tijd)] /100),0,1)*60 + ([Duur (in tijd)] - round(([Duur (in tijd)]/100),0,1)*100)
And it states that
Duur
is an invalid column name. Earlier I never got this error message.
Msg 207, Level 16, State 1, Procedure spIMPORTDRPDATA, Line 74
Invalid column name 'Duur'.
Any suggestions?
Cheers, Julian
USE [WZH]
GO
/**********************************
DROP CREATE spIMPORTDRPDATA
***********************************/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spIMPORTDRPDATA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spIMPORTDRPDATA]
GO
USE [WZH]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spIMPORTDRPDATA]
AS
BEGIN
/*
JJR 20140313
IMPORT CSV DATA
CONVERT STRING TO DATE AND INT
ALTER DATATYPE COLUMNS
*/
-- DROP THE TABLE
IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'DRPDATA')
DROP TABLE dbo.DRPDATA
--CREATE TEST TABLE
CREATE TABLE DRPDATA (
Dienstcode varchar(50),
Begindatum varchar(12),
Sorteernaam varchar(100),
[Org eenheid code] varchar(20),
Begintijd int,
Eindtijd int,
[Duur (in tijd)] varchar(20),
[Afwijkende tijd J/N] varchar(10),
Roosteritemstatus varchar(50),
[Ri Soortdienst] varchar (50),
[Ri Mdw Externkenmerk] varchar(50),
[Ri Functiecode] varchar(50),
[Ri Functieoms] varchar(100))
-- INSERT DATA FROM CSV FILE
BULK
INSERT DRPDATA
FROM 'D:\xxx\drpoutput2.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '',
--ERRORFILE = 'D:\ErrorRows.csv',
TABLOCK
)
-- CHANGE DATATYPE BEGINDATUM TO DATETIME
ALTER TABLE WZH.DBO.DRPDATA
ALTER COLUMN Begindatum DATETIME NULL
-- UPDATE THE FIELD BEGINDATUM AS DATE
UPDATE DRPDATA
SET Begindatum = CASE
WHEN ISDATE(Begindatum) = 0 THEN CONVERT(DATE, '30/01/2222', 103)
ELSE CONVERT(DATE, Begindatum, 103)
END
-- UPDATE THE FIELD [Duur in Tijd] AS Integer
UPDATE DRPDATA
SET [Duur (in tijd)] = CASE
WHEN ISNUMERIC([Duur (in tijd)]) = 1 THEN CAST([Duur (in tijd)] AS INT)
ELSE 0
END
--CHANGE DATATYPE TO INT
ALTER TABLE WZH.DBO.DRPDATA
ALTER COLUMN [Duur (in tijd)] INT NULL
-- ADD COLUMN DUUR(MIN)
ALTER TABLE WZH.DBO.DRPDATA
ADD [Duur] INT NULL
-- Truncate the result of the division, add minutes
UPDATE WZH.DBO.DRPDATA
SET [Duur]= round(([Duur (in tijd)] /100),0,1)*60 + ([Duur (in tijd)] - round(([Duur (in tijd)]/100),0,1)*100);
-- ADD COLUMN Locatie
ALTER TABLE WZH.DBO.DRPDATA
ADD [LocatieNr] INT NULL
-- ADD COLUMN LocatieCode
ALTER TABLE WZH.DBO.DRPDATA
ADD [LocatieCode] varchar(4) NULL
-- ADD COLUMN LocatieNaam
ALTER TABLE WZH.DBO.DRPDATA
ADD [LocatieNaam] varchar(50) NULL
-- ADD COLUMN LocatieNrFeitelijk
ALTER TABLE WZH.DBO.DRPDATA
ADD [LocatieNrFeitelijk] int NULL
-- ADD COLUMN LocatieCodeFeitelijk
ALTER TABLE WZH.DBO.DRPDATA
ADD LocatieCodeFeitelijk varchar(4) NULL
-- ADD COLUMN MwNr
ALTER TABLE WZH.DBO.DRPDATA
ADD [MwID] INT NULL
-- ADD COLUMN DvbNr
ALTER TABLE WZH.DBO.DRPDATA
ADD [DvbNr] INT NULL
-- ADD COLUMN NiveauZPT
ALTER TABLE WZH.DBO.DRPDATA
ADD [NiveauZPT] varchar(50) NULL
-- ADD COLUMN DienstGroep
ALTER TABLE WZH.DBO.DRPDATA
ADD [DienstGroep] varchar(50) NULL
-- ADD COLUMN OEOmschrijving
ALTER TABLE WZH.DBO.DRPDATA
ADD [OEOmschrijving] varchar(100) NULL;
-- ADD COLUMN OEGroep
ALTER TABLE WZH.DBO.DRPDATA
ADD OEGroep varchar(100) NULL;
-- ADD COLUMN AfdelingZPT
ALTER TABLE WZH.DBO.DRPDATA
ADD AfdelingZPT varchar(4) NULL;
-- ADD COLUMN FTEHrs
ALTER TABLE WZH.DBO.DRPDATA
ADD [FTEHrs] float NULL;
-- ADD COLUMN FlexVast
ALTER TABLE WZH.DBO.DRPDATA
ADD [FlexVast] varchar(10) NULL;
-- 15) Change LocatieNr for 91,92,93 (first 2 positions of [org eenheid code] to 91
-- Otherwise use 'normal' rule
UPDATE DRPDATA
SET DRPDATA.LocatieNr = CASE
WHEN SUBSTRING([Org eenheid code],1,2) IN ('91','92','93') THEN 91
ELSE SUBSTRING([Org eenheid code],3,2)
END
-- 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 DRPDATA
SET DRPDATA.LocatieCode = 'BZ'
WHERE SUBSTRING([Org eenheid code],1,2) IN ('91','92','93');
UPDATE DRPDATA
SET DRPDATA.LocatieCode = Huizen.IDHuis
FROM DRPDATA INNER JOIN Huizen ON DRPDATA.LocatieNr = Huizen.NrHuis
WHERE SUBSTRING([Org eenheid 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 DRPDATA
SET DRPDATA.LocatieNaam = 'Behandelzaken'
WHERE SUBSTRING([Org eenheid code],1,2) IN ('91','92','93');
UPDATE DRPDATA
SET DRPDATA.LocatieNaam = Huizen.Naam
FROM DRPDATA INNER JOIN Huizen ON DRPDATA.LocatieNr = Huizen.NrHuis
WHERE SUBSTRING([Org eenheid 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 DRPDATA
SET DRPDATA.LocatieNrFeitelijk = SUBSTRING(drpdata.[Org eenheid code],3,2);
-- LOCATIE CODE FEITELIJK
-- Voor behandelaars van belang. Locatie = Behandelzaken, Feitelijk kunnen ze in een huis werken
UPDATE DRPDATA
SET DRPDATA.LocatieCodeFeitelijk = Huizen.IDHuis
FROM DRPDATA INNER JOIN Huizen ON SUBSTRING(drpdata.[Org eenheid code],3,2) = Huizen.NrHuis;
-- OEGROEP
UPDATE DRPDATA
SET DRPDATA.OEGroep = 'Overhead'
WHERE SUBSTRING([Org eenheid code],5,3) >= 100 and SUBSTRING([Org eenheid code],5,3) <= 399;
UPDATE DRPDATA
SET DRPDATA.OEGroep = 'Facilitair'
WHERE SUBSTRING([Org eenheid code],5,3) >= 400 and SUBSTRING([Org eenheid code],5,3) <= 499;
UPDATE DRPDATA
SET DRPDATA.OEGroep = 'Overhead'
WHERE SUBSTRING([Org eenheid code],5,3) >= 500 and SUBSTRING([Org eenheid code],5,3) <= 899;
UPDATE DRPDATA
SET DRPDATA.OEGroep = 'Locatiefactor'
WHERE SUBSTRING([Org eenheid code],5,3) >= 900 and SUBSTRING([Org eenheid code],5,3) <= 911;
UPDATE DRPDATA
SET DRPDATA.OEGroep = 'Zorg'
WHERE SUBSTRING([Org eenheid code],5,3) >= 912 and SUBSTRING([Org eenheid code],5,3) <= 969;
UPDATE DRPDATA
SET DRPDATA.OEGroep = 'ZIDB'
WHERE SUBSTRING([Org eenheid code],5,3) >= 970 and SUBSTRING([Org eenheid code],5,3) <= 979;
UPDATE DRPDATA
SET DRPDATA.OEGroep = 'Dagactiviteiten'
WHERE SUBSTRING([Org eenheid code],5,3) >= 980 and SUBSTRING([Org eenheid code],5,3) <= 989;
UPDATE DRPDATA
SET DRPDATA.OEGroep = 'Overhead'
WHERE SUBSTRING([Org eenheid code],5,3) >= 990 and SUBSTRING([Org eenheid code],5,3) <= 999;
--UPDATE DRPDATA
--SET DRPDATA.OEGroep = 'Facilitair'
--WHERE SUBSTRING([Org eenheid code],1,2) = 80;
-- Extract MwNr, chars Before pipe char
UPDATE DRPDATA
SET MwID = substring([Ri Mdw Externkenmerk],1,CHARINDEX ('|', [Ri Mdw Externkenmerk])-1)
-- Extract Dienstverband nummer, DvbNr, chars After pipe char
UPDATE DRPDATA
SET DvbNr = substring([Ri Mdw Externkenmerk],CHARINDEX ('|', [Ri Mdw Externkenmerk])+1,LEN([Ri Mdw Externkenmerk]))
-- Extract NiveauZPT from MasterTabelFuncties
UPDATE DRPDATA
SET DRPDATA.NiveauZPT = MasterTabelFuncties.NiveauZPT
FROM DRPDATA INNER JOIN MasterTabelFuncties ON DRPDATA.[Ri Functiecode] = MasterTabelFuncties.IDFunctie
-- Extract DienstGroep from MasterTabelRISoortDienst
UPDATE DRPDATA
SET DRPDATA.DienstGroep = MasterTabelRISoortDienst.DienstGroep
FROM DRPDATA INNER JOIN MasterTabelRISoortDienst ON DRPDATA.[Ri Soortdienst] = MasterTabelRISoortDienst.RiSoortDienst;
-- Extract OE Omschrijving from MasterTabelOECode
UPDATE DRPDATA
SET DRPDATA.OEOmschrijving = MasterTabelOECode.OEOmschrijving
FROM DRPDATA INNER JOIN MasterTabelOECode ON DRPDATA.[Org eenheid code] = MasterTabelOECode.OECode;
-- EXTRACT FTE HRS FROM TABLE DRPToewijzingen
UPDATE DRPDATA
SET DRPDATA.FTEHrs = t.[Toewijzing uren (voltijd)]
FROM DRPDATA AS D inner JOIN
DRPToewijzingen AS T ON D.MwID = T.[Wrkn Werknemernummer]
AND D.[Org eenheid code] = T.[Toewijzing Orgeenheid code]
AND D.Begindatum BETWEEN T.[Toewijzing begindatum] AND T.[Toewijzing einddatum];
-- UPDAT VASTFLEX COLUMN
UPDATE DRPDATA
SET DRPDATA.FLEXVAST = CASE
WHEN FTEHrs = 0 THEN 'FLEX'
WHEN FTEHrs > 0 THEN 'VAST'
ELSE 'ONB'
END;
-- ADD NEW ORG EENHEID CODES TO MasterTabelAfdelingenDRP
INSERT INTO MasterTabelAfdelingenDRP ([Org eenheid code],LocatieCode,LocatieNr,Locatienaam,OEGroep,OEOmschrijving)
SELECT DISTINCT D.[Org eenheid code], D.LocatieCode, D.LocatieNr, D.Locatienaam, D.OEGroep, D.OEOmschrijving
FROM DRPDATA as D left outer join MasterTabelAfdelingenDRP as A on D.[Org eenheid code] = A.[Org eenheid code]
where A.[Org eenheid code] is null;
-- ADD Afdeling (A01 etc) to field AfdelingZPT. Get from table MasterTabelAfdelingenDRP
UPDATE DRPDATA
SET AfdelingZPT = MasterTabelAfdelingenDRP.ZPTIdAfd
FROM DRPDATA AS d INNER JOIN
MasterTabelAfdelingenDRP ON d.LocatieCode = MasterTabelAfdelingenDRP.LocatieCode AND
d.[Org eenheid code] = MasterTabelAfdelingenDRP.[Org eenheid code];
-- ADD IDENTITY COLUMN
ALTER TABLE DBO.DRPDATA
ADD IDDRPDATA BIGINT identity(1,1);
END
GO
April 26, 2014 at 11:02 am
If I understand correctly, you're loading a file into a table that gets recreated each time. You create the table with the columns from the file and add the other columns after you loaded the file.
Why don't you create the table correctly and use a formatfile with your bulk insert? Even better, why don't you use a staging table?
You could also change your update statements to use fewer of them.
April 26, 2014 at 11:11 am
Hi Luis,
Thank you. I am new to SQL Server and am learning and developing at the same time. (Excel front end, SQL Sever back end).
You guessed right. Every morning a csv file is generated, I need to process/manipulate that data for a number of reports.
Did a quick search on "staging tables" and might give that a shot.
Cheers,
Julian
April 26, 2014 at 11:36 am
Just because I thought it would be fun to play around to give you an example, here's something I would propose you to try.
Assuming that you have 2 permanent tables as follows:
CREATE TABLE DRPDATA_STAGING (
Dienstcode VARCHAR(50)
,Begindatum VARCHAR(12)
,Sorteernaam VARCHAR(100)
,[Org eenheid code] VARCHAR(20)
,Begintijd INT
,Eindtijd INT
,[Duur (in tijd)] VARCHAR(20)
,[Afwijkende tijd J/N] VARCHAR(10)
,Roosteritemstatus VARCHAR(50)
,[Ri Soortdienst] VARCHAR(50)
,[Ri Mdw Externkenmerk] VARCHAR(50)
,[Ri Functiecode] VARCHAR(50)
,[Ri Functieoms] VARCHAR(100)
)
CREATE TABLE DRPDATA (
IDDRPDATA BIGINT identity (1,1)
,Dienstcode VARCHAR(50)
,Begindatum DATETIME
,Sorteernaam VARCHAR(100)
,[Org eenheid code] VARCHAR(20)
,Begintijd INT
,Eindtijd INT
,[Duur (in tijd)] INT
,[Afwijkende tijd J/N] VARCHAR(10)
,Roosteritemstatus VARCHAR(50)
,[Ri Soortdienst] VARCHAR(50)
,[Ri Mdw Externkenmerk] VARCHAR(50)
,[Ri Functiecode] VARCHAR(50)
,[Ri Functieoms] VARCHAR(100)
,[Duur] INT NULL
,[LocatieNr] INT NULL
,[LocatieCode] VARCHAR(4) NULL
,[LocatieNaam] VARCHAR(50) NULL
,[LocatieNrFeitelijk] INT NULL
,LocatieCodeFeitelijk VARCHAR(4) NULL
,[MwID] INT NULL
,[DvbNr] INT NULL
,[NiveauZPT] VARCHAR(50) NULL
,[DienstGroep] VARCHAR(50) NULL
,[OEOmschrijving] VARCHAR(100) NULL
,OEGroep VARCHAR(100) NULL
,AfdelingZPT VARCHAR(4) NULL
,[FTEHrs] FLOAT NULL
,[FlexVast] VARCHAR(10) NULL
)
You could save some steps with this:
TRUNCATE TABLE dbo.DRPDATA
TRUNCATE TABLE dbo.DRPDATA_STAGING
-- INSERT DATA FROM CSV FILE
BULK INSERT DRPDATA_STAGING
FROM 'D:\xxx\drpoutput2.csv' WITH (
FIRSTROW = 2
,FIELDTERMINATOR = ';'
,ROWTERMINATOR = ''
,
--ERRORFILE = 'D:\ErrorRows.csv',
TABLOCK
)
INSERT INTO DRPDATA(
Dienstcode
,Begindatum
,Sorteernaam
,[Org eenheid code]
,Begintijd
,Eindtijd
,[Duur (in tijd)]
,[Afwijkende tijd J/N]
,Roosteritemstatus
,[Ri Soortdienst]
,[Ri Mdw Externkenmerk]
,[Ri Functiecode]
,[Ri Functieoms]
,[Duur]
,[LocatieNr]
,[LocatieCode]
,[LocatieNaam]
,[LocatieNrFeitelijk]
,[MwID]
,[DvbNr]
,OEGroep
,AfdelingZPT
,[FTEHrs]
,[FlexVast]
)
SELECT Dienstcode
,CASE
WHEN ISDATE(Begindatum) = 0
THEN CONVERT(DATE, '30/01/2222', 103)
ELSE CONVERT(DATE, Begindatum, 103)
END AS Begindatum
,Sorteernaam
,[Org eenheid code]
,Begintijd
,Eindtijd
,CASE
WHEN [Duur (in tijd)] NOT LIKE '%[^0-9]%'
THEN CAST([Duur (in tijd)] AS INT)
ELSE 0
END AS [Duur (in tijd)]
,[Afwijkende tijd J/N]
,Roosteritemstatus
,[Ri Soortdienst]
,[Ri Mdw Externkenmerk]
,[Ri Functiecode]
,[Ri Functieoms]
,CASE
WHEN [Duur (in tijd)] NOT LIKE '%[^0-9]%'
THEN round(([Duur (in tijd)] / 100), 0, 1) * 60 +
([Duur (in tijd)] - round(([Duur (in tijd)] / 100), 0, 1) * 100)
END AS [Duur]
,CASE
WHEN SUBSTRING([Org eenheid code], 1, 2) IN ('91','92','93')
THEN 91
ELSE SUBSTRING([Org eenheid code], 3, 2)
END AS [LocatieNr]
,CASE
WHEN SUBSTRING([Org eenheid code], 1, 2) IN ('91','92','93')
THEN 'BZ' END AS LocatieCode
,CASE
WHEN SUBSTRING([Org eenheid code], 1, 2) IN ('91','92','93')
THEN 'Behandelzaken' END AS [LocatieNaam]
,SUBSTRING([Org eenheid code], 3, 2) AS LocatieNrFeitelijk
,substring([Ri Mdw Externkenmerk], 1, CHARINDEX('|', [Ri Mdw Externkenmerk]) - 1) AS [MwID]
,substring([Ri Mdw Externkenmerk], CHARINDEX('|', [Ri Mdw Externkenmerk]) + 1, LEN([Ri Mdw Externkenmerk])) AS [DvbNr]
,CASE WHEN SUBSTRING([Org eenheid code], 5, 3) >= 100
AND SUBSTRING([Org eenheid code], 5, 3) <= 399
THEN 'Overhead'
WHEN SUBSTRING([Org eenheid code], 5, 3) >= 400
AND SUBSTRING([Org eenheid code], 5, 3) <= 499
THEN 'Facilitair'
WHEN SUBSTRING([Org eenheid code], 5, 3) >= 500
AND SUBSTRING([Org eenheid code], 5, 3) <= 899
THEN 'Overhead'
WHEN SUBSTRING([Org eenheid code], 5, 3) >= 900
AND SUBSTRING([Org eenheid code], 5, 3) <= 911
THEN 'Locatiefactor'
WHEN SUBSTRING([Org eenheid code], 5, 3) >= 912
AND SUBSTRING([Org eenheid code], 5, 3) <= 969
THEN 'Zorg'
WHEN SUBSTRING([Org eenheid code], 5, 3) >= 970
AND SUBSTRING([Org eenheid code], 5, 3) <= 979
THEN 'ZIDB'
WHEN SUBSTRING([Org eenheid code], 5, 3) >= 980
AND SUBSTRING([Org eenheid code], 5, 3) <= 989
THEN 'Dagactiviteiten'
WHEN SUBSTRING([Org eenheid code], 5, 3) >= 990
AND SUBSTRING([Org eenheid code], 5, 3) <= 999
THEN 'Overhead'
END AS OEGroep
,NULL AS AfdelingZPT
,NULL AS [FTEHrs]
,NULL AS [FlexVast]
FROM dbo.DRPDATA_STAGING
-- 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 DRPDATA SET
LocatieCode = Huizen.IDHuis
,LocatieNaam = Huizen.Naam
FROM DRPDATA
INNER JOIN Huizen ON DRPDATA.LocatieNr = Huizen.NrHuis
WHERE SUBSTRING([Org eenheid code], 1, 2) NOT IN ('91','92','93');
-- LOCATIE CODE FEITELIJK
-- Voor behandelaars van belang. Locatie = Behandelzaken, Feitelijk kunnen ze in een huis werken
UPDATE DRPDATA
SET DRPDATA.LocatieCodeFeitelijk = Huizen.IDHuis
FROM DRPDATA
INNER JOIN Huizen ON SUBSTRING(drpdata.[Org eenheid code], 3, 2) = Huizen.NrHuis;
-- Extract NiveauZPT from MasterTabelFuncties
UPDATE DRPDATA
SET DRPDATA.NiveauZPT = MasterTabelFuncties.NiveauZPT
FROM DRPDATA
INNER JOIN MasterTabelFuncties ON DRPDATA.[Ri Functiecode] = MasterTabelFuncties.IDFunctie
-- Extract DienstGroep from MasterTabelRISoortDienst
UPDATE DRPDATA
SET DRPDATA.DienstGroep = MasterTabelRISoortDienst.DienstGroep
FROM DRPDATA
INNER JOIN MasterTabelRISoortDienst ON DRPDATA.[Ri Soortdienst] = MasterTabelRISoortDienst.RiSoortDienst;
-- Extract OE Omschrijving from MasterTabelOECode
UPDATE DRPDATA
SET DRPDATA.OEOmschrijving = MasterTabelOECode.OEOmschrijving
FROM DRPDATA
INNER JOIN MasterTabelOECode ON DRPDATA.[Org eenheid code] = MasterTabelOECode.OECode;
-- EXTRACT FTE HRS FROM TABLE DRPToewijzingen
UPDATE DRPDATA
SET DRPDATA.FTEHrs = t.[Toewijzing uren (voltijd)]
FROM DRPDATA AS D
INNER JOIN DRPToewijzingen AS T ON D.MwID = T.[Wrkn Werknemernummer]
AND D.[Org eenheid code] = T.[Toewijzing Orgeenheid code]
AND D.Begindatum BETWEEN T.[Toewijzing begindatum]
AND T.[Toewijzing einddatum];
-- UPDAT VASTFLEX COLUMN
UPDATE DRPDATA
SET DRPDATA.FLEXVAST = CASE
WHEN FTEHrs = 0
THEN 'FLEX'
WHEN FTEHrs > 0
THEN 'VAST'
ELSE 'ONB'
END;
-- ADD NEW ORG EENHEID CODES TO MasterTabelAfdelingenDRP
INSERT INTO MasterTabelAfdelingenDRP (
[Org eenheid code]
,LocatieCode
,LocatieNr
,Locatienaam
,OEGroep
,OEOmschrijving
)
SELECT DISTINCT D.[Org eenheid code]
,D.LocatieCode
,D.LocatieNr
,D.Locatienaam
,D.OEGroep
,D.OEOmschrijving
FROM DRPDATA AS D
LEFT OUTER JOIN MasterTabelAfdelingenDRP AS A ON D.[Org eenheid code] = A.[Org eenheid code]
WHERE A.[Org eenheid code] IS NULL;
-- ADD Afdeling (A01 etc) to field AfdelingZPT. Get from table MasterTabelAfdelingenDRP
UPDATE DRPDATA
SET AfdelingZPT = MasterTabelAfdelingenDRP.ZPTIdAfd
FROM DRPDATA AS d
INNER JOIN MasterTabelAfdelingenDRP ON d.LocatieCode = MasterTabelAfdelingenDRP.LocatieCode
AND d.[Org eenheid code] = MasterTabelAfdelingenDRP.[Org eenheid code];
Note that:
- You don't need to add columns during the procedure.
- The insert is using less columns than the columns from the table by using a column list.
- There can be more than one column updated in an update statement.
- I left some columns out of the insert because I don't know your data and can't understand the column names to even guess what they could mean. You could add them if you think that is safe to do it.
- I changed the ISNUMERIC function to a comparison to have only digits. The reason is explained here[/url].
If you have any questions, feel free to ask.
April 26, 2014 at 1:55 pm
Hi Luis,
I really appreciate this, thank you. 🙂
Tomorrow, Sunday, I will try your code.
Thanks,
Julian,
Spain (no hablar Espanol)
April 27, 2014 at 1:00 pm
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
April 28, 2014 at 1:34 pm
How did you get the code 111 for CONVERT? That's completely wrong for YYYY-MM-DD. You could use code 120 or 121, however, that's an ISO stardard format so you shouldn't get those issues.
For a better reference on CONVERT codes for datetime values, check Books On Line: http://msdn.microsoft.com/en-us/library/ms187928.aspx
April 28, 2014 at 1:51 pm
That's the same link I posted. 111 is the code referenced as Japan's standard which is defined as yyyy/mm/dd. That's not exactly yyyy-mm-dd.
Dutch language uses the format dmy, so that shouldn't be an issue and the format you're using is the ISO standard.
What happens when you run this code?
SELECT CONVERT( char(20), CONVERT( datetime, '2014-04-08'))
April 28, 2014 at 4:18 pm
I get the following
(No column name)
aug 4 2014 12:00AM
April 29, 2014 at 11:43 am
Am I correct to assume that @myDate is our starting point?
And that CONVERT only changes the display of the date string according to the added parameter (111 or 105).
Then the calculations should give the same results...
J.
How do you "tell" SQL Server that @myDate is in this format: yyyy-mm-dd?
Declare @myDate varchar(50)
Set @myDate = '2014-04-08'
SELECT CONVERT( char(20), CONVERT( datetime, @myDate))
select (convert(datetime, @myDate,111))+22
select convert(char(20), (convert(datetime, @myDate,111))+22)
select (convert(datetime, @myDate,105))+22
select convert(char(20), (convert(datetime, @myDate,105))+22)
This gives identical results:
Declare @myDate varchar(50)
Set @myDate = '2014/dec/10' --yyyy-mm-dd
SELECT CONVERT( char(20), CONVERT( datetime, @myDate))
select (convert(datetime, @myDate,111))+22
select convert(char(20), (convert(datetime, @myDate,111))+22)
select (convert(datetime, @myDate,105))+22
select convert(char(20), (convert(datetime, @myDate,105))+22)
April 29, 2014 at 12:09 pm
What about using the code 120?
CONVERT( datetime, @myDate, 120)
April 29, 2014 at 12:23 pm
I might strip out the "-" to prevent issues.
See this post, last message.
Here I get consistant results:
Declare @myDate varchar(50)
Set @myDate = '20141210' --yyyymmdd
SELECT CONVERT( char(20), CONVERT( datetime, @myDate))
select (convert(datetime, @myDate,111))+22
select convert(char(20), (convert(datetime, @myDate,111))+22)
select (convert(datetime, @myDate,105))+22
select convert(char(20), (convert(datetime, @myDate,105))+22)
select (convert(datetime, @myDate,120))+22
select convert(char(20), (convert(datetime, @myDate,120))+22)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply