How to join a column of same values to another different table in SQL?-CodePudding

I have this table structure for names_table:

Name	Age	Gender
Someone1	25	Male
Someone2	25	Female

Another table names_with_company has this structure:

CompanyID	Name	Age	Gender

Now, I want to copy the data from names_table by adding a single value to column of CompanyID.

Expected result:

CompanyID	Name	Age	Gender
1234	Someone1	25	Male
1234	Someone2	25	Female

I am quite confused what should I include.

INSERT INTO names_with_company 
'1234',SELECT * FROM names_table

INSERT INTO names_with_company 
SELECT * FROM (
'1234'
UNION
SELECT * FROM names_table
)

These two doesn't work

I know these two tables are two different structures, but is there any way to have a static value in column and rest of the data from another table?

Also, can you please not suggest creating another table and joining them? I prefer it to be done using the above code lines, but with a working logic.

CodePudding user response：

Get into the habbit of always specifying the column names:

INSERT INTO names_with_company (CompanyID, Name, Age, Gender)
SELECT 1234, Name, Age, Gender
FROM names_table;

As you can see, you can provide "literal" values for any column.

CodePudding user response：

you can not able to insert because your 1st query

INSERT INTO names_with_company 
'1234',SELECT * FROM names_table

is completely wrong but if you written like below

INSERT INTO names_with_company 
SELECT '1234',name,age,gender FROM names_table

it will work but it is always better to mention the column name explicitly which is given in another answer by @stu

your 2nd query also wrong cause for union operation you have to provider same number of columns for all the selection

INSERT INTO names_with_company 
SELECT * FROM (
'1234'
UNION
SELECT * FROM names_table
)

but you have used only one select

write method for union operation is like below

select col1,col2 from table1
union

select col1,col2 from table2

then you can use it any other way