MSSQL - inserting multiple rows of data

[oynaz]

Hi guys,

I need to make an SQL script which updates a table with a bunch of file types. My rows are:

FileTypeID (Primary Key), FileTypeName, MIME, ReadOnly (boolean), FileExtension

A have about 40 different file types listed.
What is the most elegant way to do this? I could simply do like this:

INSERT INTO FileTypes (FileTypeID, FileTypeName, MIME, ReadOnly, FileExtension)
VALUES ('1','Video','WhatEverTheMIMETYPEFORMP4is','True','.mp4');

INSERT INTO FileTypes (FileTypeID, FileTypeName, MIME, ReadOnly, FileExtension)
VALUES ('2','Audio','WhatEverTheMIMETYPEFORMP3is','True','.mp3');

..etc.

But is there an easier way?

 

[Cogman]

What version of sql server? Starting with 2008 you can do this

INSERT INTO FileTypes (FileTypeID, FileTypeName, MIME, ReadOnly, FileExtension)
VALUES 
('1','Video','WhatEverTheMIMETYPEFORMP4is','True','.mp4'),
('2','Audio','WhatEverTheMIMETYPEFORMP3is','True','.mp3');

pre 2008, then your best bet is what you are doing. (assuming you are hand rolling the query and running it straight without an application layer).

One thing to note about the multi insert, if one of the values violates a constraint the whole thing will fail and get rolled back. That may or may not be what you are looking for.

 

[ethebubbeth]

Also note that the maximum number of rows you can enter using the INSERT INTO ... VALUES (...),(...) syntax is 1000. More than that and you need to take a different approach.

 

[oynaz]

Hi guys,

Thanks for the replies.
I will not be using anything earlier than SQL 2008, so Cogman's solution will work.
I might roll it into an application later on, but for now I will simply be using Management Studio.