Support Multiple Values in an Insert Statement : VistaDB Support Center

How can we help you today?

Start a new topic

Support Multiple Values in an Insert Statement

Chris Lusena

started a topic over 5 years ago

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.

2 Comments

Rob Parker

said over 5 years ago

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

Chris Lusena

said over 5 years ago

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