TSQL Multiple Inserts

开发者_JS百科

Received a query in this format for a data import...is there any equivelent in TSQL where you would only have to use "insert into blah" once? (so that I can convert this script)

insert into marital_status (id,description) 
values
   (1,'Married'),
   (2,'Separated'),
   (3,'Never Married'),
   (4,'Divorced'),
   (5,'Widowed'),
   (6,'Co-Habitation'),
   (7,'No Response')

---

This is a T-SQL script - for Server 2008 - unforutnately, in 2005, you don't have this feature available :-(

You need to write this query like this:

insert into marital_status (id,description) 
values(1,'Married')
insert into marital_status (id,description) 
values(2,'Separated')
insert into marital_status (id,description) 
values(3,'Never Married')
insert into marital_status (id,description) 
values(4,'Divorced')
insert into marital_status (id,description) 
values(5,'Widowed')
insert into marital_status (id,description) 
values(6,'Co-Habitation')
insert into marital_status (id,description) 
values(7,'No Response')

---

Another alternative would be:

insert into marital_status (id,description) 
    SELECT 1,'Married'       UNION ALL
    SELECT 2,'Separated'     UNION ALL
    SELECT 3,'Never Married' UNION ALL
    SELECT 4,'Divorced'      UNION ALL
    SELECT 5,'Widowed'       UNION ALL
    SELECT 6,'Co-Habitation' UNION ALL
    SELECT 7,'No Response'