Can we use CASE to 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

SQL Server 2005 Forums

Transact-SQL (2005)

Can we use CASE to INSERT ?

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-13 : 01:38:17

Hi,
I have to insert some hard coded values to a table.
the columns are of type int so i want to substitue the string with 1 or 2.

Ex. Insert into TAB(col1,col2)
values( 'A', case when 'Active' then 1
when 'Inactive' then 2
end)

is this allowed?

I tried this but getting this error:
An expression of non-boolean type specified in a context where a condition is expected, near 'then'.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-13 : 01:45:15

The syntax is not correct.

Sample example to start with:

Declare @Col1 varchar(50)

Set @Col1 ='Active'

Insert into TAB(col1,col2)
select 'A', case @Col1 When 'Active' then 1
when 'InActive' then 2
end

Alternate way:

Insert into TAB(col1,col2)
select 'A', case When @Col1 = 'Active' then 1
when @Col1 = 'InActive' then 2
end

Regards,
Bohra

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-13 : 01:46:26

You can't use CASE without a SELECT statement.
And in hard coded values there can't be a reason to do that.

Maybe you can give a better example?

No, you're never too old to Yak'n'Roll if you're too young to die.

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-13 : 01:58:05

Now i have hardcoded values for all the columns. this is only for sampling. but when we get the actual data from live db, there may be thousands of records...
this is from a remote db i am inserting.

for instance, there is a Industry column for which they have values : BPO,Construction,Port,Manufacturing and so on.

Now i need to store them as int. (0 thru 3 in this case). like if bpo then 0 like that....

how to go about this?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-13 : 02:04:58

Try this example if it help you :

CREATE TABLE SourceTable
(Col1 varchar(50))

CREATE TABLE TargetTable
(Col1 Int)

Insert into SourceTable
Select 'BPO' union
Select 'Construction' union
Select 'Port' union
Select 'Manufacturing'

Insert into TargetTable
Select Case Col1 When 'BPO' then 0
When 'Construction' then 1
When 'Port' then 2
When 'Manufacturing' then 3
End
From SourceTable

Select * from TargetTable

Regards,
Bohra

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-13 : 02:22:33

Hi Bohra,
Thanks for the reply. Anyway i have now hardcoded.
next thing is to put it to excel now.
the client has given data in excel and i need to send a sample insert stmnt. now how can i put my insert stmnt in excel so that he can run it?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-14 : 01:48:39

One way of doing it is to use Macros of Excel.

Bohra
I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources