SQL Insert Multiple Values with One Query

WEhalt
WEhalt used Ask the Experts™
on
Is there a way to insert multiple rows with one insert?

INSERT INTO #STAT_GROUPS SELECT (@HOSPITAL, @GROUP1) UNION SELECT(@DOCTOR, @GROUP1)

#STAT_GROUPS only has 2 fields
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Assuming your variables are single values...

INSERT INTO #STAT_GROUPS (Column1Name, Column2Name)
VALUES (@HOSPITAL, @GROUP1), (@DOCTOR, @GROUP1)

Commented:
If you have another table with doctors and groups then

insert into #stat_groups
      select Doctor, Group
      from dbo.OtherTable

hth

Mike

Author

Commented:
I do not have a table with the values, I am making one so I can create a cartesion product of options.

I cannot get this to work.  It keeps saying there is an error by the ',' .  All variables are just varchar(50) values.

INSERT INTO #STAT_GROUPS (CLAIM_LOC, CLAIM_PROC_DAYS_GRP)
VALUES (@HOSPITAL, @GROUP1), (@DOCTOR, @GROUP1)
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Commented:
The error is because you can only insert one tuple (row or part of a row) at a time.

what was the problem with

INSERT INTO STAT_GROUPS
SELECT  @HOSPITAL, @GROUP1
UNION
SELECT @DOCTOR, @GROUP1

?

Author

Commented:
Apparently nothing.  I just tried it again and it worked.

Commented:
:)  That's because it's the last day before a holiday and the Gremlins are conspiring against you :)

Have a good weekend

Mike

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial