1

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.
ohcrap 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'))
Kurt Leadley
  • 513
  • 3
  • 20

1 Answers1

2

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
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Working on you answer. – Kurt Leadley Aug 11 '16 at 03:55
  • You have `rosters_test` as column names. I put in the correct column names as so....`INSERT INTO rosters (position, id, weight, height, imageURL, birthplace, age, name, birthdate, number, season) VALUES SELECT position, id, weight, height, imageURL, birthplace, age, name, birthdate, number, season FROM rosters_test` but still get a syntax error `near select` – Kurt Leadley Aug 11 '16 at 04:00
  • I changed the id column names to `id` instead of `rosters_test`. I don't know why you had that there. – Tim Biegeleisen Aug 11 '16 at 04:01
  • Reload the page, I removed `VALUES` which should not be there. – Tim Biegeleisen Aug 11 '16 at 04:02
  • " I changed the id column names to id instead of rosters_test. I don't know why you had that there" I had it as roster_test.id at one point. – Kurt Leadley Aug 11 '16 at 04:03
  • This updated query works! I will take note of the proper syntax for future reference. Thanks for the help = ) – Kurt Leadley Aug 11 '16 at 04:04
  • I edited the `rosters_test` out of the query in the question since that was not part of the problem. (that was a remnant of another query) – Kurt Leadley Aug 11 '16 at 04:08