SQL Insert Without Specifying Columns. What Happens

SQL INSERT without specifying columns. What happens?

Your prof was right - you should name the columns explicitly before naming the values.

In this case though the values will be inserted in the order that they appear in the table definition.

The problem with this is that if that order changes, or columns are removed or added (even if they are nullable), then the insert will break.

In terms of its usefulness, not that much in production code. If you're hand coding a quick insert then it might just help save you typing all the column names out.

MYSQL: how to insert statement without specifying col names or question marks?

MySQL's syntax for INSERT is documented here: http://dev.mysql.com/doc/refman/5.7/en/insert.html

There is no wildcard syntax like you show. The closest thing is to omit the column names:

INSERT INTO MyTable VALUES (...);

But I don't recommend doing that. It works only if you are certain you're going to specify a value for every column in the table (even the auto-increment column), and your values are guaranteed to be in the same order as the columns of the table.

You should learn to use code to build the SQL query based on arrays of values in your application. Here's a Python example the way I do it. Suppose you have a dict of column: value pairs called data_values.

placeholders = ['%s'] * len(data_values)
sql_template = """
INSERT INTO MyTable ({columns}) VALUES ({placeholders})
"""
sql = sql_template.format(
    columns=','.join(keys(data_values)),
    placeholders=','.join(placeholders)
)
cur = db.cursor()
cur.execute(sql, data_values)

Why it is mandatory to pass column names to INSERT statement when we have default Getdate() column?

If you don't specify the columns, then you are implicitly stating you are inserting values into all the columns, and thus must define those values. A DEFAULT value is only used if you explicitly state DEFAULT in the VALUES clause or omit the column entirely from the INTO clause.

As such INSERT INTO Student VALUES... is equivalent to INSERT INTO Students ([Name], Grade, Created_Date) VALUES... and in the above, you don't provide a value for Created_Date so the statement fails.

If you really don't want to include the columns (which is ill-advised) use DEFAULT in the VALUES clause:

INSERT INTO Student
VALUES ( 'Uma', 'A+',DEFAULT)

How to INSERT INTO table from SELECT without specifying columns?

What about using a temporary table? You don't have to know the structure in advance.

select 
 t.* 
into #beep
from ThisOrThatTable as t

SQL Server Insert with no specified columns

Though I'm not sure why would you need such a table, the answer to your question is to use the keyword DEFAULT:

INSERT INTO EmailGroup (EmailGroupGuid)
OUTPUT inserted.EmailGroupGuid INTO @Id
VALUES(DEFAULT);

Another option is to use DEFAULT VALUES, as shown in Pawan Kumar's answer.

The key difference between these two options is that specifying the columns list and using the keyword default gives you more control.

It doesn't seem much when the table have a single column, but if you will add columns to the table, and want to insert specific values to them, using default values will no longer be a valid option.

From Microsoft Docs on INSERT (Transact-SQL):

DEFAULT

Forces the Database Engine to load the default value defined for a column.

If a default does not exist for the column and the column allows null values, NULL is inserted.

For a column defined with the timestamp data type, the next timestamp value is inserted.

DEFAULT is not valid for an identity column.

DEFAULT VALUES

Forces the new row to contain the default values defined for each column.

So as you can see, default is column based, while default values is row based.

SQL Insert without knowing all of the values for a new row

Technically, it's not.

However, you can insert dummy values (like NULLs or defaults) into the problem columns and then update them later when you know the correct values.

Is insert statement without column approve performance in Oracle

From my experience there is no gain in omitting column names - also it's a bad practice to do so, since if column order changes (sometimes people do that, for clarity, but they really don't need to) and their definition allows to insert the data, you will get wrong data in wrong columns.

As a rule of thumb it's not worth the trouble. Always specify column list that you're putting values into. Database has to check that anyways.

Related: SQL INSERT performance omitting field names?

T-SQL Insert into table without having to specify every column

You can do this quite easily actually:

-- Select everything into temp table
Select * Into 
    #tmpBigTable
    From [YourBigTable]

-- Drop the Primary Key Column from the temp table  
Alter Table #tmpBigTable Drop Column [PrimaryKeyColumn]

-- Insert that into your other big table
Insert Into [YourOtherBigTable]
    Select * From #tmpBigTable

-- Drop the temp table you created
Drop Table #tmpBigTable

Provided you have Identity Insert On in "YourOtherBigTable" and columns are absolutely identical you will be okay.

How to insert without specifying the column name?

This is a solution to your problem, but I don't really advocate it. Your tables are for different entities, so I'm not so sure that a generic stored procedure is a good idea.

One solution is to use the DEFAULT keyword:

SET @cmd = 'INSERT INTO ' + @tableName + ' VALUES(DEFAULT, ' + @codeToInsert + ',' + @detailToInsert + ' )' + ' ';

Oh, I really don't like that. It means that the insert depends on the order that the columns are defined in the table -- and woe to anyone who adds a new column and messes up this code, far away from the creation of the new column.

Another solution that I'd be slightly more comfortable with is to create views such as:

create v_people_for_update as
    select people_id as id, people_name as name, people_account as account
    from people;

Then, insert into the view:

    SET @cmd = 'INSERT INTO ' + @viewName(name, account) + ' VALUES(, ' + @codeToInsert + ',' + @detailToInsert + ' )' + ' ';

This at least lets you specify the columns (a good thing) and the collection of views can be named so it is obvious that this stored procedure is working on them.

However, if you have tables with such similar structures, then you should probably combine them into one table and dispense with the idea of using dynamic SQL to choose among different tables. Just use one table and add a column specifying the type of thing that each row refers to.

---

Related Topics