Search code examples
sqlsql-serverinsert-select

INSERT SELECT with differed table/col stucture

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.

Solution

  • 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.