Import data from CSV file into SQL Server and by adding a new column in the result table import data

Question

I'm working on a project to create a system that can import data from CSV files into tables in SQL Server automatically every day. The final goal for this project is that I have two tables table-master and table-data, with table-master containing information regarding files that were successfully imported and table-data containing the data from imported .csv files.

But in table-data there are additional columns that are not in the .csv file and these columns contain related information and will be entered into table-master.

I've successfully tried to import data from .csv into SQL Server using bulk insert, but I don't understand how to add columns to the table that results from importing data from csv.

The table-master contains the following columns:

Id = auto-generate
Name-file = contains the format REGISTRATION-yyyymmdd-hhmmss
User_process_date
Create Date = processing date
Create By = default system
Change Date = processing date
Change By = system default

and table-data contains consists of:

1 - 20: contains the columns that come from the csv file 
21: id =auto generated and will reset back to number 1 when all 
data from 1 csv file has been successfully imported into the table. 
22: id-header = the same id as the id used in table-master
23. Create Date = processing date
24. Create By = default system
25. Change Date = processing date
26. Change By = system default

     

I want to ask how the logic to make the script? And where should I start?

Answer 1

Here is a very simple example. You will definitely need to modify this to suit your specific needs.

CREATE TABLE [dbo].[FINAL]
 (
        [Date]    DATETIME,
        Type    VARCHAR(MAX),
        Change  VARCHAR(MAX),
        SP_ID   VARCHAR(MAX),
        Sedol   VARCHAR(MAX),
        Cusip   VARCHAR(MAX)
)


CREATE TABLE [dbo].[STAGING]
 (
        [Date]    VARCHAR(MAX),
        Type    VARCHAR(MAX),
        Change  VARCHAR(MAX),
        SP_ID   VARCHAR(MAX),
        Sedol   VARCHAR(MAX),
        Cusip   VARCHAR(MAX)
)
  bulk insert dbo.STAGING
  from 'C:\Documents and Settings\Desktop\YOUR_DATA.txt'
  WITH
  (
  FIRSTROW = 2,
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n'
  )
  GO
  INSERT INTO dbo.FINAL
    SELECT
        [Date] = CASE WHEN ISDATE([Date])=1 THEN CAST([Date] AS DATETIME) ELSE NULL END,
        Type,
        Change,
        SP_ID,
        Sedol,
        Cusip
    FROM [dbo].[STAGING]