Post reply

Insert statement

  • January 11, 2007 at 4:42 am

    #122234

    Hi all,

    Just need help cleaning up a simple Insert statement, I've got a table with columns as follows:

    EmployeeID, DistinguishedName, loginID, displayname, , department, title, position, phone, fax, section, alternative contact, manager, site, voice, Updated (getdate?!?!?), email.

    I just need to enter data for one user, the code should look something like this, but I'd be grateful if someone could check my syntax...

    Insert into StaffDetails

    EmployeeID(all null), DistinguishedName, loginID, displayname, department, title, position, phone, fax, section, alternative contact, manager, site, voice, Updated (getdate?!?!?), email.

    Null, LoginIDvalue, DistinguishedNameValue, "Crawford, Cindy"...etc.

    As above, the loginID field holds records as "surname,firstname"...how do I ensure that "Cindy" doesn't appear in "Displayname"?  Also, there are some fields where I won't have any info, is it enough to type a ",," to tell SQL to ignore that field??

     

    Thanks in advance,

     

    Jaybee.

  • January 11, 2007 at 4:55 am

    #682650

    Hi,

    the column list in INSERT INTO needs to be in parentheses, and it has to be followed by SELECT (or VALUES).

    If you want to skip certain column (not insert anything in it), don't include it in the column list. It wouldn't work to use ",,". Once the column is mentioned, you have to supply a value for it (at least NULL or ''(empty string, two single quotes immediately one after another))

    "Cindy" will not appear in next column because the "," is not evaluated as delimiter if it is inside a text string. Text should always be enclosed in single quotes (see below), not double - because double quotes depend on settings, while single quotes work always.

    Simplified example:

    CREATE TABLE test(col1 INT, col2 VARCHAR(20), col3 VARCHAR(20), col4 int)

    INSERT INTO test (col1, col3, col4)

    SELECT 1, 'some,thing', NULL

    INSERT INTO test(col1, col2, col4) VALUES (2,'asdfg',21)

    SELECT * FROM test

    DROP TABLE test

  • January 12, 2007 at 5:42 am

    #682872

    How's this?  (the values have been changed to protect my contract!)

    insert into 'staffdetails'

    (loginID, displayname,  department, position, phone, fax, email)

    values (PlumbD01, 'Plum,Deborah (ELSD)', 'Energy and Legal Services', 'Lease-Lend Officer', '020 8343 4500', '020 8343 4501',  deborah.plum@phoneyisp.com)

  • January 12, 2007 at 6:44 am

    #682890

    Yes, this should work. Just remember to enclose the email in single quotes, too.

  • January 12, 2007 at 8:47 am

    #682937

    Appreciated - got another one for you on the forum if you want!!

  • January 12, 2007 at 9:06 am

    #682952

    You're welcome ... and I see you already got response to that other post from other people - there is nothing I can add to it. Maybe except "be careful when updating and test the results first".

Viewing 6 posts - 1 through 5 (of 5 total)

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