Insert into Table Without Specifying Column Names

INSERT into table without specifying Column names and changing the values in a known column

As far as I know you can't write something like

INSERT INTO someTable(column1,*)
SELECT value, * FROM someTable WHERE ...

where you mix a single column name with '*'

I think this needs to be done in separate steps if possible, first create a temp table and fill it with data for the wanted month

CREATE GLOBAL TEMPORARY TABLE a
ON COMMIT PRESERVE ROWS
AS
select * from someTable where monthColumn = '201901';

Then update the month column in the temp table

UPDATE a SET monthColumn  = '201902';

Then "copy" the data back to the original table

INSERT INTO  someTable 
SELECT * FROM a;

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)

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.

Oracle insert data from another table without column names

The only way is with some dynamic SQL, by relying on column names; for example, say you have the tables

CREATE TABLE Table1
(
    name                                    VARCHAR2(100),
    surname                                 VARCHAR2(100),
    age                                     NUMBER
);

CREATE TABLE Table2
(
    name                                    VARCHAR2(100),
    age                                     NUMBER,
    oneMoreColumn                           NUMBER,
    surname                                 VARCHAR2(100)
);

you can do:

declare
    vSQL    varchar2(1000);
    vCols   varchar2(1000);
begin    
    select listagg(tc1.column_name, ', ') within group (order by tc1.column_name)
    into vCols
    from user_tab_columns tc1
           inner join user_tab_columns tc2
             on(tc1.column_name = tc2.column_name)
    where tc1.table_name = 'TABLE1'
      and tc2.table_name = 'TABLE2';
    --
    vSQL := 'insert into table2( ' || vCols || ') select ' || vCols || ' from table1';
    --
    dbms_output.put_line(vSQL);
    --
    execute immediate vSQL;
end;

this will build and execute the statement:

insert into table2( AGE, NAME, SURNAME) select AGE, NAME, SURNAME from table1

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

Insert values without mentioning the column name

If this is just for experimental purposes yes you can:

INSERT INTO Persons VALUES (NULL, 'Lastname', 'Firstname', 'Address test', 'city of angels');

But I strongly urge you to not make it a habit of doing this. Always add columns, as eloquently stated by David's answer.

INSERT INTO Persons (LastName, FirstName, Address, City) VALUES ('Last', 'First', 'Add', 'City');

Fiddle

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.

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)

---

Related Topics