Home » Populate a Normalized Table with Data from an Unnormalized Table

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.

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.