One thing is to take notice that the datatypes of columns of both tables must be the same. The output will look like this:Įxample 3: Incompatible Column Type Error The output table would return all the data or students having studentsId >2, copied for the Students_info table. SELECT StudentId,StudentName FROM students_info Now if we want to copy only data of those students who have studentId >2 (maybe to place them in another section), we will make use of the WHERE clause as follows: INSERT INTO attendence_list(StudentId,StudentName) Below attached is the screenshot of the query:Įxample 2: INSERT INTO SELECT Statement With WHERE Clause The output will show that the data from table students_info is copied to attendence_list for the two columns i.e. The data of rows that are specified is being copied and inserted: INSERT INTO attendence_list(StudentId,StudentName) the “studentId column” and the “studentName column” from the students_info table to the attendence_list table. Now we will use the INSERT INTO SELECT statement to select data i.e. Step 3: Insert Data From Students_info to Attendence_list studentid and studentname: CREATE TABLE attendence_list The attendance list will only contain two columns i.e. Now create another table named “attendance_list”. The data is successfully inserted in the table like this: Now insert data in the newly created “Students_Info” table: INSERT INTO Students_info(studentid, studentname ,address,city) Step 1: Create Table 1 and Insert Some Data Into itĬreate a table named students_info with columns StudentId, StudentName, Address, and City. Let’s discuss the concept with the help of an example so that it becomes more clear: Table_1 is the source table from where the data is being copied.Įxample 1: Understanding INSERT INTO SELECT.Table_2 is the table where the data is being copied means the target table.If we want to select/copy some columns (partial data) from a table and insert them into another, the above syntax will become: INSERT INTO table_2 (col1, col2, col3. We can also use the optional WHERE clause followed by the condition to copy/insert the data based on a specified condition. SELECT statement is followed by the name of the table from which we want to copy/select data. The name of the table in which we want to insert/paste the data is written after the INSERT INTO command. We can use the following syntax of the INSERT INTO SELECT statement to select and insert the data from one table to another: INSERT INTO tab_2 Second, the datatype of columns of both the source and target tables should be the same.First, the table/target table where the columns are being copied remains unchanged.Now there are two important things that are to be noticed: The INSERT INTO SELECT command selects some specified data from one table and inserts/pastes it into another one. Let’s have a look at how this statement works in PostgreSQL. It performs two operations in one go, i.e., “fetch the data from one table” and insert the fetched data into the targeted table. One of the most crucial and frequently used statements is INSERT INTO SELECT. PostgreSQL offers many useful statements that are used to deal with the database tables effectively.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |