INSERT to a table using SELECT and VALUES at the same time

Question

how can I insert to a table using values in different way? preferably no temp table. below is my stored procedure code, but i get errors on insert

CREATE PROCEDURE setBARS -- Add the parameters for the stored procedure here @BUSINESSAREANAME nvarchar(50), @STAFFNAME nvarchar(50), @ROLENAME nvarchar(50), @BARSSTARTDATE date, @BARSENDDATE date AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here WITH t1 (BUSINESSAREAID) AS (SELECT BUSINESSAREAID FROM BUSINESSAREA WHERE BUSINESSAREANAME = @BUSINESSAREANAME), t2 (STAFFID) AS (SELECT STAFFID FROM STAFF WHERE STAFFNAME = @STAFFNAME), t3 (ROLEID) AS (SELECT ROLEID FROM ROLE WHERE ROLENAME = @ROLENAME) INSERT INTO BARS ([BUSINESSAREAID],[STAFFID],[ROLEID],[BARSSTARTDATE],[BARSENDDATE]) VALUES ((SELECT t1.BUSINESSAREAID, t2.STAFFID, t3.ROLEID FROM t1,t2,t3), @BARSSTARTDATE, @BARSENDDATE) END GO

insert into t1 (c1, c2...) select cx, literal... from ...

jarlh
– jarlh

2018-01-25 13:29:04 +00:00
Commented Jan 25, 2018 at 13:29 — jarlh
– jarlh, Commented Jan 25, 2018 at 13:29
Please add the error(s).

David Rushton
– David Rushton

2018-01-25 13:55:29 +00:00
Commented Jan 25, 2018 at 13:55 — David Rushton
– David Rushton, Commented Jan 25, 2018 at 13:55

Onkel Toob · Accepted Answer · 2018-01-25 13:59:43Z

You should be able to include the values directly in the SELECT statement:

INSERT INTO [BARS] ( [BUSINESSAREAID] , [STAFFID] , [ROLEID] , [BARSSTARTDATE] , [BARSENDDATE] ) SELECT [t1].[BUSINESSAREAID] , [t2].[STAFFID] , [t3].[ROLEID] , @BARSSTARTDATE , @BARSENDDATE FROM [t1] , [t2] , [t3];

Ven · Accepted Answer · 2018-01-25 13:43:25Z

0

Try This

insert into bars([BUSINESSAREAID],[STAFFID],[ROLEID],[BARSSTARTDATE][BARSENDDATE]) SELECT t1.BUSINESSAREAID, t2.STAFFID, t3.ROLEID FROM t1,t2,t3, @BARSSTARTDATE, @BARSENDDATE

edited Jan 25, 2018 at 13:43

Ven

2,0241 gold badge15 silver badges28 bronze badges

answered Jan 25, 2018 at 13:31

Jasmynn Flores

13 bronze badges

1 Comment

Sebastian Over a year ago

no it does not work. the error :the select list for the INSERT statement contains fewer items than the insert list.

Jasmynn Flores · Accepted Answer · 2018-01-25 14:08:02Z

why not put all the values on a variable for example;

declare @val1 set @val1 = (select top 1 businessareaid from t1)

declare @val2 set @val1 = (select top 1 staffid from t2)

insert into bars (val1,val2) select @val1 , @val2

Collectives™ on Stack Overflow

INSERT to a table using SELECT and VALUES at the same time

3 Answers 3

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Related