How can we help you today?
Support Multiple Values in an Insert Statement
We often populate tables with an INSERT statement with multiple Values e.g.
INSERT INTO HashTypes ( Code, Title ) VALUES ('SHA1', 'SHA1') , ('MD5', 'MD5'), -- etc. GO
It appears the VistaDB 5 only supports have one value per INSERT statement which would make such code harder to read, edit, etc.
I believe that T-SQL support unto 1000 values. From https://technet.microsoft.com/en-us/library/ms187905(v=sql.105).aspx
It is a non-standard SQL syntax that is support by SQL Server and SQL Sever Express, if I recall use at my current employer correctly.
I have used it on SQL Server 2008R2, 2012, 2014, and 2016. So at least since then though I suspect longer..
The standard SQL equivalent is to use SELECT UNION
INSERT INTO HashTypes ( Code, Title ) SELECT 'SHA1', 'SHA1' UNION SELECT 'MD5', 'MD5' GO
Does work with VistaDB ,so we are going that way at the moment.
Thank you for your responce
That's a nice syntax. I think we'd seen a syntax before where VALUES and a list could be repeated any number of times in the same INSERT statement (we don't support that one yet, either), but multiple lists separated by commas in the same VALUES clause also makes sense. (Maybe that's a newer syntax?)
We'll look at adding support for this sort of thing in a future version. Does SQL Server support this syntax (and as of what version, if you know)?