SQL: How to insert data into a table with column names

Question

When inserting data into a SQL Server table, is it possible to specify which column you want to insert data to?

For a table with

I know you can have syntax like this:

INSERT INTO MyTable (Name, col4_on, col8_on, col9_on)
VALUES ('myName', 0, 1, 0)

But the above syntax becomes unwieldy when you have lots of columns, especially if they have binary data. It becomes hard to match up which 1 and 0 go with which column. I'm hoping there's a named-parameter like syntax (similar to what C# has) which looks like the following:

INSERT INTO MyTable 
VALUES (Name: 'myName', col4_on: 0, col8_on: 1, col9_on: 0)

Thanks

Answer 1

You must specify the column names. However, there is one exception. If you INSERTing exactly the same number of columns as the target table has in the same order as they are in the table, use this syntax:

INSERT INTO MyTable
VALUES ('val1A', 'val4A', 'val8A')

Note that this is a fragile way of performing an INSERT, because if that table changes, or if the columns are ordered differently on a different system, the INSERT may fail, or worse-- it may put the wrong data in each column.

I've found that when I INSERT a lot of columns, I find the queries easier to read if I can group them somehow. If column names are long, I may put them on separate lines like so:

INSERT INTO MyTable
(
    MyTable_VeryLongName_Col1,
    MyTable_VeryLongName_Col4,
    MyTable_VeryLongName_Col8,
    -- etc.
)
SELECT
    Very_Long_Value_1,
    Very_Long_Value_4,
    Very_Long_Value_8,
    -- etc.

Or you can group 2 columns on a line, or put spaces on every 5, or comment every 10th line, etc. Whatever makes it easier to read.

If you find including column names onerous when INSERTing a lot of rows, then try chaining the data together:

INSERT INTO MyTable (col1, col4, col8)
VALUES ('val1A', 'val4A', 'val8A'),
    ('val1B', 'val4B', 'val8B'),
    -- etc.

Or UNION them together:

INSERT INTO MyTable (col1, col4, col8)
SELECT 'val1A', 'val4A', 'val8A'
UNION ALL 'val1B', 'val4B', 'val8B'
UNION ALL ... -- etc.

Or, SELECT them from another table:

INSERT INTO MyTable (col1, col4, col8)
SELECT val1, va4, val8
FROM MyOtherTable
WHERE -- some condition is met

Answer 2

INSERT INTO MyTable (col1, col4, col8)
VALUES ('val1', 'val4', 'val8')

This statement will add values to the columns mentioned in your INSERT INTO statement, you can write the above query in the following formats it will not make any difference .

INSERT INTO MyTable (col8, col1, col4)
VALUES ('val8', 'val1', 'val4')

OR

INSERT INTO MyTable (col4, col8, col1)
VALUES ('val4', 'val8', 'val1')

to Add multiple rows at a time you can pass multiple rows at a time in you values clause something like this

INSERT INTO MyTable (col4, col8, col1)
VALUES ('val4', 'val8', 'val1'),
       ('val4', 'val8', 'val1'),
       ('val4', 'val8', 'val1'),
       ('val4', 'val8', 'val1')

The order of the values should match the order of the columns mentioned in your INSERT INTO statement.

All above statement will have the same result.

keeping one thing in mind once you have mentioned a column you must provide a value for it

like this

INSERT INTO MyTable (col1, col4, col8)
VALUES ('val1', null, 'val8')

but you cannot do something like this

INSERT INTO MyTable (col1, col4, col8)
VALUES ('val1', 'val8')