INSERT SELECT with differed table/col stucture

Question

I am trying to create a INSERT SELECT statement which inserts and converts data from Imported_table to Destination_table.

Imported_table

+------------------+-----------------------+
| Id (varchar(10)) | genre (varchar(4000)) |
+------------------+-----------------------+
| 6                | Comedy                |
+------------------+-----------------------+
| 5                | Comedy                |
+------------------+-----------------------+
| 1                | Action                |
+------------------+-----------------------+

Destination_table (How it should be looking)

+-----------------------------+----------------------------+
| genre_name (PK,varchar(50)) | description (varchar(255)) |
+-----------------------------+----------------------------+
| Comedy                      | Description of Comedy      |
+-----------------------------+----------------------------+
| Action                      | Description of Action      |
+-----------------------------+----------------------------+

Imported_table.Id isn't used at all but is still in this (old) table
Destination_table.genre_name is a primairy key and should be unique (distinct)
Destination_table.description is compiled with CONCAT('Description of ',genre)

My best try

INSERT INTO testdb.dbo.Destination_table (genre_name, description)
SELECT DISTINCT Genre,
       LEFT(Genre,50) AS genre_name,
       CAST(CONCAT('Description of ',Genre) AS varchar(255)) AS description
FROM   MYIMDB.dbo.Imported_table

Gives the error: The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

Thanks in advance.

You are `selecting` 3 `columns` and `inserting` 2, either `select` 3 `insert` 3 or `select` 2 and `insert` 2. — Abhishek, Feb 22 '18 at 14:02

score 1 · Accepted Answer · answered Feb 22 '18 at 14:03

1

The largest error in your query is that you are trying to insert 3 columns into a destination table having only two columns. That being said, I would just use LEFT for both inserted values and take as much space as the new table can hold:

INSERT INTO testdb.dbo.Destination_table (genre_name, description)
SELECT DISTINCT
    LEFT(Genre, 50),
    'Description of ' + LEFT(Genre, 240)    -- 240 + 15 = 255
FROM MYIMDB.dbo.Imported_table;

As a side note, the original genre field is 4000 characters wide, and your new table structure runs the risk of throwing away a lot of information. It is not clear whether you are concerned with this, but it is worth pointing out.

answered Feb 22 '18 at 14:03

Tim Biegeleisen

502,043
27
286
360

Thanks alot! I see what I've done wrong. I tought DISTINCT handled data a differend way.. – Thomas Beumer Feb 22 '18 at 14:11
@ThomasBeumer Out of curiosity, did you think that `DISTINCT` would choose between `Genre` and `LEFT(Genre, 50)` ? – Tim Biegeleisen Feb 22 '18 at 14:11
1

I tought that `DISTINCT genre` would only select 1 of each `genre` but without inserting – Thomas Beumer Feb 22 '18 at 14:17

score 1 · Answer 2 · answered Feb 22 '18 at 14:04

This means your SELECT (genre, genre_name,description) and INSERT (genre_name, description) lists don't match. You need to SELECT the same number of fields as you are specifying in your INSERT.

Try this:

INSERT INTO testdb.dbo.Destination_table (genre_name, description)
SELECT DISTINCT Genre,
       CAST(CONCAT('Description of ',Genre) AS varchar(255)) AS description
FROM   MYIMDB.dbo.Imported_table

score 1 · Answer 3 · answered Feb 22 '18 at 14:04

You have 3 columns in your SELECT, try:

INSERT INTO testdb.dbo.Destination_table (genre_name, description)
SELECT DISTINCT LEFT(Genre,50) AS genre_name,
       CAST(CONCAT('Description of ',Genre) AS varchar(255)) AS description
FROM   MYIMDB.dbo.Imported_table

INSERT SELECT with differed table/col stucture

3 Answers3