[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

INSERT Procedure

Juan Puebla

3/24/2007 11:37:00 AM

Hi,

I'm trying to insert a new row (INSERT INTO) with a SELECT clause. I made
this example to make it clear:

@Name NVarchar(100),
@CountryName Nvarchar(100)

INSERT INTO Clients (Name, IdCountry)
VALUES (@Name, (SELECT IdCountry FROM Countries WHERE CountryName =
@CountryName))

I know that I can use a SELECT clause instead of VALUES. But I don't know to
use SELECT clause inside VALUES.

Thanks

Juan Puebla
Barcelona
3 Answers

David Portas

3/24/2007 11:45:00 AM

0

On 24 Mar, 11:37, Juan Puebla <JuanPue...@discussions.microsoft.com>
wrote:
> Hi,
>
> I'm trying to insert a new row (INSERT INTO) with a SELECT clause. I made
> this example to make it clear:
>
> @Name NVarchar(100),
> @CountryName Nvarchar(100)
>
> INSERT INTO Clients (Name, IdCountry)
> VALUES (@Name, (SELECT IdCountry FROM Countries WHERE CountryName =
> @CountryName))
>
> I know that I can use a SELECT clause instead of VALUES. But I don't know to
> use SELECT clause inside VALUES.
>
> Thanks
>
> Juan Puebla
> Barcelona

You can't do that. Instead you'll have to use INSERT... SELECT.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

--CELKO--

3/24/2007 5:08:00 PM

0

>> I know that I can use a SELECT clause instead of VALUES. But I don't know to use SELECT clause inside VALUES. <<

What you have written is valid in Standard SQL, if the subquery
expression is a scalar value or empty. If the results are empty, it
is converted into a NULL. Unfortunately, SQL Server does not support
all of the features of the VALUES clause.

Jeffrey Williams

3/25/2007 4:31:00 PM

0

To use VALUES:

@Name NVarchar(100),
@CountryName Nvarchar(100)
@CountryId int

SET @CountryId = (SELECT IdCountry FROM Countries WHERE CountryName = @CountryName)

INSERT INTO Clients (Name, IdCountry)
VALUES (@Name, @CountryId)

To use SELECT:

@Name NVarchar(100),
@CountryName Nvarchar(100)

INSERT INTO Clients (Name, IdCountry)
SELECT @Name, IdCountry FROM Countries WHERE CountryName = @CountryName)


Juan Puebla wrote:
> Hi,
>
> I'm trying to insert a new row (INSERT INTO) with a SELECT clause. I made
> this example to make it clear:
>
> @Name NVarchar(100),
> @CountryName Nvarchar(100)
>
> INSERT INTO Clients (Name, IdCountry)
> VALUES (@Name, (SELECT IdCountry FROM Countries WHERE CountryName =
> @CountryName))
>
> I know that I can use a SELECT clause instead of VALUES. But I don't know to
> use SELECT clause inside VALUES.
>
> Thanks
>
> Juan Puebla
> Barcelona
>