Error 191: Some part of your SQL statement is nested too deeply.

When inserting string into SQL Server table using the INSERT predicate that uses string concatenation there seems to be an issue with in SQL Server 2005. There seems to be an upper limit of about 480+ concaenations before the insert fails and you get following error messsge:

Msg 191, Level 15, State 1, Line 1
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
What does that mean? I mean it was a simple insert statement no loops just two brackets; well it seems to be a bug and has been fixed in next major release of SQL Server.

To produce the error, create a new database and try executing the following SQL Statement:

… to create test table …
CREATE TABLE [dbo].[T1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TValue] [varchar](MAX) NULL,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

… test insert statements …

-- Good SQL Statement
INSERT INTO T1 VALUES ('A' + ... + 'A')
GO

Please click here for full statement.

Only difference between the good statement and bad statement? The bad statement has ONE extra concatenation.

Microsoft Connect Article, Link.