Menu
In SQL, Select into ... copies rows into a different (backup) table. Is this possible if the backup table has different structure (or different column names)? If not, what is the best way to achieve this?
Here is what I want to do: TableA has columns a1,a2,a3. I want to copy some rows from this table to another table TableB which has column b1,b2,b3,b4. Content of a1 to go into b1, a2 to b2 etc.
785
To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.
Note the following when using UNION in SQL: All SELECT statements should list the same number of columns. The corresponding columns must have the same data type. The corresponding columns can have different names, as they do in our example.
Use the wildcard (*) after the SELECT statement with the where condition which you can apply to copy required data only. In addition to above syntax, you can also copy only the selected column from another table. Specify the column name you want to copy. Also, give the condition to get only the required data from another table.
You can create new column names using the AS clause. The following SQL statement uses the IN clause to copy the table into a new table in another database: Tip: SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:
We can verify the data in the table using the SELECT query as below. We will be using sys. columns to get the column names in a table. It is a system table and used for maintaining column information. It contains the following information about columns: Name – Name of the column.
Learn how to use SQL SELECT INTO statement with the explained examples of this tutorial. Copy all data to the new table using SQL SELECT INTO statement. You can copy all the columns and data from another table or you can copy only the selected columns from the other table.
The column names do not matter at all, as long as data types match (or can be cast in the assignment).
If the data types of the columns don't match, try casting the values accordingly. Just try with small dummy tables. Be sure to list the target columns explicitly to avoid confusion. Like this:
INSERT INTO TableB (b1, b2, b3) SELECT a1, a2, a3 FROM TableA WHERE <some condition>; More details in the SQLite manual here.
182
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
