Cast/Convert timeveld om via bulk insert in te voegen

[AMBERTJE]

Goedemiddag aan allen,

Ik ondervind een probleem bij het runnen van een bulk insert in SQL Server Management Studio (Express versie 2012)
In het tekstbestand dat ik wil inserten zit een veld dat tijd moet voorstellen maar de lay-out van dat veld is niet conform het type datetime. vb: 0 01:23
Gevolg is dat ik met geen enkel datatype dit kan inlezen. Met dit veld moeten wel berekeningen gedaan worden dus ben ik vrij beperkt.
Mijn kennis van SQL is niet groot vandaar dat ik jullie hulp inroep

Dit is de tabel:
[SQL]
use Management_Info

if exists (select * from sys.tables where name = 'DL')
begin
drop table DL
end

Create Table [DL]
(
[Id] int identity(1,1) Not Null,
[Assignment] nvarchar(8) Not Null,
[DC] nchar(2),
[WH] nchar(1),
[Rpt_Date] smalldatetime,
[Start_Date] smalldatetime,
[Start_Time] smalldatetime,
[End_Date] smalldatetime,
[End_Time] datetime,
[Cust_Id] nchar(5),
[Assoc_Id] nchar(4),
[Rpt_Group] nchar(1),
[JobCode] nchar(5),
[Pallets] smallint,
[Cases] smallint,
[Tot_Time] smalldatetime

constraint pk_LD_Id primary key (Id)
)
[/SQL]

De code voor de bulk insert:
[SQL]
use Management_Info
go
----------------------bulkinsert(enkele records tegelijk)----------------------
BULK INSERT DL
From 'C:\BI\Management_Info\SQL Scripts\DL.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
FIRSTROW = 1 --start met inladen vanaf 1ste rij (txt bestand heeft geen headers)
)

select * from DL
[/SQL]

Een voorbeeldje van de txt file als attachment toegevoegd.
Weet iemand hoe ik dit kan omzeilen? Kan ik een cast of convert doen bij het creëren van de tabel?
Ik weet dat ik het vrij primitief aanpak maar een bulk insert is een handige tool om duizenden lijnen in te lezen (het bijgevoegd voorbeeld is drastisch ingekrompen)

Alvast bedankt op voorhand.
Grtjs,
Ambertje

Bekijk bijlage 180218

 

[AMBERTJE]

Kan er echt niemand helpen aub?

 

[Luukie]

Volgens mij heb je een afwijking tussen de kolommen in je textbestand en de kolommen die je in de tabel DL wilt opslaan. Dat zou je als eerste eens na moeten kijken. Daarnaast moet je even checken of de datatypes wel kloppen. Bijvoorbeeld voor een Total-time heb je geen smalldatetime datatype nodig maar bijv. integer.

Hierbij een scriptje waarmee je misschien op weg geholpen bent:

[SQL]delete from DL

create table #temp
(
[Id] int NOT NULL,
[Assignment] nvarchar(8) NOT NULL,
[DC] nchar(2),
[WH] nchar(100),
[Rpt_Date] smalldatetime,
[Start_Date] smalldatetime,
[Start_Time] smalldatetime,
[End_Date] VARCHAR(100),--smalldatetime,
[End_Time] VARCHAR(100),--datetime,
[Cust_Id] nchar(5),
[Assoc_Id] nchar(4),
[Rpt_Group] nchar(100),
[JobCode] nchar(5),
[Pallets] smallint,
[Cases] varchar(100),
[Tot_Time] VARCHAR(100)--smalldatetime

)

----------------------bulkinsert(enkele records tegelijk)----------------------
BULK INSERT #temp
FROM 'C:\BI\Management_Info\SQL Scripts\DL.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
FIRSTROW = 1 --start met inladen vanaf 1ste rij (txt bestand heeft geen headers)
)
select * from #temp


SET IDENTITY_INSERT DL ON

insert into [DL]
(
[Id] ,
[Assignment] ,
[DC] ,
[WH] ,
[Rpt_Date] ,
[Start_Date],
[Start_Time] ,
[End_Date] ,
[End_Time] ,
[Cust_Id] ,
[Assoc_Id],
[Rpt_Group],
[JobCode] ,
[Pallets] ,
-- [Cases] ,
[Tot_Time]
)

select [Id] ,
[Assignment],
[DC],
'A',
[WH] ,
[Rpt_Date] ,
cast([Start_Date] as datetime),
cast([Start_Time] as datetime),
cast([End_Date] as datetime),
cast([End_Time] as int),
[Cust_Id] ,
[Assoc_Id],
--[Rpt_Group],
[JobCode] ,
[Pallets] ,
--[Cases] ,
cast(SUBSTRING([Cases], 5, 5) as datetime)
-- replace([Tot_Time], '|', '')
from #temp

SET IDENTITY_INSERT DL OFF

drop table #temp

select * from DL


[/SQL]