Is there a way to populate column based on conditions stored as rows in a table

Question

I am working on a project that has a C# front end that will be used to select a file for importing into an MSSQL SQL Database. In the table there will be an additional column called 'recommendedAction' (tinyint - 0-5 only)

I would like to have sql fill in the 'recommendedAction' column based on criteria in a different table.

Is there a way that when SQL is importing (SSIS or pure TSQL) it could read the values of a table and fill in the 'action' based on the criteria? Or is this something that should be done in the C# frontend?

EDIT

SQL table structure for imported data (with additional column)

Create Table ImportedData (
Column1 INT Identity,
Column2 VARCHAR(10) NOT NULL,
Column3 CHAR(6) NOT NULL,
RecommendedAction TINYINT NOT NULL
)

Table structure of recommended action criteria

Create Table RecommendedActions(
ID INT Identity,
ActionID TINYINT NOT NULL, --value to put in the RecommendedAction column if criteria is a match
CriteriaColumn VARCHAR(255) NOT NULL --Criteria to match against the records
)

Example records for RecommendedActions

ID    ActionID    CriteriaColumn
1     2           'Column2 LIKE ''6%'''
2     3           'Column2 LIKE ''4%'''

Now when a new set of data is imported, if Column2 has a value of '6032' it would fill in a RecommendedAction of 2

Please provide sample data and desired results. Explain the logic for assigning values from the other table as well. — Gordon Linoff
– Gordon Linoff, Commented May 3, 2021 at 17:30
Does this answer your question? How to Update Table with Using Inner Join and Case Statement — T.S.
– T.S., Commented May 3, 2021 at 17:36
How varied are your criteria, can they all be defined by column operator value? — Charlieface
– Charlieface, Commented May 3, 2021 at 20:59

Serg · Accepted Answer · 2021-05-03 18:08:26Z

0

Many ways exist. For example you can insert into the tb table a value selected from the ta table according to criteria.

Example setup

create table ta(
 Id int,   
 val int);

insert into ta(ID, val) values
   (1, 30)
  ,(2, 29)
  ,(3, 28)
  ,(4, 27)
  ,(5, 26);
  
create table tb
(Id int,   
ref int);

Example insert

-- parameters
declare @p1 int = 1, 
        @p2 int = 27;

-- parameterized INSERT
insert tb(Id, ref)
   values(@p1, (select ta.id from ta where ta.val=@p2));

edited May 3, 2021 at 18:08

answered May 3, 2021 at 17:53

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gudwlk · Accepted Answer · 2021-05-06 13:52:18Z

Below added Stored procedure will do the job. It gets the Action column value based on the Column2 parameter and insert into the ImportedData table. You can execute this Stored procedure inside the C# code with required parameters. I added sample execute statements for to test the query.

Sample data inserted to the RecommendedActions Table:

INSERT INTO  RecommendedActions  
VALUES 
 (2, 'Column2 LIKE ''6%''')
 ,(3, 'Column2 LIKE ''4%''')

Stored Procedure Implementation : CREATE PROCEDURE Insert_ImportedData(

  @Column2  AS VARCHAR(10)
 ,@Column3 AS CHAR(3)
 )
 AS 
  BEGIN

    DECLARE @RecommendedAction AS TINYINT   

     SELECT @RecommendedAction = ActionID 
     FROM   RecommendedActions 
     WHERE SUBSTRING(CriteriaColumn, 15, 1) = LEFT(@Column2 , 1)    

     INSERT INTO ImportedData VALUES (@Column2,@Column3,@RecommendedAction)


   END
GO

This is the execute statement for the Above Stored procedure

  EXEC  Insert_ImportedData '43258' , 'ATT'
  EXEC  Insert_ImportedData '63258' , 'AOT'

ListenSoftware Louise Ai Agent · Accepted Answer · 2021-05-06 15:47:13Z

0

you can use sqlalchemy in python and load your data into a dataframe then append the dataframe to the sql table. You can set the dtype for each of the field datatype in the read_csv using a dictionary. Loading data with Python is super powerful because the bulk load is fast. Use your c# code to build the csv file using stream io and use linq to for your conditions for data fields. Then use python to load your csv.

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine(connectionstring)

df = pd.read_csv("your_data.csv", header=None)
df.columns = ['field1', 'field2', 'field3']
df.to_sql(name="my_sql_table", con=connection,  if_exists='append', index=False)

answered May 6, 2021 at 15:47

ListenSoftware Louise Ai Agent

4,3432 gold badges31 silver badges39 bronze badges

Collectives™ on Stack Overflow

Is there a way to populate column based on conditions stored as rows in a table

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related