Menu
I have INVOICE TABLE I want to insert value by not specifying column names using SQL Server, I have tried this but it is not working..please help
INSERT INTO INVOICE VALUES( 1,1,KEYBOARD,1,15,5,75) 
956
There are two ways of using INSERT INTO statement for inserting rows: Only values: First method is to specify only the value of data to be inserted without the column names. INSERT INTO table_name VALUES (value1, value2, value3,…); table_name: name of the table.
We cannot insert data without specifying column names if there is a mismatch between data insertion and the order of column values is different. We can get the following error message.
Because the values are typed in the same order in which the columns appear in the table, it is not necessary to type the column names. As a side note, if you want to insert values into specific columns only, specify only the column names you want to insert values into.
In order to insert default values to the columns, you just need exclude the default columns from the insert list with a SQL insert into statement.
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.
86
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.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
