1

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.

Community
  • 1
  • 1
Anders
  • 11,090
  • 34
  • 91
  • 142

4 Answers4

7

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;
Pierre-Alain Vigeant
  • 21,135
  • 7
  • 60
  • 101
  • 1
    Well, my second query should work since the id column is auto-incrementing and an identity? – Anders Sep 08 '09 at 18:58
  • Yes, if the column was really created with `ID INT IDENTITY NOT NULL PRIMARY KEY` (or similar). Maybe something is missing in your table definition. – Pierre-Alain Vigeant Sep 08 '09 at 19:01
  • This is what it was created as: `[id] [int] IDENTITY(1,1) NOT NULL`. If I attempt to add `PRIMARY KEY` to the end of that line, I get an error saying I cannot add multiple PRIMARY KEY constraints to table 'MyTable'. There is another section in the table definition: `CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ( [id] ASC )`, is this what defines the ID column as the PK? – Anders Sep 08 '09 at 19:06
  • `PRIMARY KEY` is a short for `CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ( [id] ASC )`. So you do have a primary key that is auto incrementing. Odd. Can you be more explicit on the "crash"? What is the Sql Error exactly? – Pierre-Alain Vigeant Sep 08 '09 at 19:34
  • Pierre: The crash that I am getting is not from my query, but from the answer for the link that I posted (`INSERT INTO GroupTable DEFAULT VALUES`). I must have been mistyping something because I just ran my query again and it completed successfully. – Anders Sep 08 '09 at 19:50
1

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.

Beska
  • 11,976
  • 14
  • 73
  • 108
  • My second query does not crash the application, the query from the post I linked does. This is the first time something weird like this has happened. – Anders Sep 08 '09 at 18:59
0

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?

ennuikiller
  • 43,779
  • 13
  • 108
  • 136
0

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.

Ken
  • 342
  • 2
  • 5