insert values into table with a case statement attached

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Please provide the approriate syntax for inserting values into a table with a condition

CASE as depicted below

SQL

Insert Into Transactions (Name, PERIOD_COUNT, Act_Prime_Amt, Act_Avg_Amt) 
        Values (Master.Name, lnPeriodCount, 
        CASE WHEN (lnPeriodCount <= Master.Act_Prime_Pd, 0, Master.Act_Prime_Amt), 
        CASE WHEN  (lnPeriodCount <= Master.Act_Avg_Pd, 0, Master.Act_Avg_Amt))

Posted 28-Nov-14 14:09pm

Member 10744248

Updated 28-Nov-14 14:29pm

v4

Add a Solution

Comments

PIEBALDconsult 28-Nov-14 20:26pm

Those semi-colons don't belong in there.

Member 10744248 28-Nov-14 20:30pm

Is the syntax right please aside of the semi colon

DamithSL 28-Nov-14 21:49pm

are you insert from another table data?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-11-28T22:55:00

Try this:

SQL

Insert Into Transactions (Name, PERIOD_COUNT, Act_Prime_Amt, Act_Avg_Amt)
        SELECT Master.Name, lnPeriodCount,
        CASE WHEN lnPeriodCount <= Master.Act_Prime_Pd THEN 0 ELSE Master.Act_Prime_Amt END,
        CASE WHEN  lnPeriodCount <= Master.Act_Avg_Pd THEN 0 ELSE Master.Act_Avg_Amt END

For further information, please see: CASE (Transact-SQL)[^]

King Fisher · Answer 2 · 2014-11-28T17:32:00

Solution 1

use SQL INSERT INTO SELECT statement

http://www.w3schools.com/sql/sql_insert_into_select.asp[^]

Eg:

SQL

create table #tbl_Test(id int identity(1,1),name nvarchar(max))
declare @name nvarchar(max) ='';
insert into #tbl_Test(name)select  case when @name ='' then 'Me' else @name end

select *from #tbl_test

Try it :)

Posted 28-Nov-14 17:32pm

King Fisher

Comments

Maciej Los 29-Nov-14 4:56am

+5

King Fisher 29-Nov-14 5:05am

Thank you :)