too long

Question

I'm having a problem. I have a table in which I want to move its data into another table. When I do : INSERT INTO rosters SELECT * FROM rosters_test
All of the rows enter into the database. The problem is that the columns are out of order for the new data entered (the new data has id where name should be etc), therefore making the data incorrect and useless.
I have tried to be more specific about the insert statement, but that is where the ERROR: UNIQUE constraint failed occurs. I have a dual key on the rosters table which is the id and the season. I am trying to insert the 20142015 roster from the rosters_test table into the rosters table where the 20152016 and 20162017 rosters reside.

Every row in the roster_test table is 20142015 for the season and I checked all 27 rows for repeat ids and there are none. These are the queries I tried:

INSERT INTO 'rosters'
SELECT 'number' AS 'number',  'name' AS 'name' , 'height' AS 'height', 'weight' AS 'weight', 'birthplace' AS 'birthplace', 'birthdate' AS 'birthdate', 'position' AS 'position', 'id' AS 'id', 'age' AS 'age', 'season' AS 'season', 'imageURL' AS 'imageURL'
FROM 'rosters_test' 

INSERT INTO 'rosters'  (number, name, height, weight, birthplace, birthdate ,position ,id ,age ,season, imageURL) 
SELECT 'number',  'name' , 'height', 'weight', 'birthplace', 'birthdate', 'position', 'id', 'age' , 'season', 'imageURL'
FROM 'rosters_test'

I also tried things such as rosters_test.number AS rosters.number as well. Nothing is working: Here is the schema of the two tables:

CREATE TABLE 'rosters_test' ('number' INTEGER, 'name' TEXT, 'height' INTEGER, 'weight' INTEGER, 'birthplace' TEXT, 'birthdate' TEXT, 'position' TEXT, 'id' INTEGER, 'age' INTEGER, 'season' INTEGER, 'imageURL' TEXT)

I had a dual key on the rosters_test table at one point too.

CREATE TABLE 'rosters' ('position' TEXT, 'id' INTEGER, 'weight' INTEGER, 'height' TEXT, 'imageURL' TEXT, 'birthplace' TEXT, 'age' INTEGER, 'name' TEXT, 'birthdate' TEXT, 'number' INTEGER, 'season' INTEGER, PRIMARY KEY('id','season'))

Answer 1

Use INSERT INTO...SELECT and explicity specify the columns (and their order) which you want for the rosters table:

INSERT INTO rosters (position, id, weight, height, imageURL, birthplace, age, name,
                     birthdate, number, season)
SELECT position, id, weight, height, imageURL, birthplace, age, name, birthdate,
       number, season
FROM rosters_test