0

I am working with databases in which I have 3 tables one is skill table the other one is experience and the third one is Experience_skill table in this table I have foreign keys now the question is both foreign keys are primary keys as well. Let say I am storing data into skill table as well as in experience table how can I insert the the both keys data there in Experience_skill table. I have tried following queries.

insert into Experience_skill(eid, Skill_Id) select eid from Experience where eid=2 union select Skill_Id from Skills where Skill_Id=2

error I get:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns. Than i tried this one.

insert into Experience_skill(eid)select eid from Experience where eid=2

it gives me this error:

Cannot insert the value NULL into column 'Skill_Id', table 'resume.dbo.Experience_skill'; column does not allow nulls. INSERT fails. Please help me out here are the snapshots of the table first snap shot is of skill tableenter image description here

the second one is experience table enter image description here

And this one is EXperience_skill table where i have my foreign keysenter image description here

Improve this question
13
  • Please provide DDLs for your tables. Commented Dec 11, 2013 at 18:35
  • I don't think union is what you're looking for here. you need to specify as many columns in the select as in the insert Commented Dec 11, 2013 at 18:35
  • in the insert you're saying you'll provide eid and Skill_Id.. however your select is only providing one of those. Commented Dec 11, 2013 at 18:36
  • i dont know i am messing up with this kind of problem first time Commented Dec 11, 2013 at 18:37
  • Also, you're trying to insert eid and skill_id but you're providing those both in the where clause.... in other words.. you already have what you're looking for. Commented Dec 11, 2013 at 18:38

1 Answer 1

Reset to default
3

When using INSERT INTO, you need to supply ALL of the columns in the destination table (that do not allow NULLs) in each ROW of the SELECT.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

you are saying this,insert into Experience_skill(eid, Skill_Id)select eid from Experience where eid=2 UNION select Skill_Id from Skills where Skill_Id=2
i think this will work if i create the view of both tables and than i apply the above query
no, that will not work. In your example, each row will only have 1 column in it, either eid or Skill_id. You need to have eid AND Skill_id in the same row.
oh i see so it is not possible to have the single row in select statement . so there is no way to that any idea i am really needing this
the reason why we all want to see your table structure is to determine if there is some relationship between the 2 tables. If there is, then they can be JOINed in a single query to produce what you need.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.