question insert exists - 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

question insert exists

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-25 : 03:54:13

I'm trying to insert selected data from one database to another.
I have 2 tables
strcell PK=stoolguid
luoption PK=loptionid FK=stoolguid

Each stoolguid in the strcell table has many loptionid's. I want to insert in the new database table just the loptionid's for a given stoolguid. As some of the loptionid's will be the same for other stoolguid's then I cannot insert and don't need to insert those loptionid's that are already in the new database table.
The following sql query will correctly insert into the new database table as long as the table is empty. However if i insert another stoolguid into the new database some of the loptionid's are already present therefore the insert does not work. I think I need to use 'Exists'

insert into database2.luoption 
(loptionid,sname,spartner) 
select loptionid,sname,spartner 
from database1.luoption 
where loptionid in 
(select loptionid from strcell where stoolguid = @stoolguid)

Does this make sense?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 04:07:24

something similar to this?

insert	database2.luoption 
	(
		loptionid,
		sname,
		spartner
	) 
select	src.loptionid,
	src.sname,
	src.spartner 
from	database1.dbo.luoption AS src
where	not exists (select * from from strcell AS x where x.stoolguid = @stoolguid)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 04:10:00

how will you decide which stoolguid a particular loptionid should be entered agauinst in cases where it exists for more than 1?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 04:23:03

[code]insert into database2.luoption
(loptionid,sname,spartner)
select loptionid,sname,spartner
from
(
select l.loptionid,s.sname,s.spartner,
row_number() over(partition by l.loptionid order by s.stoolguid) as seq
from database1.luoption l
join strcell s
on l.loptionid =s.loptionid
where s.stoolguid = @stoolguid
)t
where seq=1[/code]

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-25 : 04:30:59

quote:
Originally posted by visakh16

how will you decide which stoolguid a particular loptionid should be entered agauinst in cases where it exists for more than 1?

Firstly thanks for your quick responses. not entirely sure what you mean.
The strcell table primary key is a combination of stoolguid and another lgrid so table in database 1 looks like this: (Lets say database 2 already has stoolguid 1111 and i want to insert 2222.)

Database1
stoolguid...lgrid...loptionid
1111...1...1
1111...2...5
1111...3...30
2222...1...4
2222...2...1
2222...3...5

luoption table
loptionid
1...
4...
5...
30...

Database 2
stoolguid...lgrid...loptionid
1111...1...1
1111...2...5
1111...3...30

luoption table
loptionid
1...
5...
30...

I want to insert stoolguid 2222 which means i just need to insert loptionid 4. The luoption table is just a reference table.

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-25 : 04:44:27

quote:

Originally posted by visakh16

insert into database2.luoption 
(loptionid,sname,spartner) 
select loptionid,sname,spartner
from
(
select l.loptionid,s.sname,s.spartner,
row_number() over(partition by l.loptionid order by s.stoolguid) as seq
from database1.luoption  l
join strcell s
on l.loptionid =s.loptionid 
where s.stoolguid = @stoolguid
)t
where seq=1

Wow, I would never have thought of that. I will give it a go but is there something simpler - maybe my previous post better describes my problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 04:49:40

[code]insert into database2.table
SELECT stoolguid,lgrid,loptionid
FROM database1.table t1
LEFT JOIN database2.table t2
ON t2.stoolguid=t1.stoolguid
LEFT JOIN database2.table t3
ON t3.loptionid=t1.loptionid
WHERE t2.stoolguid IS NULL
AND t3.loptionid IS NULL[/code]

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-25 : 05:05:00

quote:

Originally posted by visakh16

insert into database2.table
SELECT stoolguid,lgrid,loptionid
FROM database1.table t1
LEFT JOIN database2.table t2
ON t2.stoolguid=t1.stoolguid
LEFT JOIN database2.table t3
ON t3.loptionid=t1.loptionid
WHERE t2.stoolguid IS NULL
AND t3.loptionid IS NULL

Hi, thanks for the continued help. after reading this bit of code I don't think it will work.
If you look at my previous post where I am inserting stoolguid 2222 into the database then first I have to insert the loptionid before I insert the 2222 in strcell otherwise I get referential integrity error.
The scenario is that the end user selects which stoolguid to insert into the new database. I was thinking of getting rid of the referential integrity between the 2 tables. Then I could insert the stoolguid 2222 into strcell then simply add all the optionid's. Is this OK?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:07:44

nope. in that case dont drop referential integrity. just include a check ig lopitionid exists and insert it first if not exists. then do above code.

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-25 : 05:33:02

quote:
Originally posted by visakh16

nope. in that case dont drop referential integrity. just include a check ig lopitionid exists and insert it first if not exists.

I think we have crossed lines here because thats what I'm trying to do. I'm trying to find the optionid in the strcell table for a selected stoolguid that is not in the option table. If I find any like in my example optionID 4 then I insert those details from the old database to the new database.

I have 3 lookup tables from strcell which is why i want to do this as simple as possible. Is there a good reason why I shouldn't take referential off? It seems much easier and quicker to delete everything in the option table then insert the stoolguid into the strcell table then insert all the relevent loptionid's.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:40:02

nope. the reason why you shouldnt take referntial integrity is because it has been kept for purpose. taking it will enable anybody to insert invalid values for optionid which doesnt exists in its master table and this will create more problems
so solution should be identify non existing values in beginning and insert them in master before proceeding

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:42:37

then should this be enough to include non existing option ids to table?


insert	database2.luoption 
	(
		loptionid,
		sname,
		spartner
	) 
select	x.loptionid,
	x.sname,
	x.spartner 
from	strcell AS x 
where x.stoolguid = @stoolguid
and 	not exists (select * from database1.luoption where  loptionid=x.loptionid)

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-25 : 06:30:18

quote:

Originally posted by visakh16

then should this be enough to include non existing option ids to table?


insert	database2.luoption 
	(
		loptionid,
		sname,
		spartner
	) 
select	x.loptionid,
	x.sname,
	x.spartner 
from	strcell AS x 
where x.stoolguid = @stoolguid
and 	not exists (select * from database1.luoption where  loptionid=x.loptionid)

well I altered it a little but it helped me big time with a little help from pesos too so a big thanks.

First I get the stoolguid optionid's that are not in the new luoption table. (have to use distinct).
Then I get the option details from the master.
Then I insert them into the new database.
I've tested it well enough and it works cool.


insert database2.luoption 
	(
		loptionid,
		sname,
		spartner
	) 
select loptionid, sname, spartner
from luoption where loptionid in 
(select distinct x.loptionid	
from strcell AS x 
where x.stoolguid = '{9EFB9D20-60B3-11D5-BE08-0080C8FC6E95}'
and not exists (select * from ServerAliasName.sitemanager.dbo.luoption where  loptionid=x.loptionid))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources