A requirement may arise when we have to select specific rows and insert these in same table with some values different. There may be several scenarios for it. Following example would cover the requirement and process involved to complete the task.
We have a table with information about language skills of employees. We are required to select records of an employee and insert these for different employee. All other field values are same except the employee number that we would provide explicitly in script. Consider following script
-- Craete table for demo CREATE TABLE EmpLanguages (ID SMALLINT IDENTITY(1,1), CNIC CHAR(13), Lang VARCHAR(50), OralSkill TINYINT, WrittenSkill TINYINT) GO -- Populate table with sample data INSERT INTO EmpLanguages SELECT '1111111111111','Urdu', 4, 4 UNION ALL SELECT '1111111111111','Arabic', 3, 2 UNION ALL SELECT '1111111111111','English', 3, 3 UNION ALL SELECT '1111111111111','Pashto', 3, 2 GO -- verify the data SELECT * FROM EmpLanguages GO
We have values for employee with CNIC 1111111111111, that we have to insert for employee with CNIC 2222222222222. First of all get columns list instead of manually writing these in query
-- Get columns SP_HELP EmpLanguages GO
Now copy the columns and construct following script.
-- Insert same records for different employees INSERT INTO EmpLanguages SELECT '2222222222222' ,Lang, OralSkill, WrittenSkill FROM EmpLanguages WHERE CNIC = '1111111111111' GO -- Again verify the data SELECT * FROM EmpLanguages GO
Verification of data shows that task is complete. This method may be applied for various other scenarios with little modification.