Menu
In SQL Server 2000 or above is there anyway to handle an auto generated primary key (identity) column when using a statement like the following?
Insert Into TableName Values(?, ?, ?) My goal is to NOT use the column names at all.
700
An identity column contains a unique numeric value for each row in the table. Whether you can insert data into an identity column and how that data gets inserted depends on how the column is defined.
Once we insert a row in a table, the @@IDENTITY function column gives the IDENTITY value generated by the statement. If we run any query that did not generate IDENTITY values, we get NULL value in the output. The SQL @@IDENTITY runs under the scope of the current session.
To manually insert a new value into the Id column, we first must set the IDENTITY_INSERT flag ON as follows: SET IDENTITY_INSERT Students ON; To set the IDENTIT_INSERT flag ON we need to use the SET statement followed by the flag name and the name of the table.
The SQL INSERT statement is used to insert a one or more records into a table.
By default, if you have an identity column, you do not need to specify it in the VALUES section. If your table is:
ID NAME ADDRESS Then you can do:
INSERT INTO MyTbl VALUES ('Joe', '123 State Street, Boston, MA') This will auto-generate the ID for you, and you don't have to think about it at all. If you SET IDENTITY_INSERT MyTbl ON, you can assign a value to the ID column.
66
Another "trick" for generating the column list is simply to drag the "Columns" node from Object Explorer onto a query window.
35
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
