SQL Server SSIS fill in Null Value with Non-Null Value

brqmpdu1 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(106)

I'm currently able to get this type of output from a flat file after running a script component in SSIS:

My goal is to fill in the rest of the column with the same serial number like this:

I tried to do this by setting it a variable and using a derived column, but the variable isn't set til after the data flow. Also the script only reads line by line so I don't see an obvious way to do this simple task.

Is there a way to create a column in the script and give it a default value of the serial number? Or is there a way for use a SQL command to update the Null values Where the column is Not Null?

sql-server

来源：https://stackoverflow.com/questions/48666552/ssis-fill-in-null-value-with-non-null-value

2条答案

按热度按时间

g2ieeal71#

You can try this design in order to perform what you need (feel free to simplify it, tested with your input data) :

Read your source
Multicast
Aggregate on the column you want the max value (serial number)
create a derived column with a constant value ( 1 for example)
sort the set on the constant value
Merge your two set with a full join using the constant key
import data into the destination table and use the new aggregated column to fill the Serial number column

赞(0）回复(0）举报 2023-10-15

2vuwiymt2#

Update statement after the data is loaded to a table:

update table 
set column = (select top 1 column from table where column is not null) 
where column is null

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server SSIS fill in Null Value with Non-Null Value

2条答案

相关问题

热门标签

最新问答