Insert Into If - 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

Insert Into If

Author

Topic

klau962
Starting Member

8 Posts

Posted - 2009-07-24 : 11:47:40

Hi,

I have 2 tables accts and company, both with a column called ID. The idea of what I'm trying to do is, I want to check accts to see if any values of ID are null, if so replace with some value and insert a row into company, where ID is a primary key. Where I'm having trouble is if I want to run this twice, I can't insert into company because it's a primary key.

I have 2 approaches:
check if there are any nulls in accts, if so, update the nulls and insert into company; if not, do nothing

OR

Update all the nulls to some value x, if ID=x in accts then insert into company

I'm not sure how to code the If statement into the Insert Into, or vice versa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 12:15:58

do you mean this?


insert into company
select distinct isnull(id,somevalue) from acct

klau962
Starting Member

8 Posts

Posted - 2009-07-24 : 12:22:31

quote:
Originally posted by visakh16

do you mean this?

insert into company
select distinct isnull(id,somevalue) from acct

not quite, i don't want to insert all distinct IDs from acct, just 'somevalue', and only if a null value was replaced with 'somevalue' in acct

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 12:25:48

do you mean just check for presence of nulls in acct and if yes, put a value in company?

klau962
Starting Member

8 Posts

Posted - 2009-07-24 : 12:42:52

quote:
Originally posted by visakh16

do you mean just check for presence of nulls in acct and if yes, put a value in company?

yes pretty much... :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:08:33

[code]
insert into company
select 'your value'
FROM(select count(*) as nullcnt from acct where id is null)t
where nullcnt>0
[/code]

klau962
Starting Member

8 Posts

Posted - 2009-07-24 : 14:02:38

quote:

Originally posted by visakh16


insert into company
select 'your value'
FROM(select count(*) as nullcnt from acct where id is null)t
where nullcnt>0

when i try this code, i get the message:
Incorrect syntax near the keyword 'WHERE'

klau962
Starting Member

8 Posts

Posted - 2009-07-24 : 14:54:52

what if i want to insert some text that is not in the table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources