insert into tabel based on values in the table!

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 into tabel based on values in the table!

Author

Topic

martij2
Starting Member

4 Posts

Posted - 2008-05-16 : 11:38:56

Hi i am trying to create an insert statement that will insert rows into a table based on the information in the table already.
the table looks like this

Groupid field1 field2
-1 100 200
-1 100 300
-1 300 500
-1 300 600
-1 400 100

the insert looks like this

INSERT Into table1(groupid,field1,field2)
select -1,@passedvalue,field2
from table1
where field1 = @passedvalue1

assume @passedvalue = 700, @passwedvalue1 = 100
Now this is fine however i cannot have a duplicate key (key is comibantion of all 3 fields) thus the first time this runs it works however if it runs again it fails - how can i change the where clause to ignore rows that already exist?
eg if @passedvalue = 300 and passedvalue1 = 500

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 11:45:37

i thinkk seeing your example you meant field2=@passedvalue1 in last line. Try this out anyways:-

INSERT Into table1(groupid,field1,field2)
select -1,@passedvalue,field2
from table1 t1
left join table1 t2
on t2.field2=t1.field2
and t2.field1=@passedvalue
and t2.groupid=-1
where t1.field2 = @passedvalue1
and t2.groupid is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources