Populate a Normalized Table with Data from an Unnormalized Table

Say you wanted to transfer data from an unnormalized source table in Excel (a worksheet) to SQL Server.

Take a look at the columns in the source table, TABLE_A, with repeating groups of data as columns. Assume that columns Mark1, Mark2 and Mark3 stand for Subjects ‘S1’, ‘S2’ and ‘S3’.

TABLE_A: Student_ID, StudentName, Mark1, Mark2, Mark3

You have to transfer this data to normalized table TABLE_B with following columns:

TABLE_B: Student_ID, StudentName, Mark_ID, Marks

Use the following SQL in SQL Server:

Insert into TABLE_B (Student_ID, StudentName,  Mark_ID, Marks)Select Student_ID, StudentName, 'S1',  Mark1From TABLE_AUnion AllSelect Student_ID, StudentName, 'S2',  Mark2From TABLE_AUnion AllSelect Student_ID, StudentName, 'S3',  Mark3From TABLE_AOrder by 1,2,3,4

You will have a Union of as many SELECT as the number of the repeating groups of data (the repeating columns) in TABLE_A.

This SQL can be used in any database.

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.