Table Value Constructor (TVC) allows to group multiple rows of data in a single DML statement. Before SQL Server 2008, inserting multiple rows to a table required multiple INSERT INTO VALUES clause. However, table value constructor (TVC) does this in one go. TVC works as a VALUES clause in INSERT/ SELECT as well as in USING clause within a MERGE statement.
Using Table Value Constructor – Insert Multiple Rows
In this query, creating employee table structure. Furthermore, using VALUES clause surrounded by left & right parenthesis “( )”. Each row separated by parentheses treated as a single row with a comma at the end. Therefore, this method specifies multiple rows in a single DML statement. For instance, we can create a table on the fly using the VALUES clause.
USE tempdb; DROP TABLE IF EXISTS Employee; CREATE TABLE Employee ( Id INT NOT NULL, [Name] VARCHAR(500) NOT NULL, Salary DECIMAL(18,2) NULL ); --Table value constructor VALUES clause INSERT INTO Employee (Id, Name, Salary) VALUES (1, 'Brown', NULL) ,(2, 'Peter', 2300.50) ,(3, 'Remo', 1000.00); SELECT * FROM Employee;
Traditional Methods – Insert Multiple Rows
Using old design queries to insert multiple rows. Following query includes multiple INSERT INTO statements to insert data in the employee table. Similarly, using UNION ALL to group multiple rows and insert them in one go.
--Traditional Method to Insert Rows USE tempdb; INSERT INTO Employee (Id, Name, Salary) VALUES (4, 'Mak', NULL) INSERT INTO Employee (Id, Name, Salary) VALUES (5, 'Tiko', 500.00) INSERT INTO Employee (Id, Name, Salary) VALUES (6, 'Sam', 120); --Insert Multiple Rows With UNION ALL INSERT INTO Employee (Id, Name, Salary) SELECT 7, 'Vishal', 300.00 UNION ALL SELECT 8, 'Sangram', 400.00 UNION ALL SELECT 9, 'Shailesh', 800.00; SELECT * FROM Employee WHERE ID BETWEEN 4 AND 9;
Table Value Constructor – SELECT Statement
TVC applied in below query to create a table on the fly using VALUES clause. A single row is wrapped in parenthesis ( ) and multiple rows are separated by commas. TVC within FORM clause produces data, also a new table name Department is specified with a list of column names (Id, Name) comma-separated.
SELECT * FROM (VALUES (1, 'Account') , (2, 'HR') , (3, 'IT')) as Department (Id, Name);
Table Value Constructor – Merge Statement – Using Clause
Another way to use a TVC in merge statement with USING clause. In the following query, within USING clause providing the values to insert or update in the employee table. Using clause data treated as a derived table and source data to merge statement.
SELECT * FROM Employee WHERE ID IN (1,2); --Before MERGE INTO Employee As Targ USING (VALUES (1, 'Visha', 400) ,(2, 'Niel' , 700) ) as Src (Id, Name, Salary) ON Src.Id = Targ.Id WHEN MATCHED THEN UPDATE SET Targ.Name = Src.Name ,Targ.Salary = Src.Salary WHEN NOT MATCHED THEN INSERT (Id, Name, Salary) VALUES (Src.Id, Src.Name, Src.Salary); SELECT * FROM Employee WHERE ID IN (1,2); --After
Table Value Constructor – SQL JOINs
Another approach to use TVC with SQL JOINs. By specifying multiple values in FORM clause and joining with the base table. Dept(name) considered as derived table constructed using TVC.
SELECT E.Id, E.Name, EmpContact.Contact FROM Employee as E INNER JOIN (VALUES (1, '020-944221'), (2,'020-323231')) as EmpContact(EmpId ,Contact) ON E.Id = EmpContact.EmpId;
Table Value Constructor – CROSS APPLY
Similarly, using a TVC with CROSS APPLY and other types of joins. Address table created on the fly using a TVC and cross applied with an employee table.
SELECT * FROM Employee CROSS APPLY (VALUES ('Mumbai')) as Address(City);
Table Value Constructor – Limitations
TVC can be used only with 1000 rows. Trying to insert more than 1000 rows in a single DML statement with TVC will raise error 10738 stating maximum rows limit reached.
USE tempdb; DROP TABLE IF EXISTS Number; CREATE TABLE Number ( Num INT ); INSERT INTO Number(Num) VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('10'), ('11'), ('12'), ('13'), ('14'), ('15'), ('16'), ('17'), ('18'), ('19'), ('20'), ('21'), ('22'), ('23'), ('24'), ('25'), ('26'), ('27'), ('28'), ('29'), ('30'), ('31'), ('32'), ('33'), ('34'), ('35'), ('36'), ('37'), ('38'), ('39'), ('40'), ('41'), ('42'), ('43'), ('44'), ('45'), ('46'), ('47'), ('48'), ('49'), ('50'), ('51'), ('52'), ('53'), ('54'), ('55'), ('56'), ('57'), ('58'), ('59'), ('60'), ('61'), ('62'), ('63'), ('64'), ('65'), ('66'), ('67'), ('68'), ('69'), ('70'), ('71'), ('72'), ('73'), ('74'), ('75'), ('76'), ('77'), ('78'), ('79'), ('80'), ('81'), ('82'), ('83'), ('84'), ('85'), ('86'), ('87'), ('88'), ('89'), ('90'), ('91'), ('92'), ('93'), ('94'), ('95'), ('96'), ('97'), ('98'), ('99'), ('100'), ('101'), ('102'), ('103'), ('104'), ('105'), ('106'), ('107'), ('108'), ('109'), ('110'), ('111'), ('112'), ('113'), ('114'), ('115'), ('116'), ('117'), ('118'), ('119'), ('120'), ('121'), ('122'), ('123'), ('124'), ('125'), ('126'), ('127'), ('128'), ('129'), ('130'), ('131'), ('132'), ('133'), ('134'), ('135'), ('136'), ('137'), ('138'), ('139'), ('140'), ('141'), ('142'), ('143'), ('144'), ('145'), ('146'), ('147'), ('148'), ('149'), ('150'), ('151'), ('152'), ('153'), ('154'), ('155'), ('156'), ('157'), ('158'), ('159'), ('160'), ('161'), ('162'), ('163'), ('164'), ('165'), ('166'), ('167'), ('168'), ('169'), ('170'), ('171'), ('172'), ('173'), ('174'), ('175'), ('176'), ('177'), ('178'), ('179'), ('180'), ('181'), ('182'), ('183'), ('184'), ('185'), ('186'), ('187'), ('188'), ('189'), ('190'), ('191'), ('192'), ('193'), ('194'), ('195'), ('196'), ('197'), ('198'), ('199'), ('200'), ('201'), ('202'), ('203'), ('204'), ('205'), ('206'), ('207'), ('208'), ('209'), ('210'), ('211'), ('212'), ('213'), ('214'), ('215'), ('216'), ('217'), ('218'), ('219'), ('220'), ('221'), ('222'), ('223'), ('224'), ('225'), ('226'), ('227'), ('228'), ('229'), ('230'), ('231'), ('232'), ('233'), ('234'), ('235'), ('236'), ('237'), ('238'), ('239'), ('240'), ('241'), ('242'), ('243'), ('244'), ('245'), ('246'), ('247'), ('248'), ('249'), ('250'), ('251'), ('252'), ('253'), ('254'), ('255'), ('256'), ('257'), ('258'), ('259'), ('260'), ('261'), ('262'), ('263'), ('264'), ('265'), ('266'), ('267'), ('268'), ('269'), ('270'), ('271'), ('272'), ('273'), ('274'), ('275'), ('276'), ('277'), ('278'), ('279'), ('280'), ('281'), ('282'), ('283'), ('284'), ('285'), ('286'), ('287'), ('288'), ('289'), ('290'), ('291'), ('292'), ('293'), ('294'), ('295'), ('296'), ('297'), ('298'), ('299'), ('300'), ('301'), ('302'), ('303'), ('304'), ('305'), ('306'), ('307'), ('308'), ('309'), ('310'), ('311'), ('312'), ('313'), ('314'), ('315'), ('316'), ('317'), ('318'), ('319'), ('320'), ('321'), ('322'), ('323'), ('324'), ('325'), ('326'), ('327'), ('328'), ('329'), ('330'), ('331'), ('332'), ('333'), ('334'), ('335'), ('336'), ('337'), ('338'), ('339'), ('340'), ('341'), ('342'), ('343'), ('344'), ('345'), ('346'), ('347'), ('348'), ('349'), ('350'), ('351'), ('352'), ('353'), ('354'), ('355'), ('356'), ('357'), ('358'), ('359'), ('360'), ('361'), ('362'), ('363'), ('364'), ('365'), ('366'), ('367'), ('368'), ('369'), ('370'), ('371'), ('372'), ('373'), ('374'), ('375'), ('376'), ('377'), ('378'), ('379'), ('380'), ('381'), ('382'), ('383'), ('384'), ('385'), ('386'), ('387'), ('388'), ('389'), ('390'), ('391'), ('392'), ('393'), ('394'), ('395'), ('396'), ('397'), ('398'), ('399'), ('400'), ('401'), ('402'), ('403'), ('404'), ('405'), ('406'), ('407'), ('408'), ('409'), ('410'), ('411'), ('412'), ('413'), ('414'), ('415'), ('416'), ('417'), ('418'), ('419'), ('420'), ('421'), ('422'), ('423'), ('424'), ('425'), ('426'), ('427'), ('428'), ('429'), ('430'), ('431'), ('432'), ('433'), ('434'), ('435'), ('436'), ('437'), ('438'), ('439'), ('440'), ('441'), ('442'), ('443'), ('444'), ('445'), ('446'), ('447'), ('448'), ('449'), ('450'), ('451'), ('452'), ('453'), ('454'), ('455'), ('456'), ('457'), ('458'), ('459'), ('460'), ('461'), ('462'), ('463'), ('464'), ('465'), ('466'), ('467'), ('468'), ('469'), ('470'), ('471'), ('472'), ('473'), ('474'), ('475'), ('476'), ('477'), ('478'), ('479'), ('480'), ('481'), ('482'), ('483'), ('484'), ('485'), ('486'), ('487'), ('488'), ('489'), ('490'), ('491'), ('492'), ('493'), ('494'), ('495'), ('496'), ('497'), ('498'), ('499'), ('500'), ('501'), ('502'), ('503'), ('504'), ('505'), ('506'), ('507'), ('508'), ('509'), ('510'), ('511'), ('512'), ('513'), ('514'), ('515'), ('516'), ('517'), ('518'), ('519'), ('520'), ('521'), ('522'), ('523'), ('524'), ('525'), ('526'), ('527'), ('528'), ('529'), ('530'), ('531'), ('532'), ('533'), ('534'), ('535'), ('536'), ('537'), ('538'), ('539'), ('540'), ('541'), ('542'), ('543'), ('544'), ('545'), ('546'), ('547'), ('548'), ('549'), ('550'), ('551'), ('552'), ('553'), ('554'), ('555'), ('556'), ('557'), ('558'), ('559'), ('560'), ('561'), ('562'), ('563'), ('564'), ('565'), ('566'), ('567'), ('568'), ('569'), ('570'), ('571'), ('572'), ('573'), ('574'), ('575'), ('576'), ('577'), ('578'), ('579'), ('580'), ('581'), ('582'), ('583'), ('584'), ('585'), ('586'), ('587'), ('588'), ('589'), ('590'), ('591'), ('592'), ('593'), ('594'), ('595'), ('596'), ('597'), ('598'), ('599'), ('600'), ('601'), ('602'), ('603'), ('604'), ('605'), ('606'), ('607'), ('608'), ('609'), ('610'), ('611'), ('612'), ('613'), ('614'), ('615'), ('616'), ('617'), ('618'), ('619'), ('620'), ('621'), ('622'), ('623'), ('624'), ('625'), ('626'), ('627'), ('628'), ('629'), ('630'), ('631'), ('632'), ('633'), ('634'), ('635'), ('636'), ('637'), ('638'), ('639'), ('640'), ('641'), ('642'), ('643'), ('644'), ('645'), ('646'), ('647'), ('648'), ('649'), ('650'), ('651'), ('652'), ('653'), ('654'), ('655'), ('656'), ('657'), ('658'), ('659'), ('660'), ('661'), ('662'), ('663'), ('664'), ('665'), ('666'), ('667'), ('668'), ('669'), ('670'), ('671'), ('672'), ('673'), ('674'), ('675'), ('676'), ('677'), ('678'), ('679'), ('680'), ('681'), ('682'), ('683'), ('684'), ('685'), ('686'), ('687'), ('688'), ('689'), ('690'), ('691'), ('692'), ('693'), ('694'), ('695'), ('696'), ('697'), ('698'), ('699'), ('700'), ('701'), ('702'), ('703'), ('704'), ('705'), ('706'), ('707'), ('708'), ('709'), ('710'), ('711'), ('712'), ('713'), ('714'), ('715'), ('716'), ('717'), ('718'), ('719'), ('720'), ('721'), ('722'), ('723'), ('724'), ('725'), ('726'), ('727'), ('728'), ('729'), ('730'), ('731'), ('732'), ('733'), ('734'), ('735'), ('736'), ('737'), ('738'), ('739'), ('740'), ('741'), ('742'), ('743'), ('744'), ('745'), ('746'), ('747'), ('748'), ('749'), ('750'), ('751'), ('752'), ('753'), ('754'), ('755'), ('756'), ('757'), ('758'), ('759'), ('760'), ('761'), ('762'), ('763'), ('764'), ('765'), ('766'), ('767'), ('768'), ('769'), ('770'), ('771'), ('772'), ('773'), ('774'), ('775'), ('776'), ('777'), ('778'), ('779'), ('780'), ('781'), ('782'), ('783'), ('784'), ('785'), ('786'), ('787'), ('788'), ('789'), ('790'), ('791'), ('792'), ('793'), ('794'), ('795'), ('796'), ('797'), ('798'), ('799'), ('800'), ('801'), ('802'), ('803'), ('804'), ('805'), ('806'), ('807'), ('808'), ('809'), ('810'), ('811'), ('812'), ('813'), ('814'), ('815'), ('816'), ('817'), ('818'), ('819'), ('820'), ('821'), ('822'), ('823'), ('824'), ('825'), ('826'), ('827'), ('828'), ('829'), ('830'), ('831'), ('832'), ('833'), ('834'), ('835'), ('836'), ('837'), ('838'), ('839'), ('840'), ('841'), ('842'), ('843'), ('844'), ('845'), ('846'), ('847'), ('848'), ('849'), ('850'), ('851'), ('852'), ('853'), ('854'), ('855'), ('856'), ('857'), ('858'), ('859'), ('860'), ('861'), ('862'), ('863'), ('864'), ('865'), ('866'), ('867'), ('868'), ('869'), ('870'), ('871'), ('872'), ('873'), ('874'), ('875'), ('876'), ('877'), ('878'), ('879'), ('880'), ('881'), ('882'), ('883'), ('884'), ('885'), ('886'), ('887'), ('888'), ('889'), ('890'), ('891'), ('892'), ('893'), ('894'), ('895'), ('896'), ('897'), ('898'), ('899'), ('900'), ('901'), ('902'), ('903'), ('904'), ('905'), ('906'), ('907'), ('908'), ('909'), ('910'), ('911'), ('912'), ('913'), ('914'), ('915'), ('916'), ('917'), ('918'), ('919'), ('920'), ('921'), ('922'), ('923'), ('924'), ('925'), ('926'), ('927'), ('928'), ('929'), ('930'), ('931'), ('932'), ('933'), ('934'), ('935'), ('936'), ('937'), ('938'), ('939'), ('940'), ('941'), ('942'), ('943'), ('944'), ('945'), ('946'), ('947'), ('948'), ('949'), ('950'), ('951'), ('952'), ('953'), ('954'), ('955'), ('956'), ('957'), ('958'), ('959'), ('960'), ('961'), ('962'), ('963'), ('964'), ('965'), ('966'), ('967'), ('968'), ('969'), ('970'), ('971'), ('972'), ('973'), ('974'), ('975'), ('976'), ('977'), ('978'), ('979'), ('980'), ('981'), ('982'), ('983'), ('984'), ('985'), ('986'), ('987'), ('988'), ('989'), ('990'), ('991'), ('992'), ('993'), ('994'), ('995'), ('996'), ('997'), ('998'), ('999'), ('1000'), ('1001')
Msg 10738, Level 15, State 1, Line 1007 The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.
To overcome this limit, either we can switch to a traditional method or wrap multiple rows in a CTE. Using CTE reference with a TVC to insert the data in a table as done in the following query.
USE tempdb; DROP TABLE IF EXISTS Number; CREATE TABLE Number ( Num INT ); WITH NumberData AS ( SELECT Num FROM (VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('10'), ('11'), ('12'), ('13'), ('14'), ('15'), ('16'), ('17'), ('18'), ('19'), ('20'), ('21'), ('22'), ('23'), ('24'), ('25'), ('26'), ('27'), ('28'), ('29'), ('30'), ('31'), ('32'), ('33'), ('34'), ('35'), ('36'), ('37'), ('38'), ('39'), ('40'), ('41'), ('42'), ('43'), ('44'), ('45'), ('46'), ('47'), ('48'), ('49'), ('50'), ('51'), ('52'), ('53'), ('54'), ('55'), ('56'), ('57'), ('58'), ('59'), ('60'), ('61'), ('62'), ('63'), ('64'), ('65'), ('66'), ('67'), ('68'), ('69'), ('70'), ('71'), ('72'), ('73'), ('74'), ('75'), ('76'), ('77'), ('78'), ('79'), ('80'), ('81'), ('82'), ('83'), ('84'), ('85'), ('86'), ('87'), ('88'), ('89'), ('90'), ('91'), ('92'), ('93'), ('94'), ('95'), ('96'), ('97'), ('98'), ('99'), ('100'), ('101'), ('102'), ('103'), ('104'), ('105'), ('106'), ('107'), ('108'), ('109'), ('110'), ('111'), ('112'), ('113'), ('114'), ('115'), ('116'), ('117'), ('118'), ('119'), ('120'), ('121'), ('122'), ('123'), ('124'), ('125'), ('126'), ('127'), ('128'), ('129'), ('130'), ('131'), ('132'), ('133'), ('134'), ('135'), ('136'), ('137'), ('138'), ('139'), ('140'), ('141'), ('142'), ('143'), ('144'), ('145'), ('146'), ('147'), ('148'), ('149'), ('150'), ('151'), ('152'), ('153'), ('154'), ('155'), ('156'), ('157'), ('158'), ('159'), ('160'), ('161'), ('162'), ('163'), ('164'), ('165'), ('166'), ('167'), ('168'), ('169'), ('170'), ('171'), ('172'), ('173'), ('174'), ('175'), ('176'), ('177'), ('178'), ('179'), ('180'), ('181'), ('182'), ('183'), ('184'), ('185'), ('186'), ('187'), ('188'), ('189'), ('190'), ('191'), ('192'), ('193'), ('194'), ('195'), ('196'), ('197'), ('198'), ('199'), ('200'), ('201'), ('202'), ('203'), ('204'), ('205'), ('206'), ('207'), ('208'), ('209'), ('210'), ('211'), ('212'), ('213'), ('214'), ('215'), ('216'), ('217'), ('218'), ('219'), ('220'), ('221'), ('222'), ('223'), ('224'), ('225'), ('226'), ('227'), ('228'), ('229'), ('230'), ('231'), ('232'), ('233'), ('234'), ('235'), ('236'), ('237'), ('238'), ('239'), ('240'), ('241'), ('242'), ('243'), ('244'), ('245'), ('246'), ('247'), ('248'), ('249'), ('250'), ('251'), ('252'), ('253'), ('254'), ('255'), ('256'), ('257'), ('258'), ('259'), ('260'), ('261'), ('262'), ('263'), ('264'), ('265'), ('266'), ('267'), ('268'), ('269'), ('270'), ('271'), ('272'), ('273'), ('274'), ('275'), ('276'), ('277'), ('278'), ('279'), ('280'), ('281'), ('282'), ('283'), ('284'), ('285'), ('286'), ('287'), ('288'), ('289'), ('290'), ('291'), ('292'), ('293'), ('294'), ('295'), ('296'), ('297'), ('298'), ('299'), ('300'), ('301'), ('302'), ('303'), ('304'), ('305'), ('306'), ('307'), ('308'), ('309'), ('310'), ('311'), ('312'), ('313'), ('314'), ('315'), ('316'), ('317'), ('318'), ('319'), ('320'), ('321'), ('322'), ('323'), ('324'), ('325'), ('326'), ('327'), ('328'), ('329'), ('330'), ('331'), ('332'), ('333'), ('334'), ('335'), ('336'), ('337'), ('338'), ('339'), ('340'), ('341'), ('342'), ('343'), ('344'), ('345'), ('346'), ('347'), ('348'), ('349'), ('350'), ('351'), ('352'), ('353'), ('354'), ('355'), ('356'), ('357'), ('358'), ('359'), ('360'), ('361'), ('362'), ('363'), ('364'), ('365'), ('366'), ('367'), ('368'), ('369'), ('370'), ('371'), ('372'), ('373'), ('374'), ('375'), ('376'), ('377'), ('378'), ('379'), ('380'), ('381'), ('382'), ('383'), ('384'), ('385'), ('386'), ('387'), ('388'), ('389'), ('390'), ('391'), ('392'), ('393'), ('394'), ('395'), ('396'), ('397'), ('398'), ('399'), ('400'), ('401'), ('402'), ('403'), ('404'), ('405'), ('406'), ('407'), ('408'), ('409'), ('410'), ('411'), ('412'), ('413'), ('414'), ('415'), ('416'), ('417'), ('418'), ('419'), ('420'), ('421'), ('422'), ('423'), ('424'), ('425'), ('426'), ('427'), ('428'), ('429'), ('430'), ('431'), ('432'), ('433'), ('434'), ('435'), ('436'), ('437'), ('438'), ('439'), ('440'), ('441'), ('442'), ('443'), ('444'), ('445'), ('446'), ('447'), ('448'), ('449'), ('450'), ('451'), ('452'), ('453'), ('454'), ('455'), ('456'), ('457'), ('458'), ('459'), ('460'), ('461'), ('462'), ('463'), ('464'), ('465'), ('466'), ('467'), ('468'), ('469'), ('470'), ('471'), ('472'), ('473'), ('474'), ('475'), ('476'), ('477'), ('478'), ('479'), ('480'), ('481'), ('482'), ('483'), ('484'), ('485'), ('486'), ('487'), ('488'), ('489'), ('490'), ('491'), ('492'), ('493'), ('494'), ('495'), ('496'), ('497'), ('498'), ('499'), ('500'), ('501'), ('502'), ('503'), ('504'), ('505'), ('506'), ('507'), ('508'), ('509'), ('510'), ('511'), ('512'), ('513'), ('514'), ('515'), ('516'), ('517'), ('518'), ('519'), ('520'), ('521'), ('522'), ('523'), ('524'), ('525'), ('526'), ('527'), ('528'), ('529'), ('530'), ('531'), ('532'), ('533'), ('534'), ('535'), ('536'), ('537'), ('538'), ('539'), ('540'), ('541'), ('542'), ('543'), ('544'), ('545'), ('546'), ('547'), ('548'), ('549'), ('550'), ('551'), ('552'), ('553'), ('554'), ('555'), ('556'), ('557'), ('558'), ('559'), ('560'), ('561'), ('562'), ('563'), ('564'), ('565'), ('566'), ('567'), ('568'), ('569'), ('570'), ('571'), ('572'), ('573'), ('574'), ('575'), ('576'), ('577'), ('578'), ('579'), ('580'), ('581'), ('582'), ('583'), ('584'), ('585'), ('586'), ('587'), ('588'), ('589'), ('590'), ('591'), ('592'), ('593'), ('594'), ('595'), ('596'), ('597'), ('598'), ('599'), ('600'), ('601'), ('602'), ('603'), ('604'), ('605'), ('606'), ('607'), ('608'), ('609'), ('610'), ('611'), ('612'), ('613'), ('614'), ('615'), ('616'), ('617'), ('618'), ('619'), ('620'), ('621'), ('622'), ('623'), ('624'), ('625'), ('626'), ('627'), ('628'), ('629'), ('630'), ('631'), ('632'), ('633'), ('634'), ('635'), ('636'), ('637'), ('638'), ('639'), ('640'), ('641'), ('642'), ('643'), ('644'), ('645'), ('646'), ('647'), ('648'), ('649'), ('650'), ('651'), ('652'), ('653'), ('654'), ('655'), ('656'), ('657'), ('658'), ('659'), ('660'), ('661'), ('662'), ('663'), ('664'), ('665'), ('666'), ('667'), ('668'), ('669'), ('670'), ('671'), ('672'), ('673'), ('674'), ('675'), ('676'), ('677'), ('678'), ('679'), ('680'), ('681'), ('682'), ('683'), ('684'), ('685'), ('686'), ('687'), ('688'), ('689'), ('690'), ('691'), ('692'), ('693'), ('694'), ('695'), ('696'), ('697'), ('698'), ('699'), ('700'), ('701'), ('702'), ('703'), ('704'), ('705'), ('706'), ('707'), ('708'), ('709'), ('710'), ('711'), ('712'), ('713'), ('714'), ('715'), ('716'), ('717'), ('718'), ('719'), ('720'), ('721'), ('722'), ('723'), ('724'), ('725'), ('726'), ('727'), ('728'), ('729'), ('730'), ('731'), ('732'), ('733'), ('734'), ('735'), ('736'), ('737'), ('738'), ('739'), ('740'), ('741'), ('742'), ('743'), ('744'), ('745'), ('746'), ('747'), ('748'), ('749'), ('750'), ('751'), ('752'), ('753'), ('754'), ('755'), ('756'), ('757'), ('758'), ('759'), ('760'), ('761'), ('762'), ('763'), ('764'), ('765'), ('766'), ('767'), ('768'), ('769'), ('770'), ('771'), ('772'), ('773'), ('774'), ('775'), ('776'), ('777'), ('778'), ('779'), ('780'), ('781'), ('782'), ('783'), ('784'), ('785'), ('786'), ('787'), ('788'), ('789'), ('790'), ('791'), ('792'), ('793'), ('794'), ('795'), ('796'), ('797'), ('798'), ('799'), ('800'), ('801'), ('802'), ('803'), ('804'), ('805'), ('806'), ('807'), ('808'), ('809'), ('810'), ('811'), ('812'), ('813'), ('814'), ('815'), ('816'), ('817'), ('818'), ('819'), ('820'), ('821'), ('822'), ('823'), ('824'), ('825'), ('826'), ('827'), ('828'), ('829'), ('830'), ('831'), ('832'), ('833'), ('834'), ('835'), ('836'), ('837'), ('838'), ('839'), ('840'), ('841'), ('842'), ('843'), ('844'), ('845'), ('846'), ('847'), ('848'), ('849'), ('850'), ('851'), ('852'), ('853'), ('854'), ('855'), ('856'), ('857'), ('858'), ('859'), ('860'), ('861'), ('862'), ('863'), ('864'), ('865'), ('866'), ('867'), ('868'), ('869'), ('870'), ('871'), ('872'), ('873'), ('874'), ('875'), ('876'), ('877'), ('878'), ('879'), ('880'), ('881'), ('882'), ('883'), ('884'), ('885'), ('886'), ('887'), ('888'), ('889'), ('890'), ('891'), ('892'), ('893'), ('894'), ('895'), ('896'), ('897'), ('898'), ('899'), ('900'), ('901'), ('902'), ('903'), ('904'), ('905'), ('906'), ('907'), ('908'), ('909'), ('910'), ('911'), ('912'), ('913'), ('914'), ('915'), ('916'), ('917'), ('918'), ('919'), ('920'), ('921'), ('922'), ('923'), ('924'), ('925'), ('926'), ('927'), ('928'), ('929'), ('930'), ('931'), ('932'), ('933'), ('934'), ('935'), ('936'), ('937'), ('938'), ('939'), ('940'), ('941'), ('942'), ('943'), ('944'), ('945'), ('946'), ('947'), ('948'), ('949'), ('950'), ('951'), ('952'), ('953'), ('954'), ('955'), ('956'), ('957'), ('958'), ('959'), ('960'), ('961'), ('962'), ('963'), ('964'), ('965'), ('966'), ('967'), ('968'), ('969'), ('970'), ('971'), ('972'), ('973'), ('974'), ('975'), ('976'), ('977'), ('978'), ('979'), ('980'), ('981'), ('982'), ('983'), ('984'), ('985'), ('986'), ('987'), ('988'), ('989'), ('990'), ('991'), ('992'), ('993'), ('994'), ('995'), ('996'), ('997'), ('998'), ('999'), ('1000'), ('1001')) as Number(Num) ) INSERT INTO Number (Num) SELECT Num FROM NumberData;
Summary
As a result, we have learnt to use Table Value Constructor (TVC) in SQL Server. Using TVC generating a result set on the fly, inserting multiple rows in a go becomes simple. TVC can be used along with CTE, Joins, subqueries, merge and other statements. Visit more on Tech-Recipes Database archive post to learn more useful stuff.
 allows to group multiple rows of data in a single DML statement. Before SQL Server 2008, inserting multiple rows to a table){kind=link}