Try this:
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
.
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)
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]