How do insert data into a table that already exists?-CodePudding

I'm trying to insert data into a table that already exists, but I cant find anything on how to do this. I only found how to insert this data into a new table.

Syntax error at or near Insert

Tutorial I visited

 SELECT film_category.film_id, film_category.category_id, rental_duration, rental_rate
 INSERT INTO category_description
 FROM film_category
 LEFT JOIN FILM
 ON film_category.film_id = film.film_id

CodePudding user response：

The order is wrong https://www.w3schools.com/sql/sql_insert_into_select.asp

Also see this answer Insert into ... values ( SELECT ... FROM ... )

 INSERT INTO category_description
 SELECT 
   film_category.film_id,
   film_category.category_id,
   rental_duration,
   rental_rate
 FROM 
   film_category
   LEFT JOIN FILM ON film_category.film_id = film.film_id

CodePudding user response：

A simplified test to showcase methods to insert.

CREATE TABLE TableA (
ID INT GENERATED ALWAYS AS IDENTITY,
ColA1 INT, 
ColA2 VARCHAR(30)
)

✓

--
-- INSERT VALUES into existing table
--
INSERT INTO TableA (ColA1, ColA2) VALUES
(10, 'A'),
(20, 'B'), 
(30, 'B');

3 rows affected

--
-- SELECT INTO new table
--
SELECT ID, ColA1 2 AS ColB1, ColA2||'2' AS ColB2 
INTO TableB
FROM TableA;

3 rows affected

--
-- INSERT from SELECT with explicit columns
--
INSERT INTO TableA (ColA1, ColA2) 
SELECT ColB1, ColB2 
FROM TableB;

3 rows affected

SELECT * FROM TableA;

id | cola1 | cola2
-: | ----: | :----
 1 |    10 | A    
 2 |    20 | B    
 3 |    30 | B    
 4 |    12 | A2   
 5 |    22 | B2   
 6 |    32 | B2

--
-- INSERT from SELECT without columns
-- Only works when they have the same number of columns. 
--
INSERT INTO TableB
SELECT *
FROM TableA;

6 rows affected

SELECT * FROM TableB;

id | colb1 | colb2
-: | ----: | :----
 1 |    12 | A2   
 2 |    22 | B2   
 3 |    32 | B2   
 1 |    10 | A    
 2 |    20 | B    
 3 |    30 | B    
 4 |    12 | A2   
 5 |    22 | B2   
 6 |    32 | B2

db<>fiddle here