Insert statement filling data using select

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)

Insert statement filling data using select

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-01-28 : 06:31:46

I have the following insert:


insert into tablepayments(payment_id,inv_id,task_id,paid_amt) select payment_id, inv_id, task_id, inv_amt from tableinvoices where inv_id=@inv_id and one more condition.

Is it possible:

I want to add another condition , before inserting any records to tablepayments, check if a record already exists with the combination of: inv_id and task_id

i don't want to have duplicate records with same inv_id and task_id

thank you very much for the helpful information.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 06:41:03

if not exists (select 1 from from tableinvoices where inv_id=@inv_id and task_id = @task_id)
insert into ..........

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-28 : 06:42:17

declare @cnt int
set @cnt = select 1 from from tableinvoices where inv_id=@inv_id and task_id = @task_id

insert into ..........
select .......... where @cnt = 0

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-28 : 06:42:25

declare @cnt int
select @cnt = count(1) from tableinvoices where inv_id=@inv_id and task_id = @task_id
insert into tableinvoices
select field1,field2.............
where @cnt = 0

Jai Krishna

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-28 : 06:44:19

[code]IF NOT EXISTS (SELECT * FROM tablepayments WHERE inv_id = @inv_id and task_id = @task_id)
insert into tablepayments(payment_id,inv_id,task_id,paid_amt)
select payment_id, inv_id, task_id, inv_amt
from tableinvoices
where inv_id=@inv_id
and task_id = @task_id[/code]

- Lumbago

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-01-28 : 06:50:41

Hello Lumbago,

But i don't have @taskid info. as a parameter to pass, i only have @inv_id which i will pass it as a param.

quote:

Originally posted by Lumbago

IF NOT EXISTS (SELECT * FROM tablepayments WHERE inv_id = @inv_id and task_id = @task_id)
  insert into tablepayments(payment_id,inv_id,task_id,paid_amt) 
  select payment_id, inv_id, task_id, inv_amt 
  from tableinvoices 
  where inv_id=@inv_id
    and task_id = @task_id

- Lumbago

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 06:52:43

quote:
Originally posted by cplusplus

Hello Lumbago,

But i don't have @taskid info. as a parameter to pass, i only have @inv_id which i will pass it as a param.

quote:
Originally posted by Lumbago

IF NOT EXISTS (SELECT * FROM tablepayments WHERE inv_id = @inv_id ~~and task_id = @task_id~~)
insert into tablepayments(payment_id,inv_id,task_id,paid_amt)
select payment_id, inv_id, task_id, inv_amt
from tableinvoices
where inv_id=@inv_id
and task_id = @task_id

- Lumbago

then check only with @inv_id

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-28 : 06:53:40

U have to pass @task_id if u want to have a unique combination of inv_id and task_id

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 07:10:22

quote:
Originally posted by cplusplus

Hello Lumbago,

But i don't have @taskid info. as a parameter to pass, i only have @inv_id which i will pass it as a param.

quote:
Originally posted by Lumbago

IF NOT EXISTS (SELECT * FROM tablepayments WHERE inv_id = @inv_id and task_id = @task_id)
insert into tablepayments(payment_id,inv_id,task_id,paid_amt)
select payment_id, inv_id, task_id, inv_amt
from tableinvoices
where inv_id=@inv_id
and task_id = @task_id

- Lumbago

then where will you get @taskid value to compare and see if it exists?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 07:33:22

[code]INSERT tablepayments
(
payment_id,
inv_id,
task_id,
paid_amt
)
SELECT s.payment_id,
s.inv_id,
s.task_id,
s.inv_amt
FROM tableinvoices AS s
LEFT JOIN tablepayments AS c ON c.inv_id = s.inv_id
AND c.task_id = s.task_id
WHERE s.inv_id = @inv_id
AND c.task_id IS NULL[/code]

E 12°55'05.63"
N 56°04'39.26"

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-01-28 : 07:56:39

Hello Visakh,

we need to get task_id values from tableinvoices to tablepayments.

but before inserting to tablepayments, just want to make sure this task_id(tableinvoices), should not be under tablepayments with the combination of @inv_id.

quote:
Originally posted by visakh16

quote:
Originally posted by cplusplus

Hello Lumbago,

But i don't have @taskid info. as a parameter to pass, i only have @inv_id which i will pass it as a param.

quote:
Originally posted by Lumbago

IF NOT EXISTS (SELECT * FROM tablepayments WHERE inv_id = @inv_id and task_id = @task_id)
insert into tablepayments(payment_id,inv_id,task_id,paid_amt)
select payment_id, inv_id, task_id, inv_amt
from tableinvoices
where inv_id=@inv_id
and task_id = @task_id

- Lumbago

then where will you get @taskid value to compare and see if it exists?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 08:43:12

then it should be


IF NOT EXISTS (SELECT * FROM tablepayments 
JOIN tableinvoices 
ON tableinvoices.task_id=tablepayments.task_id
WHERE tablepayments.inv_id = @inv_id )
  insert into tablepayments(payment_id,inv_id,task_id,paid_amt) 
  select payment_id, inv_id, task_id, inv_amt 
  from tableinvoices 
  where inv_id=@inv_id
    and task_id = @task_id

asgast
Posting Yak Master

149 Posts

Posted - 2009-01-28 : 08:46:46

I always use the solution Peso gave here, main reasons are:
as far as I know NOT EXISTS is much slower then JOINS,
if you insert one row then solution with not exists is acceptable
but with
insert into tablepayments(payment_id,inv_id,task_id,paid_amt)
select payment_id, inv_id, task_id, inv_amt
you may have a situation, when out of ten rows you need to skip 3 and insert 7. With IF NOT EXISTS (SELECT * FROM tablepayments WHERE inv_id = @inv_id and task_id = @task_id) you will skip the whole insert.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources