Insert leading spaces, then the value to a field in varchar(10)

I’m using the database (SQL Server) of an old program, and want to make batch inserts on the SUPPLIERS table which key is varchar(10). The thing is that the old program fills the key with leading blankspaces to complete the 10 characters, example (underscore represents blankspace)
=’_______310′, ‘_______311’…, ‘______1000’.

I’ve tried to convert the value to char(10) but it adds the blankspaces at the trail. I’ve also tried to use space function, but the problem is that for key 310, I’ve to add 7 blankspaces, but for the key 1000, 6, so making this function dynamic on a simple select or insert statement would be very messy.

Without those spaces this app will not work properly, so do you have any idea of how to add this spaces on a simple select or insert statement?

Thanks

>Solution :

You can use the RIGHT function along with the SPACE function to dynamically calculate and insert the appropriate number of leading spaces. Here is a way to do this in your SQL statement:

INSERT INTO SUPPLIERS (key_column, other_columns)
VALUES (RIGHT(SPACE(10) + '310', 10), other_values);

For batch inserts:

INSERT INTO SUPPLIERS (key_column, other_columns)
SELECT RIGHT(SPACE(10) + CAST(key AS VARCHAR(10)), 10), other_columns
FROM (
    SELECT '310' AS key, other_value1 AS other_columns
    UNION ALL
    SELECT '311', other_value2
    UNION ALL
    SELECT '1000', other_value3
) AS batch;

RIGHT(SPACE(10) + CAST(key AS VARCHAR(10)), 10) – concatenates the 10 spaces with the key and then takes the rightmost 10 characters, ensuring the key has leading spaces.