INSERT OVER statement?

Question

What the difference between

INSERT INTO table VALUES (values)

and

INSERT OVER table VALUES (values)

?

+1. Don't know what it does yet but this works for me `create table #test(c int) INSERT OVER #test VALUES (10)` — Martin Smith, Mar 28 '11 at 14:24
@Martin It's totally undocumented in Books Online though! I thought this was a terrible question, but it's actually a great one :-) — RB., Mar 28 '11 at 14:25
+1 It works for me too.couldnt find any documentation for this. — CloudyMarble, Mar 28 '11 at 14:27
Maybe something to do with composable DML - [unless the author here just used the wrong phrase](http://connect.microsoft.com/SQLServer/feedback/details/535753/insert-over-dml-is-not-allowed-on-a-table-with-a-foreign-key-constraint) — Martin Smith, Mar 28 '11 at 14:36
@Chris [Here](http://connect.microsoft.com/SQLServer/feedback/details/653915/insert-over-syntax). — GSerg, Mar 29 '11 at 12:55

Quassnoi · Answer 1 · 2011-03-28T14:58:30.617

8

Of all reserved keywords, only INTO and OVER work.

SQL:2003 mentions OVERRIDING keyword to override the identity (currently only supported by DB2)

Probably, SQL Server parses it for now but does not actually implement.

The plans generated are identical, and ParameterizedText is expanded into INSERT INTO.

So as for 2008R2, the answer would be this:

No difference, except that INSERT OVER has already wasted about 50 manhours of most curious developers to the moment and there is more to go

edited Mar 28 '11 at 14:58

answered Mar 28 '11 at 14:51

Quassnoi

413,100
91
616
614

1

@Robert: The `OVER` clause used in the window functions does not seem to deal with this. – Quassnoi Mar 28 '11 at 15:10
Yeah, I know. But it is the only Microsoft-defined use of the `OVER` keyword that I can find. – Robert Harvey Mar 28 '11 at 15:18

score 4 · Accepted Answer · answered Apr 04 '11 at 20:05

People at Microsoft say:

Thanks for reporting this issue. We keep recognizing the OVER keyword along with the INTO keyword (with the same meaning) in INSERT statements to provide backward compatibility with the previous versions of SQL Server. This should not present any problem for application development.

Eugene Zabokritski, SQL Engine

score 2 · Answer 3 · answered Mar 28 '11 at 14:32

2

According to this page:

http://msdn.microsoft.com/en-us/library/ms174335.aspx

INSERT OVER is not part of the defined syntax tree. Therefore, even if it works, it's probably not officially supported by Microsoft.

answered Mar 28 '11 at 14:32

Robert Harvey

178,213
47
333
501

+1 - My guess is it's some sort of accident that they left it on. Maybe got mixed-up prepositions in the code or something... – JNK Mar 28 '11 at 14:33

score 0 · Answer 4 · answered Oct 20 '17 at 14:57

Apologies for resurrecting a dead thread, but this shows near the top of a Google search for INSERT OVER. I was hoping to find a better explanation/example here, but this link does a decent job as a starting point. If you're looking for information on the INSERT OVER DML statement (like a Merge command), take a look at this link: https://decipherinfosys.wordpress.com/2007/11/28/sql-server-2008-insert-over-a-dml-statement/

score 0 · Answer 5 · answered Mar 28 '11 at 14:22

0

Presumably INSERT OVER is supposed to overwrite an existing row in the table. For this you would actually use

UPDATE <table> SET <field=value, field2=value2> WHERE <condition to pinpoint row>

answered Mar 28 '11 at 14:22

El Ronnoco

11,753
5
38
65

I don't think this is what he's after, actually. It's apparently undocumented syntax for an `INSERT`... – JNK Mar 28 '11 at 14:33

INSERT OVER statement?

5 Answers5