adding value form two table

Question

0.00/5 (No votes)

See more:

compatable model Table

model1  model2  model3  model4  model5  model6  model7
10	8900	8520	9300	9500	9360	9630
11	8100	8300		NULL	NULL	NULL

spare Table

XML

spareID  SpareCode  
1        A   
2        B

another table SpareModel

XML

SpareID  model
1         10 
1       8900    
1       8520    
1       9300    
1       9500

i want to add in SpareModel from model and spare table
row 1 (spareID) compatable modle all value in row in model

Posted 12-Oct-12 23:41pm

DINESH K MAURYA

Add a Solution

Comments

Aarti Meswania 13-Oct-12 6:00am

provide output table for clarify requirement.

DINESH K MAURYA 13-Oct-12 6:09am

table 3 is out put table

damodara naidu betha 15-Oct-12 1:57am

There is no relation ship between first two tables. Please provide the correct info.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

surendersinghprajapati · Answer 1 · 2012-10-12T23:57:00

Solution 1

you can use insert queries with select statements to select values from multiple tables.

something like this -

insert into table1 select val1,val2... from table2 inner join table3 on table3.t1 = table2.t1

I think this would be helpful.

Posted 12-Oct-12 23:57pm

surendersinghprajapati

damodara naidu betha · Answer 2 · 2012-10-14T20:27:00

Hi,

I think following code block can help you..

SQL

WITH MODCTE AS (
  SELECT Model1,Model2,Model3,Model4,Model5,Model6,Model7,
  Row_Number() OVER( ORDER BY Model1) ROWNUM FROM compatableModel
), ResultCTE AS (
 SELECT SpareId,SpareCode,Model1,Model2,Model3,Model4,Model5,Model6,Model7 
 FROM  (SELECT SpareId,SpareCode,Row_NUMBER() OVER(ORDER BY SpareId) ROWNUM FROM Spare 
       ) A 
 JOIN MODCTE ON MODCTE.ROWNUM = A.ROWNUM
)

SELECT * INTO SpareModel FROM (
 SELECT SpareId,Model,ModelNo FROM ResultCTE
 UNPIVOT
 (
 ModelNo FOR Model IN
   ( Model1,Model2,Model3,Model4,Model5,Model6,Model7) ) As UP
) As A

SELECT * FROM SpareModel