CODEWITHSUNDEEP
sqlpostgresql
user7609786
user7609786

Reputation: 39

Copy rows of data from one table to another table in same database using pgadmin

I'm quite new to this and I need some help.

I would like to copy rows of data from one table to another table in the same database using pgadmin. However, they have slightly different column id. Is there a way to write a script to match them and do a copy?

Please help. Thank you.

I have tried the following( to copy existing data in STUD table into STUDENT table):

CREATE TABLE STUDENT(
Student_id  INT,
Student_name TEXT,
Student_address TEXT
);

INSERT INTO STUDENT
SELECT * FROM STUD AS D
WHERE(
Student_id = D.id,
Student_name = D.name
);

I have 2 tables STUDENT and STUD. Under STUDENT table, I have Student_id, Student_name and Student_address. Under STUD table, I have name and id. The rows in the both the tables are not in order.

** ADD ON**
The table in STUD does not come with the Student_address column. Only the STUDENT table has. Since STUD does not have Student_address, I would like to put Student_address in STUDENT table as NULL. Is it possible to write a general script where columns in STUDENT table that does not exist in Stud will be NULL?

EDIT** Instead of NULL, I am required to insert "TBC"

Upvotes: 1

Views: 3400

Answers (1)

user330315
user330315

Reputation:

Use an insert based on a select:

insert into student (Student_id, Student_name)
select id, name
from stud;

The column names don't have to match, only the data types. Any column you do not specify in the insert's target list will be set to null (or more specifically to the default value defined for that column)

Online example: http://rextester.com/ISCL45721

Edit (after the requirements have changed)

To insert a constant value into one column for all rows, include that constant in the select clause:

insert into student (Student_id, Student_name, student_address)
select id, name, 'TBC'
from stud;

Upvotes: 1

Related Questions