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?

SQL is a language used to work with data from an RDBMS. You can't import anything into a query language. What specific RDBMS are you using? That would be where you're attempting to import the data, and knowing which one you're using would be necessary in order to be able to answer. You've not added a tag for that RDBMS. It's the first thing you should add after the SQL tag. Please edit and add that tag. Also, SO is not a free code writing service. What have you done to try and solve the problem yourself? — Ken White
– Ken White, Commented Oct 17, 2022 at 3:13
Firstly you also need a "staging" table. Because CSV files are not free from error. The normal approach is you insert into a staging table first, then you call a stored procedure that does a bunch of error checking and only then do you write it the the final table (and the header table). I assume at this time you are just using BCP. It won't be able to write the filename in. I suggest you use Powershell to call BCP (or maybe use bulk insert) and also use another step to write the filename into the staging table. — Nick.Mc
– Nick.Mc, Commented Oct 17, 2022 at 5:38
You also need to consider what happens when multiple files are in the source folder, and also what happens if a file fails to be imported (i.e. if colums are wrong or data is truncated) — Nick.Mc
– Nick.Mc, Commented Oct 17, 2022 at 5:38
There are a couple of suggestion here (forget about LogParse though) devblogs.microsoft.com/scripting/… — Nick.Mc
– Nick.Mc, Commented Oct 17, 2022 at 5:40

ASH · Accepted Answer · 2022-10-20 16:36:25Z

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]

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related