Multiple Values List Insert Into

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Multiple Values List Insert Into

Author

Topic

doco
Yak Posting Veteran

77 Posts

Posted - 2015-05-08 : 11:57:54

Using SQL Server 2012 Developer on Win7

https://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx
The link above shows this example to insert multiple rows but does not like the comma between the two lists. Each list itself works individually. But putting more than one comma separated list, separated by comma, produces an error.

Micorsoft's example:


USE AdventureWorks2008R2;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');
GO

SQL I am using:


insert into ProvalWheelerCommon..memos
values (2,'TCA',1,'Tax Code Area', '2015-05-08 00:00:00.000','A'),(2,'TCA',2,'1','2015-05-08 00:00:00.000','A');

Produces error

quote:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

Not seeing what the problem is. Any help would be greatly appreciated.

TIA

doco

Education is what you have after you've forgotten everything you learned in school

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-05-09 : 01:43:37

Hi,
Se don't have your structure , so some points where to look.

What is the output of this:


SELECT compatibility_level
FROM sys.databases WHERE name = 'AdventureWorks2008R2'

Maybe your compatibility level is not supporting table value constructor.

Also , did you try to insert one by one ?


insert into ProvalWheelerCommon..memos
values (2,'TCA',1,'Tax Code Area', '2015-05-08 00:00:00.000','A')
insert into ProvalWheelerCommon..memos
values(2,'TCA',2,'1','2015-05-08 00:00:00.000','A')

sabinWeb MCP

Kristen
Test

22859 Posts

Posted - 2015-05-09 : 03:58:21

Does ProvalWheelerCommon..memos only have (exactly) 6 columns? or 6 columns plus an additional IDENTITY column?

If not you need


insert into ProvalWheelerCommon..memos
(
    ColName1, ColName2, ..., ColName6
)
values (2,'TCA',1,'Tax Code Area', '2015-05-08 00:00:00.000','A'),(2,'TCA',2,'1','2015-05-08 00:00:00.000','A');

Also worth checking that the columns in the TABLE are in the SAME ORDER as your VALUES. You run the risk that someone adds a column to the table in the future, or changes the order of the columns, so it would be much safer to include a Column List anyway.


EXEC ProvalWheelerCommon..sp_help memos

will list the columns and their data types.

doco
Yak Posting Veteran

77 Posts

Posted - 2015-05-09 : 05:29:02

The Insert Into works one list at a time; there is no identity column; the values list is of correct length and in proper order; the likelihood of a structure change in the future is p=0.

I will check compatibility level when I get back to the office Monday.

Thanks

doco

Education is what you have after you've forgotten everything you learned in school

Kristen
Test

22859 Posts

Posted - 2015-05-09 : 05:50:15

quote:
Originally posted by doco

the likelihood of a structure change in the future is p=0

If you adopt that attitude across the whole of your APP and there IS then a change the effort to find all occurrences, fix them, and re-test the whole application, is huge ... same as using SELECT * without a column list.

Up to you though, of course

I tried the 2x values statement into a #TEMP table here (no idea what your table structure is, not that that should matter for the given error message) and it worked fine, so seems most likely to be Compatibility Mode issue. (I tested it on Compatibility Mode = 80 database and that worked OK ...)

doco
Yak Posting Veteran

77 Posts

Posted - 2015-05-09 : 09:55:34

quote:

If you adopt that attitude across the whole of your APP...

Very true, and for the moment of the comment I was perhaps a bit short sighted. My apologies.

I will post structure when I get back to the office.

Thanks

EDIT:
Compatibility level is 80. I am using SSMS 2012 on my desktop and accessing SQL Server 2008 R2 running databases in SQL Server 2005. Now there is a kettle of fish...

structure:


        [lrsn] [int] NOT NULL,
	[memo_id] [char](4) NULL,
	[memo_line_number] [int] NOT NULL,
	[memo_text] [char](65) NULL,
	[last_update] [datetime] NULL,
	[status] [char](1) NULL

Thanks again.

Education is what you have after you've forgotten everything you learned in school

Kristen
Test

22859 Posts

Posted - 2015-05-09 : 11:24:09

The (static, not #TEMP) table that I tried was Compatibility 80 but on SQL 2008. If SQL 2012 behaves differently that will be a bother!!

Retried with your table structure, both when connected to the DB (which has Compatibility Mode 80) and also from a different DB using MyDatabaseName..MyTableName

All worked OK ...

I'm logged on as SYSADMIN though, but I can't see how permissions would manifest itself as a syntax error.

Any chance the error message is coming from a Trigger? On the tools that I use the "source" would display on the line above the actual error message

Msg 102, Level 15, State 1, Procedure MyTriggerName, Line 2
Incorrect syntax near ','.

(Looks like you Cut & Pasted your error message, so I'm doubting this is the issue ...)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources