sub query insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sub query insert

Author

Topic

ace333
Starting Member

11 Posts

Posted - 2005-09-20 : 03:54:29

use Dacari
go

IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.InsITRRComments
IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.InsITRRComments >>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.InsITRRComments >>'
END
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.InsITRRComments

@CommentId int,
@DacType varchar(25),
@DacTypeId int,
@TypeOfComment varchar(50),
@Comments varchar(4000),
@RiskId_External_ID varchar(16),
@UserId varchar(25),
@FullUserName varchar(50),
@AmendedDateTime datetime,
@ProcessedFlag char(1)

AS
BEGIN
/*************************************************************************
*
* Name: InsITRRComments
*
* Description: Insert new Term Type item.
*
* Inputs:
* @CommentId
* @DacType
* @DacTypeId
* @TypeOfComment
* @Comments
* @RiskId_External_ID
* @UserId
* @FullUserName
* @AmendedDateTime
* @ProcessedFlag
*
* Outputs: None
*
*
* Notes:
*
* History:
* Date Author Changes
* ---------- -------------- -------------------------------------------
* 16 Sept 05 xxxxxxxxxxxxxx Original
*************************************************************************/

---------------------------------------------------------------------------

INSERT INTO ITRRComments
VALUES (
@DacType,
@DacTypeId,
@TypeOfComment,
@Comments,
@RiskId_External_ID,
@UserId,
(select FullUserName from SecurityUsers where UserId=@UserId)
@AmendedDateTime,
'N'
)
END

GO

IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.InsITRRComments>>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.InsITRRComments>>>'
go

GRANT EXECUTE ON dbo.InsITRRComments TO AppWrite
go

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Subqueries are not allowed in this context. Only scalar expressions are allowed

This is the error that I'm getting, how do I get round the issue of a sub query problem

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-20 : 03:58:18

you just need to remove the ( )

follow this format:

insert into tablename(field1,field2...)
select value1,value2,... from sourceTable
where....

--------------------
keeping it simple...

ace333
Starting Member

11 Posts

Posted - 2005-09-20 : 04:07:18

Have tried this now but get the following error...

use Dacari
go

IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.InsITRRComments
IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.InsITRRComments >>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.InsITRRComments >>'
END
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.InsITRRComments

@CommentId int,
@DacType varchar(25),
@DacTypeId int,
@TypeOfComment varchar(50),
@Comments varchar(4000),
@RiskId_External_ID varchar(16),
@UserId varchar(25),
@FullUserName varchar(50),
@AmendedDateTime datetime,
@ProcessedFlag char(1)

AS
BEGIN
/*************************************************************************
*
* Name: InsITRRComments
*
* Description: Insert new Term Type item.
*
* Inputs:
* @CommentId
* @DacType
* @DacTypeId
* @TypeOfComment
* @Comments
* @RiskId_External_ID
* @UserId
* @FullUserName
* @AmendedDateTime
* @ProcessedFlag
*
* Outputs: None
*
*
* Notes:
*
* History:
* Date Author Changes
* ---------- -------------- -------------------------------------------
* 16 Sept 05 Noel Nicholson Original
*************************************************************************/

---------------------------------------------------------------------------

INSERT INTO ITRRComments
VALUES (
@DacType,
@DacTypeId,
@TypeOfComment,
@Comments,
@RiskId_External_ID,
@UserId,
select FullUserName from SecurityUsers where UserId=@UserId,
@AmendedDateTime,
'N'
)
END

GO

IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.InsITRRComments>>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.InsITRRComments>>>'
go

GRANT EXECUTE ON dbo.InsITRRComments TO AppWrite
go

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Incorrect syntax near the keyword 'select'
Line 57: Incorrect syntax near ','.
Invalid object name 'dbo.InsITRRComments'.

I think its something to do with the fact that the rest of the values being inserted are scaler values.....
is there a way i can assign the select statement to a scaler value

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-20 : 04:34:45

just follow the format i provided, here's to push you into the right direction...

INSERT INTO ITRRComments(enumerate your fields here)
select [fields you needd here correspoding to the fields above] from SecurityUsers where UserId=@UserId

ok, again...

enumerate the fields where you're going to save the data,
for the select, enumerate the variables or fields from securityusers corresponding to the fields in itrrcomments

try to understand the problem and you'll get the hang of it...

--------------------
keeping it simple...

ace333
Starting Member

11 Posts

Posted - 2005-09-20 : 04:40:30

all i need is one value from the securityusers table, and what u mean by enumerate...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-20 : 04:44:42

enumerate = list down?

for security users, have one variable recieve that value then...

like

select @field1=field1 from table1

quote:
Originally posted by ace333

all i need is one value from the securityusers table, and what u mean by enumerate...

--------------------
keeping it simple...

ace333
Starting Member

11 Posts

Posted - 2005-09-20 : 04:47:24

use Dacari
go

IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.InsITRRComments
IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.InsITRRComments >>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.InsITRRComments >>'
END
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.InsITRRComments

@CommentId int,
@DacType varchar(25),
@DacTypeId int,
@TypeOfComment varchar(50),
@Comments varchar(4000),
@RiskId_External_ID varchar(16),
@UserId varchar(25),
/* @FullUserName varchar(50),*/
@AmendedDateTime datetime,
@ProcessedFlag char(1)

AS
BEGIN
/*************************************************************************
*
* Name: InsITRRComments
*
* Description: Insert new Term Type item.
*
* Inputs:
* @CommentId
* @DacType
* @DacTypeId
* @TypeOfComment
* @Comments
* @RiskId_External_ID
* @UserId
* @FullUserName
* @AmendedDateTime
* @ProcessedFlag
*
* Outputs: None
*
*
* Notes:
*
* History:
* Date Author Changes
* ---------- -------------- -------------------------------------------
* 16 Sept 05 Noel Nicholson Original
*************************************************************************/

---------------------------------------------------------------------------
DECLARE @FullUserName varchar(50)
select
@FullUserName = FullUserName
from SecurityUsers
where UserId=@UserId

INSERT INTO ITRRComments
VALUES (
@DacType,
@DacTypeId,
@TypeOfComment,
@Comments,
@RiskId_External_ID,
@UserId,
@FullUserName,
@AmendedDateTime,
'N'
)
END

GO

IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.InsITRRComments>>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.InsITRRComments>>>'
go

/*GRANT EXECUTE ON dbo.InsITRRComments TO AppWrite*/
go

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

this is what i have done now
Invalid column name 'FullUserName' its similar to another example i was looking at, but it wont work

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-20 : 04:55:22

you cannot insert into variables and expect them to be inserted into the fields of a table, can you just use the format i provided and replace those fields that needs replacing?

if you insist on this format:

select @fullusername=FullUserName from SecurityUsers where UserId=@UserId

INSERT INTO ITRRComments
VALUES (
@DacType,
@DacTypeId,
@TypeOfComment,
@Comments,
@RiskId_External_ID,
@UserId, @fullusername,
@AmendedDateTime,
'N'
)

--------------------
keeping it simple...

ace333
Starting Member

11 Posts

Posted - 2005-09-20 : 04:59:56

if i understood what u wrote i would have tried it already !

ace333
Starting Member

11 Posts

Posted - 2005-09-20 : 05:35:42

code above works, my sql that is, thanks every one

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources