Post reply

Insert of multiple rows of fixed data erroring with Incorrect syntax near ','

  • November 8, 2012 at 8:34 am

    #284404

    I have seen this format (in QotD and many other places) for inserting multiple rows of configuration data but it has never worked for me (on SQL Server 2005), why?

    Error is Incorrect syntax near ','

    insert COSTING (ID,GROUP1,ROUP2,GROUP3)

    VALUES

    ('ACS1-1-0010','ACS1-2-0010','ACS1-3-0010',''),

    ('ACS1-1-0020','ACS1-2-0010','ACS1-3-0010',''),

    ('ACS2-1-0190','ACS2-2-0010','','')

    I have always changed it to be

    insert COSTING (ID,GROUP1,GROUP2,GROUP3)

    VALUES ('ACS1-1-0010','ACS1-2-0010','ACS1-3-0010','')

    insert COSTING (ID,GROUP1,GROUP2,GROUP3)

    VALUES ('ACS1-1-0020','ACS1-2-0010','ACS1-3-0010','')

    insert COSTING (ID,GROUP1,GROUP2,GROUP3)

    VALUES ('ACS2-1-0190','ACS2-2-0010','','')

    It has been bugging me

  • November 8, 2012 at 8:39 am

    #1557212

    That was implemented on SQL Server 2008.

    In 2005 or previous versions, you need to use several inserts or Selects with UNION ALL. Example:

    insert COSTING (ID,GROUP1,ROUP2,GROUP3)

    SELECT 'ACS1-1-0010','ACS1-2-0010','ACS1-3-0010','' UNION ALL

    SELECT 'ACS1-1-0020','ACS1-2-0010','ACS1-3-0010',''UNION ALL

    SELECT 'ACS2-1-0190','ACS2-2-0010','',''

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 8, 2012 at 8:47 am

    #1557216

    Thanks,

    It's as simple as that then.

    I've used the select ... union all method too

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply