SQL statement to insert record into table that has identity column?

Question

Hello SO:

I am attempting to insert a row manually into my SQL Server data table. This table has 5 columns, one identity and four data columns. I looked at this post, but when I run the selected answer's query (after replacing 'GroupTable' with my table name of course), my SQL Server 2005 management studio crashes.

I have tried variations of the following statements, but obviously no luck thus far:

INSERT INTO MyTable (id, col1, col2, col3, col4)
VALUES (0, 'Column 1 Value', 'Column 2 Value', 'Column 3 Value', 'Column 4 Value')

INSERT INTO MyTable (col1, col2, col3, col4)
VALUES ('Column 1 Value', 'Column 2 Value', 'Column 3 Value', 'Column 4 Value')

Any pointers would be greatly appreciated.

Answer 1

The first time I've heard of mysql having an SQL 2005 management client, but in any case have you tried directly logging to the database from a command line and executing the insert statement from there?

Answer 2

Usually, omitting the id column would generate a new automatic ID if the column is set so. In your case, you can use

SET IDENTITY_INSERT MyTable ON;

INSERT INTO MyTable (id, col1, col2, col3, col4)
VALUES (4567, 'Value1', 'Value2', 'Value3', 'Value4');

SET IDENTITY_INSERT MyTable OFF;

Answer 3

Not sure what you mean by "crashes" but, that second statement should work providing you have the identity specification set correctly on the table. Right-click the table in mangement studio and look at the properties for the id column. isIdentity should be "Yes" and identity increment should be 1.

Answer 4

As some others have said, I'm thinking the second one should work for you, but if you're crashing the management studio when you run the query, whether it's broken or not, your problem is bigger than a malformed sql query.

The studio shouldn't crash, even if you write some pretty awful sql...it should just give you an error message and move on. You might consider reinstalling the management studio if these kinds of errors are common for you.