CODEWITHSUNDEEP
sqlsql-server
user2393171
user2393171

Reputation: 393

INSERT into table without specifying Column names

I have an INVOICE table and I want to insert values by not specifying the column names using SQL Server.

I have tried this but it is not working.

INSERT INTO INVOICE
VALUES(1, 1, KEYBOARD, 1, 15, 5, 75)

Upvotes: 31

Views: 116695

Answers (5)

Flippsie
Flippsie

Reputation: 481

I agree, this is generally bad practice. However, I can think of one exception. I have a scenario where updating a table is not possible and I am having to select a number of records into a temporary table, update them there, delete them from the source table and then reinsert them. Whilst not using a field list would potentially risk data misalignment or loss, provided the temporary table is create with a SELECT * FROM MyTable INTO #MyTable, it will always align with the subsequent INSERT INTO MyTable SELECT * FROM #MyTable. This future-proofs against any new fields that may be added, any script using a precise set of fields would not necessarily error if it wasn't updated, and the data in any new fields would be lost or inserted into the wrong column of a similar datatype. It's a niche situation, but it also removes the need to modify the script, should the table change. This will only work if you do not need to insert an identity value, which is almost as odd as enabling this functionality in the first place.

Upvotes: 0

Parvez Khan
Parvez Khan

Reputation: 577

yes you can directly insert values into table as follows:

insert into `schema`.`tablename` values(val1,val2,val3);

Upvotes: 9

Brian lee
Brian lee

Reputation: 356

INSERT INTO INVOICE VALUES( 1,1,'KEYBOARD',1,15,5,75);

you forget to include the single quotes in keyboard,text required single quotes in sql

Upvotes: 5

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

As long as you have the right number of columns in your INSERT statement, and as long as all the values except KEYBOARD are some numeric data type, and as long as you have suitable permissions, this should work.

INSERT INTO INVOICE VALUES( 1,1,'KEYBOARD',1,15,5,75);

SQL requires single quotes around text values.

But not using column names isn't a good practice. It's not unheard of for people to change the order of columns in a table. Changing the order of columns isn't a good practice, either, but some people insist on doing it anyway.

If somebody does that, and swaps the 5th and 7th columns in your table, your INSERT statement will still succeed--both those columns are numeric--but the INSERT will screw up your data.

Upvotes: 56

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Why would you want to do this? Not specifying column names is bad coding practice.

In your case, though, keyboard needs to be surrounded by single quotes:

INSERT INTO INVOICE VALUES( 1,1, 'KEYBOARD',1,15,5,75)

If you just don't want to type the column names, you can get them easily in SQL Server Management Studio. Open the "Object Browser", open the database, and choose "Tables". Choose the Invoice table. One of the options is "Columns".

Just click on the "Columns" name (no need to open it) and drag it into a query window. It will insert the list of columns.

Upvotes: 17

Related Questions