T-SQL Insert Column Name as First Row

Question

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 |
+-----+------+---------------+---------------+

In effect: [How do you return the column names of a table?](https://stackoverflow.com/questions/600446/how-do-you-return-the-column-names-of-a-table) — Rangad, Jun 09 '19 at 09:38
I'm a bit confused. You say you want to insert "the name of each column in the first table", but your example seems to be inserting a complete row from the second table. I don't follow what you are really doing. Before and after data would help. — Gordon Linoff, Jun 09 '19 at 12:14
Sorry @GordonLinoff I was not very clear. I apologize. I am new to Stackoverflow and trying to learn how to be more efficient. I posted an answer to my question. — stackoverflow, Jun 09 '19 at 18:54

score 2 · Answer 1 · answered Jun 09 '19 at 08:21

2

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;

answered Jun 09 '19 at 08:21

Tim Biegeleisen

502,043
27
286
360

Thank you for your help. I posted an answer to my question. – stackoverflow Jun 09 '19 at 18:54

Ilyes · Answer 2 · 2019-06-09T09:42:09.197

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

Thank you for your help. I posted an answer to my question. – stackoverflow Jun 09 '19 at 18:54 — stackoverflow, Jun 09 '19 at 18:54

score 0 · Accepted Answer · answered Jun 09 '19 at 18:53

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

T-SQL Insert Column Name as First Row

3 Answers3