:::: MENU ::::

Select and Insert rows in same table with some different values

  • Feb 23 / 2012
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Select and Insert rows in same table with some different values

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.

Consult us to explore the Databases. Contact us