Start a new topic

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


Thank You.




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)?

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

Login to post a comment