CODEWITHSUNDEEP
sqlpostgresql
Stack Ty
Stack Ty

Reputation: 51

How do insert data into a table that already exists?

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

Upvotes: 1

Views: 3274

Answers (2)

LukStorms
LukStorms

Reputation: 29647

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, 'C');

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+1, CONCAT(LEFT(ColB2,1),'3') AS ColB23
FROM TableB;

3 rows affected

SELECT * FROM TableA;

id | cola1 | cola2
-: | ----: | :----
 1 |    10 | A    
 2 |    20 | B    
 3 |    30 | C    
 4 |    13 | A3   
 5 |    23 | B3   
 6 |    33 | C3   

--
-- 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 | C2   
 1 |    10 | A    
 2 |    20 | B    
 3 |    30 | C    
 4 |    13 | A3   
 5 |    23 | B3   
 6 |    33 | C3

db<>fiddle here

Upvotes: 1

Johnny Saldana
Johnny Saldana

Reputation: 29

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

Upvotes: 0

Related Questions