Right now I am doing a daily bulk insert of an csv file inside an SQL DB using the following script:
Truncate table [dbo].[Table];
GO
BULK INSERT [dbo].[Table]
FROM 'C:\BulkImport\Table.csv'
WITH (FIRSTROW = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR='\n');
GO
USE DB;
GO
ALTER DATABASE DB
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (dbo_Log, 1);
GO
ALTER DATABASE DB
SET RECOVERY FULL;
GO
As you can see I am first truncating the table, then inserting the data and shrinking the log for the database (the csv file I am inserting is quite large - approx 2GB of data, and after each insert the DB size kept increasing dramatically, shrinking the log file solves this issue but I`m not sure if this is good practice or not, I am fairly new to the SQL world).
Now I want to achieve 2 things:
- I realize that truncating the whole table and inserting back the data in the csv file is not good practice, so now I want to bulk insert the file inside the database incrementally, only for new or modified data (part of the data in the file changes retroactively).
- to have created / modified date columns generated automatically upon inserting / updating data.