How To Use Table Value Constructor In SQL Server

Posted August 24, 2019 by Vishwanath Dalvi in Database, SQL Server

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;   


How To Use Table Value Constructor In SQL Server

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);


How To Use Table Value Constructor In SQL Server_1

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


How To Use Table Value Constructor In SQL Server_2

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;


How To Use Table Value Constructor In SQL Server_4

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);


How To Use Table Value Constructor In SQL Server_6

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.

How To Use Table Value Constructor In SQL Server_7

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.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply