Need to find first empty field, and update

Question

Hello,

I am working with a database schema that I cannot change - and I need to run a bulk update. I am moving data between source and target databases, with the same schema. I know this is awful, but it is what I am working with right now.

The schema is as follows:

Car
CarType1
CarType2
CarType3
CarType4

What I am trying to do - is bring over the CarTypes from my source database, by updating the target CarTypes.

My question is:

If my source record for car "BMW" has 3 CarTypes specified like so:

Car: BMW
CarType1: Fast
CarType2: Well Made
CarType3: Good Handling
CarType4: NULL

and my target has:

Car: BMW
CarType1: Fun Car
CarType2: NULL
CarType3: NULL
CarType4: NULL

How would I write an update statement such that CarType1-2-3 from the source would fill in the available NULL fields on the target? So Source:CarType1 -> Target:CarType2. And also - would there be a way to cancel out of the bulk update for this record and log a message if there wasn't room in the target for all of the source fields(say if there's 4 CarType values in my source, and only 3 NULL CarType columns in the corresponding target row?

Thank you!

Answer 1

I don't know about doing it in one statement but four UPDATE statements do the trick.
The final select shows you what columns have overflowed.

DECLARE @CarsSource TABLE (Car VARCHAR(32), CarType1 VARCHAR(32), CarType2 VARCHAR(32), CarType3 VARCHAR(32), CarType4 VARCHAR(32))
DECLARE @CarsDestination TABLE (Car VARCHAR(32), CarType1 VARCHAR(32), CarType2 VARCHAR(32), CarType3 VARCHAR(32), CarType4 VARCHAR(32))

INSERT INTO @CarsSource VALUES ('BMW', 'Fast', 'Well Made', 'Good Handling', NULL)
INSERT INTO @CarsSource VALUES ('Overflow', 'Fast', 'Well Made', 'Good Handling', 'Overflow')

INSERT INTO @CarsDestination VALUES ('BMW', 'Fun Car', NULL, NULL, NULL)
INSERT INTO @CarsDestination VALUES ('Overflow', 'Fun Car', NULL, NULL, NULL)

UPDATE  @CarsDestination
SET     CarType1 = s.CarType1
        , CarType2 = s.CarType2
        , CarType3 = s.CarType3
        , CarType4 = s.CarType4
FROM    @CarsDestination d
        INNER JOIN @CarsSource s ON s.Car = d.Car
WHERE   d.Cartype1 IS NULL        

UPDATE  @CarsDestination
SET     CarType2 = s.CarType1
        , CarType3 = s.CarType2
        , CarType4 = s.CarType3
FROM    @CarsDestination d
        INNER JOIN @CarsSource s ON s.Car = d.Car
WHERE   d.Cartype2 IS NULL        

UPDATE  @CarsDestination
SET     CarType3 = s.CarType1
        , CarType4 = s.CarType2
FROM    @CarsDestination d
        INNER JOIN @CarsSource s ON s.Car = d.Car
WHERE   d.Cartype3 IS NULL        

UPDATE  @CarsDestination
SET     CarType4 = s.CarType1        
FROM    @CarsDestination d
        INNER JOIN @CarsSource s ON s.Car = d.Car
WHERE   d.Cartype4 IS NULL        

SELECT  *
FROM    @CarsSource s
        LEFT OUTER JOIN @CarsDestination d ON COALESCE(d.Cartype4, d.Cartype3, d.Cartype2, d.Cartype1) = COALESCE(s.Cartype4, s.Cartype3, s.Cartype2, s.Cartype1)
WHERE   d.Car IS NULL