How to get data from two diffenerent tables ?

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008R2

XML

<b>I have a situation ,there are two tables

table1 :
            col1 col2 col3
            a    b    c
table2 :
            id ,col
            1  a
            2  c
            3  b and so on...


table 1 col1,col2,col3 data will be inserted into table 2 and generate id by identity.
Now I want ids of col1,col2 and col3  like
  1,2,3 (a,b,c..id values) in a single select query.
Because already my job is taking more time for remaining scripts I dont want to burden more
Can any one help me out .


</b>

Posted 3-Apr-14 0:53am

pratap420

Add a Solution

Comments

$*Developer - Vaibhav*$ 3-Apr-14 7:18am

table1 :
col1 col2 col3
a b c
table2 :
id ,col
1 a
2 c
3 b
4 e
5 k

In above case your table1 column increase?

pratap420 3-Apr-14 8:35am

No for sample I gave like that but actually my table1 has fixed 8 cols like this,data will be different for each row and table2 data will be increased based on table1 col data .Table2 always have distinct rows .

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)

Maciej Los · Answer 1 · 2014-04-03T10:50:00

Try this:

SQL

DECLARE @src TABLE(col1 VARCHAR(30), col2 VARCHAR(30), col3 VARCHAR(30))

INSERT INTO @src (col1, col2, col3)
VALUES('a', 'b', 'c')

DECLARE @dst TABLE(id INT IDENTITY(1,1), col VARCHAR(30))

INSERT INTO @dst (col)
SELECT colValue AS col
FROM (
    SELECT colValue, colDescription
    FROM (
        SELECT col1, col2, col3
        FROM @src
    ) AS pvt
    UNPIVOT(colValue FOR colDescription IN ([col1], [col2], [col3])) AS unpvt
) AS src

SELECT *
FROM @dst

Result:

1	a
2	b
3	c

The magic does UNPIVOT[^] statement ;)

[EDIT]
Below solution is based on OP's comments.

As per i understand what OP wants to do, He/She want to insert into table3 id's instead values stored in col1, col2, col3 which are equal to sValue.

SQL

DECLARE @table1 TABLE (col1 VARCHAR(30), col2 VARCHAR(30), col3 VARCHAR(30))

INSERT INTO @table1 (col1, col2, col3)
VALUES('file_abc ', 'Dfile_xyz', 'Mfile_mnp'),
('file_afc', 'Dfile_xyz', 'Mfile_mnp'),
('file_afc', 'Dfile_acd', 'Mfile_oop')

DECLARE @table2 TABLE(id INT IDENTITY(1,1), sValue VARCHAR(30))

INSERT INTO @table2 (sValue)
VALUES ('file_afc'), ('Dfile_acd'),
('Mfile_mnp'), ('file_abc'),
('Dfile_xyz'), ('Mfile_oop')


DECLARE @table3 TABLE(tab3id INT IDENTITY(1,1), col1 INT, col2 INT, col3 INT)

--SELECT *
--FROM @table1

--SELECT *
--FROM @table2


INSERT INTO @table3 (col1, col2, col3)
SELECT (SELECT id FROM @table2 WHERE sValue = t1.col1) AS col1,
        (SELECT id FROM @table2 WHERE sValue = t1.col2) AS col2,
        (SELECT id FROM @table2 WHERE sValue = t1.col3) AS col3
FROM @table1 AS t1


SELECT *
FROM @table3
ORDER BY tab3id

Note: There are few ways to achieve that. I showed you a way with subqueries ;)
[/EDIT]

pratap420 · Answer 2 · 2014-04-03T20:40:00

Solution 2

SQL

 insert into table3(col1,col2,col3)
(Select c1.id,c2.id,c3.id
from table1 t1
left join table2 c1 on c1.col=t1.col1
left join table2 c2 on c2.col=t1.col2
left join table2 c3 on c3.col=t1.col3
)

I tried with this .plz suggest me is it fulfill my requirement?

Posted 3-Apr-14 20:40pm

pratap420

Updated 3-Apr-14 23:42pm

v3

Comments

Maciej Los 4-Apr-14 5:54am

Not an answer!