Untitled

-- Alter AccountStructure34 to add a primary key on Id if it doesn't already exist
ALTER TABLE AccountStructure34
ADD CONSTRAINT PK_AccountStructure34 PRIMARY KEY (Id);

-- Create AccountElements34 with AccountStructureId as a foreign key referencing AccountStructure34
CREATE TABLE AccountElements34 (
    Id INT PRIMARY KEY,
    AccountStructureId INT NOT NULL,
    Value NVARCHAR(MAX),
    Description NVARCHAR(MAX),
    FOREIGN KEY (AccountStructureId) REFERENCES AccountStructure34(Id)
);

-- Insert data into AccountElements34, applying the CASE condition to the Value column
INSERT INTO AccountElements34 (Id, AccountStructureId, Value, Description)
SELECT 
    Id,
    AccountStructureId,
    CASE 
        WHEN AccountStructureId = 1 THEN Value + 'X'
        WHEN AccountStructureId = 2 THEN Value + '0'
        WHEN AccountStructureId = 3 THEN '0' + Value
        WHEN AccountStructureId = 4 THEN '0' + Value + '0'
        WHEN AccountStructureId = 5 THEN Value + '00'
        ELSE Value  -- Preserve Value if AccountStructureId doesn't match any condition
    END AS Value,
    Description
FROM AccountElements
WHERE Value IS NOT NULL;