0

Hey guys i am quite a newbie here, what i am trying to do is i am trying to copy data (skills) column from one temp table to another table where id=1;

now Table one called skillmapping has candidateId and skillName columns

Table2 has only skillName column

I am trying to insert data from Table 2 to Table 1 where the canidateId=1

Please help me with this. i tried copy data but seems like error for candidateId.. thanks.

I tried this but says incorrect syntax 

 insert into candidateComputerSkillMapping(skillname) values(select * from tempComputerSkillMap)

Thanks for All the answers guys, i guess columns on both tables should be same i mean both should have an id and skillName then only we can copy right.. thanks anyways,

designerNProgrammer
  • 2,621
  • 5
  • 34
  • 46
  • `insert into Table2 select skillname from Table1 where candidateid = 1` – vikas May 30 '13 at 06:12
  • your insert query is wrong instead of `select * from tempComputerSkillMap` try to use `select skillname from tempComputerSkillMap` because you want ot insert only skillname value into table `candidateComputerSkillMapping`..hope you understand, `*` means `all columns`.. – Rahul May 30 '13 at 06:27
  • `insert into candidateComputerSkillMapping select skillName from tempComputerSkillMap where candidateid = 1` should work. Please post your create table script – vikas May 30 '13 at 06:30
  • that's does not matter that `both tables should be same i mean both should have an id and skillName then only we can copy right`..actually if your `id` is primary key then it will create problem beacuse you cannot assign `null` value to primary key while `inserting` otherwise that's doesn't matter. – Rahul May 30 '13 at 06:41

4 Answers4

1

Try to make a query like 

INSERT INTO candidateComputerSkillMapping (skillName)
SELECT skillName
FROM tempComputerSkillMap
WHERE candidateId=1;

Or

INSERT INTO candidateComputerSkillMapping
SELECT skillName
FROM tempComputerSkillMap
WHERE candidateId=1;

Check sqlauthority

If candidateId in candidateComputerSkillMapping table is primaryid then you cannot insert null to primary key and it will give error.That's why show These two tables structure for good answer.

Hope it works.

Rahul
  • 5,603
  • 6
  • 34
  • 57
  • 1
    dear you want to insert `table 2` data to `table 1` or `table 1` data to `table 2`.. – Rahul May 30 '13 at 06:15
  • i am trying to insert data from table2 to table1 and the problem is that table2 only contains skillname and i want to add that data to table 1 where we have a skillId=1 – designerNProgrammer May 30 '13 at 06:17
0
INSERT INTO [Table2] SELECT skillName FROM [skillmapping] WHERE candidateId = 1
zkanoca
  • 9,664
  • 9
  • 50
  • 94
  • It says an Error Column name or number of supplied values does not match table definition. I guess because i don't have candidateId on skillmapping table.. is there any other way. thanks – designerNProgrammer May 30 '13 at 06:12
  • As you said you have candidateId on your skillmapping table. Did I understand wrong? – zkanoca May 30 '13 at 06:16
0

if you think data already exist and you need to update it then...

update Table2 set skillname = (select skillname from Table1 where candidateid =1)

if you think you need to insert new record then....

insert into Table2 select skillname from Table1 where candidateid = 1
Pavan Kumar K
  • 1,360
  • 9
  • 11
0

Since your table2 doesn't have candidateid, I assume at the time of the insert, you are sure that all skills contained in table2 belong to candidateid = 1.

If the assumption is correct, then you can follow the example below to insert new skills from table2 into table1.

EDIT: Updated the queries with comments.

use tempdb

-- Pretending this is your table1
create table candidateComputerSkillMapping
(
    candidateid int             not null
    , skillname varchar(128)    not null
    , unique 
    (
        candidateid
        , skillname
    )
)

-- Table1 has one skill for candidateid = 1
insert candidateComputerSkillMapping values (1, 'Old Skill')

-- Prentending this is your table2
create table tempComputerSkillMap
(
    skillname varchar(128) not null
)

-- table2 has two skills. Now since this table doesn't have candidateid, how do I know the skills are for candidateid = 1?
-- I don't know and I am assuming that you are certain that these skills indeed belong to candidateid = 1.
insert tempComputerSkillMap values ('Old skill')
insert tempComputerSkillMap values ('New skill')

-- Insert only new skills from table2 into table1 for candidateid = 1
insert candidateComputerSkillMapping (candidateid, skillname)
select 1, t2.skillname
from tempComputerSkillMap t2
where not exists (select * from candidateComputerSkillMapping t1 where t1.candidateid = 1 and t1.skillname = t2.skillname)

select * from candidateComputerSkillMapping
Louie Bao
  • 1,632
  • 2
  • 16
  • 23