Start a new topic

Multiple Row Insert

I'm trying to write a query to insert multiple rows. I saw this thead (http://support.vistadb.com/support/discussions/topics/4000336627) which says the SQL server syntax isn't supported, but using unions work. I've tried this with 5.7 and 6 beta. Am I doing something wrong?


 

INSERT INTO States ([Name], [Abbreviation])
SELECT 'Alabama', 'AL' UNION
SELECT 'Alaska', 'AK' UNION
SELECT 'Arizona', 'AZ' UNION
SELECT 'Arkansas', 'AR' UNION
SELECT 'California', 'CA' UNION
SELECT 'Colorado', 'CO' UNION
SELECT 'Connecticut', 'CT' UNION
SELECT 'Delaware', 'DE' UNION
SELECT 'Florida', 'FL' UNION
SELECT 'Georgia', 'GA' UNION
SELECT 'Hawaii', 'HI' UNION
SELECT 'Idaho', 'ID' UNION
SELECT 'Illinois', 'IL' UNION
SELECT 'Indiana', 'IN' UNION
SELECT 'Iowa', 'AI' UNION
SELECT 'Kansas', 'KS' UNION
SELECT 'Kentucky', 'KY' UNION
SELECT 'Lousiana', 'LA' UNION
SELECT 'Maine', 'ME' UNION
SELECT 'Maryland', 'MD' UNION
SELECT 'Massachusetts', 'MA' UNION
SELECT 'Michigan', 'MI' UNION
SELECT 'Mississippi', 'MS' UNION
SELECT 'Montana', 'MT' UNION
SELECT 'Nebraska', 'NE' UNION
SELECT 'Nevada', 'NV' UNION
SELECT 'New Hampshire', 'NH' UNION
SELECT 'New Jersey', 'NJ' UNION
SELECT 'New Mexico', 'NM' UNION
SELECT 'New York', 'NY' UNION
SELECT 'North Carolina', 'NC' UNION
SELECT 'North Dakota', 'ND' UNION
SELECT 'Ohio', 'OH' UNION
SELECT 'Oklahoma', 'OK' UNION
SELECT 'Oregon', 'OR' UNION
SELECT 'Pennsylvania', 'PA' UNION
SELECT 'Rhode Island', 'RI' UNION
SELECT 'South Carolina', 'SC' UNION
SELECT 'South Dakota', 'SD' UNION
SELECT 'Tennessee', 'TN' UNION
SELECT 'Texas', 'TX' UNION
SELECT 'Utah', 'UT' UNION
SELECT 'Vermont', 'VT' UNION
SELECT 'Virginia', 'VA' UNION
SELECT 'Washington', 'WA' UNION
SELECT 'West Virginia', 'WV' UNION
SELECT 'Wisconsin', 'WI' UNION
SELECT 'Wyoming', 'WY' UNION

 

The error I get is:


Line #: 11; Column #: 1

Error 509 (Provider v. 5.7.4.0): Invalid or incomplete statement: SELECT

Line #: 10; Column #: 1

Error 509 (Provider v. 5.7.4.0): Invalid or incomplete statement: SELECT

Line #: 9; Column #: 1

Error 509 (Provider v. 5.7.4.0): Invalid or incomplete statement: SELECT

Line #: 8; Column #: 1

Error 509 (Provider v. 5.7.4.0): Invalid or incomplete statement: SELECT

Line #: 7; Column #: 1

Error 509 (Provider v. 5.7.4.0): Invalid or incomplete statement: SELECT

Line #: 6; Column #: 1

Error 509 (Provider v. 5.7.4.0): Invalid or incomplete statement: SELECT

Line #: 5; Column #: 1

Error 509 (Provider v. 5.7.4.0): Invalid or incomplete statement: SELECT

Line #: 4; Column #: 1

Error 509 (Provider v. 5.7.4.0): Invalid or incomplete statement: SELECT

Line #: 3; Column #: 1

Error 509 (Provider v. 5.7.4.0): Invalid or incomplete statement: SELECT

Line #: 2; Column #: 1

Error 509 (Provider v. 5.7.4.0): Invalid or incomplete statement: INSERT

Line #: 1; Column #: 1



You seem to have a trailing UNION on the final SELECT, so it's expecting another SELECT to follow.  That's probably what the error is complaining about.


Also, it might require parentheses around the sub-query of the INSERT (from before the first SELECT to after the final SELECT of the multi-UNION), but it may also accept it without them.


Crap. Thank you. I missed the trailing UNION.

Login to post a comment