CODEWITHSUNDEEP
sql-server
Leron
Leron

Reputation: 9876

How to use inner SELECT inside INSERT statement

I have this problem. I have a Currency table which looks like this :

Id|IntKey|Text
1  10     USD
2  20     EUR

A have another table - Accounts where I have Foreign Key CurrencyId. The problem is that when I collect the information for an account the Currency comes to me as string value or the Text column value in my database. However I want to use IntKey to match the CurrencyId so what I want is in my INSERT query to provide the IntKey based on the string valued.

The easy way that I can implement myself is first take the value I need is just to perfrom a separate select like

SELECT IntKey FROM Currency WHERE Text = @param

then execute it, take the value and use it in my INSERT however I would liek to combine it to one query (if possible) I tried this:

INSERT INTO [DB].[dbo].[Accounts] VALUES
(@IBAN, SELECT IntKey FROM [dbo].[Currency] WHERE Text = @param, ...)

and then

SqlCommand command = new SqlCommand(insertNewAccount, connection); command.ExecuteScalar();

But this doesn't work. Is this possible and how can I implement it?

Upvotes: 0

Views: 480

Answers (2)

Francois
Francois

Reputation: 10978

You were close to it.

INSERT INTO [DB].[dbo].[Accounts] 
SELECT @IBAN, @otherValue, @anotherValue, IntKey FROM [dbo].[Currency] 
WHERE Text = @param

No need for VALUES, and SELECT has to be around all columns and variables.

Upvotes: 2

Fka
Fka

Reputation: 6234

Did you try with parenthesises?

INSERT INTO [DB].[dbo].[Accounts] VALUES
(
   @IBAN, 
   (SELECT IntKey FROM [dbo].[Currency] WHERE Text = @param), 
   ...
)

Upvotes: 1

Related Questions