CODEWITHSUNDEEP
sqlsql-server
Fotini Skoti
Fotini Skoti

Reputation: 43

insert rows into table with identity column

My table is this: 

CREATE TABLE studio(

    s_id           int NOT NULL IDENTITY(1,1),
    name           varchar(30),
    country        varchar(15),
    income_money   float(53),
    movies         int,

    CONSTRAINT primaryKeyStudio PRIMARY KEY(s_id)
)

I want to insert values into table studio, is it better this way:

INSERT INTO studio VALUES ('Walt Disney Studios','USA',45041000.99,50)
INSERT INTO studio VALUES ('Warner Bros Studios','London',30205000.76,60)
INSERT INTO studio VALUES ('Universal Studios','Hollywood',50235499.87,80)

Or this way:

INSERT INTO studio(name,country,income_money,movies) VALUES ('Walt Disney Studios','USA',45041000.99,50)
INSERT INTO studio(name,country,income_money,movies) VALUES ('Warner Bros Studios','London',30205000.76,60)
INSERT INTO studio(name,country,income_money,movies) VALUES ('Universal Studios','Hollywood',50235499.87,80)

Upvotes: 2

Views: 193

Answers (3)

codelover
codelover

Reputation: 317

Your second approach is always better since if you miss inserting a row by mistake,the insertion just takes place,and hence difficult to find the missing value

Whereas in the second method the exact values to which you have to insert is known to you initially itself

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726967

The second way, i.e. this one

INSERT INTO studio(name,country,income_money,movies) VALUES ('Walt Disney Studios','USA',45041000.99,50)

where you list column names explicitly is better regardless of the identity column, for several reasons:

  • It is more readable - Users who are not familiar with the table at all can match up the values to their meaning,
  • Users do not need to remember what columns are in the table - Users who are familiar with your table will have easier time expanding your INSERT to use additional columns
  • Changing the schema does not break this INSERT - Someone adding a column in the middle would not break your existing code.

Upvotes: 2

Kevin Bowersox
Kevin Bowersox

Reputation: 94499

The first method requires a value for each column to be provided. If a new column is added to the table all insert statements must be rewrote to include the new column. Statements wrote using the second approach would remain functional since they specify columns explicitly.

I would recommend using the second approach.

Upvotes: 0

Related Questions