why incorrect syntax near ',' when inserting multiple rows at a time in a table?

[santhanalakshmi]

Hi,

I am working on SQL 2008 database. In Micosoft SQL server Management studio, i am trying to insert multiple records at a time in a table, using this query

[B] insert into test1 values(6,'san6',1),( 7,'san7',1);[/B]

Error:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

I think so the syntax was correct. But i dont know why am facing this error. The same problem in SQL Server 2000 also.I am working on my server database.

Please help me out. Thanks in advance.

[ck9663]

You can use a SELECT even with fixed value...Try something like:

insert into test1 
select 6,'san6',1
union all
select 7,'san7',1

Happy Coding!!!

~~ CK

[santhanalakshmi]

Hi,

The code is working finely, which you told to try me. This also very helpful for me. Thanks. Can you tell me ? why my code is not working ?

[gpl]

Very simply, when inserting literal values, you may only insert 1 row at a time.

As the language allows you insert the result of a query, you can use a union query to generate a result set containing multiple rows of literals

[almaz]

santhanalakshmi , your original script is absolutely correct for SQL Server 2008, but not for SQL Server 2000.

Inserting multiple rows using "insert...value s" command is one of new features of SQL Server 2008

[santhanalakshmi]

But my original script its not running..Facing the following errors:

Error:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

[almaz]

Do you connect to SQL Server 2008?

To verify the version of SQL Server you are connected to run the following query:

print @@version

To verify that your database have a SQL2008 compatibility level, check whether the following script returns "100" or not.

select compatibility_level from sys.databases where database_id=db_id()

[santhanalakshmi]

hi,
Thanks for your help ?

when i run this query ?

[B]print @@version [/B]

output :

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

and the second query

[B]use master
select cmptlevel from sysdatabases where dbid=db_id()[/B]

output :
80

In my system, i installed SQL 2000 plus SQL 2008 ? How can i change my version from SQL 2000 to SQL 2008 version.

[almaz]

It means that you are connecting to SQL Server 2000, not SQL Server 2008.

SQL Server 2000 does not allow you to add multiple rows using "insert...value s" command, so you should use approach suggested by ck9663

[NeoPa]

Well caught Almaz. I thought that was available in 2008 (I knew it wasn't in 2005 because I tried it and it failed ;-( ), but I wasn't sure if an outer level of parentheses was required (INSERT Examples (Transact-SQL) tells me it isn't). The OP stated it was SQL 2008 right from the start, which threw me.

[santhanalakshmi]

Dear sir,

You explained me a lot. Thanks for your response. Did you having own blogs ? In future, i need of your help. How can i contact you ? thanks

[NeoPa]

SanthanaLakshmi :
In future, i need of your help. How can i contact you ?

I'm not sure who this is addressed to, but the only way you can request help on here is to post questions in the forums.

Any member is welcome to invite you to make alternative contact with them, but otherwise we have strict rules forbidding members contacting other members directly for technical help.

[kissa49]

Thank you!

ALMAZ, This just helped me a great deal, thank you very much!!!

[almaz]

My pleasure. It's great to know that even after six years these posts are helpful for someone :).