CODEWITHSUNDEEP
sqlsql-servert-sql
stackoverflow
stackoverflow

Reputation: 43

T-SQL Insert Column Name as First Row

I have the following table (Table_1):

Seq  |  Name  |  Column1  |  Column2

There is another table (Table_2) with the following structure:

Name  |  Column1Table2  |  Column2Table2

I need to insert the Name of each column to the first table. So, the result should be (Seq would be Row Number starting at 1):

+-----+------+---------------+---------------+
| Seq | Name |    Column1    |    Column2    |
+-----+------+---------------+---------------+
|   1 | Name | Column1Table2 | Column2Table2 |
+-----+------+---------------+---------------+

Upvotes: 0

Views: 952

Answers (3)

stackoverflow
stackoverflow

Reputation: 43

Thank you Everyone. I ended up using the Stuff function to concatenate the column names coming from the schema table and then Pivot the values into columns and insert them into the Temp table. And finally insert the Temp table values into the main table. This post was very helpful.

Efficiently convert rows to columns in sql server

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

Seq would be Row Number starting at 1

Is that what are you trying to do?

;WITH CTE AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) RN,
         Name, 
         Column1Table2, 
         Column2Table2
  FROM Table2
)
INSERT INTO Table1(Seq, Name, Column1, Column2)
SELECT RN,
       Name,
       Column1Table2,
       Column2Table2
FROM CTE;

Demo:

CREATE TABLE Table1(
  Seq INT,
  Name VARCHAR(45),
  Column1 VARCHAR(45),
  Column2 VARCHAR(45)
);

CREATE TABLE Table2(
  Name VARCHAR(45),
  Column1Table2 VARCHAR(45),
  Column2Table2 VARCHAR(45)
);

INSERT INTO Table2 VALUES
('Name1', 'Col1Value1', 'Col2Value1'),
('Name2', 'Col1Value2', 'Col2Value2');

;WITH CTE AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) RN,
         Name, 
         Column1Table2, 
         Column2Table2
 FROM Table2
)
INSERT INTO Table1(Seq, Name, Column1, Column2)
SELECT RN,
       Name,
       Column1Table2,
       Column2Table2
FROM CTE;

SELECT *
FROM Table1;

Returns:

+-----+-------+------------+------------+
| Seq | Name  |  Column1   |  Column2   |
+-----+-------+------------+------------+
|   1 | Name1 | Col1Value1 | Col2Value1 |
|   2 | Name2 | Col1Value2 | Col2Value2 |
+-----+-------+------------+------------+

Live Demo

Update:

I need to insert the Name of each column to the first table

I don't think you are trying to insert the column names of a table in another table, but if so then why you want to do that? what are you trying to do really? and why not just 

INSERT INTO Table1 (Seq, Name, Column1, Column2) VALUES
(1, 'Name', 'Table2Column1', 'Table2Column2');
--You can remove <Name> column and <1> value if it's an identity column

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521864

Assuming that Seq in the first table is an auto increment column, you should be able to just omit it during the insert, e.g.

INSERT INTO Table_1 (Name, Column1, Column2)
SELECT Name, Column1Table2, Column2Table2
FROM Table_2;

Upvotes: 2

Related Questions