Insert with a where condition

Hi, sql 2005

I have an insert statement that is ignoring the where condition. That is, I 
want to insert records when they do not already exist in the destination 
table.

INSERT INTO dbo.tblmnuGroupPerm
	(
		gId
		,mtfID
		,...
	)
SELECT 
	@gID
	,mtfID
	,...
FROM dbo.locmnuTabFunction AS ltf
WHERE
	ltf.mtfID NOT IN
		(
			SELECT gp.mtfID
			FROM dbo.tblmnuGroupPerm AS gp
			WHERE
				gp.gId=@gID
				AND gp.Deleted=0
		)



Any ideas or recommendations appreciated :-)

Many thanks,
Jonathan
0
Utf
1/28/2010 3:25:01 AM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
1068 Views

Similar Articles

[PageSpeed] 47

It's OK... <oops "redFace">I did not correctly select the correct database 
from the list of available databases</oops>

"Jonathan" wrote:

> Hi, sql 2005
> 
> I have an insert statement that is ignoring the where condition. That is, I 
> want to insert records when they do not already exist in the destination 
> table.
> 
> INSERT INTO dbo.tblmnuGroupPerm
> 	(
> 		gId
> 		,mtfID
> 		,...
> 	)
> SELECT 
> 	@gID
> 	,mtfID
> 	,...
> FROM dbo.locmnuTabFunction AS ltf
> WHERE
> 	ltf.mtfID NOT IN
> 		(
> 			SELECT gp.mtfID
> 			FROM dbo.tblmnuGroupPerm AS gp
> 			WHERE
> 				gp.gId=@gID
> 				AND gp.Deleted=0
> 		)
> 
> 
> 
> Any ideas or recommendations appreciated :-)
> 
> Many thanks,
> Jonathan
0
Utf
1/28/2010 3:31:01 AM
If it is possible that column "mtfID" has NULLs then it is better to use the NOT EXISTS predicate instead of NOT IN:

....
FROM dbo.locmnuTabFunction AS ltf
WHERE NOT EXISTS(SELECT *
                  FROM dbo.tblmnuGroupPerm AS gp
                  WHERE ltf.mtfID = gp.mtfID
                    AND gp.gId = @gID
                    AND gp.Deleted = 0
                  );

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
1/28/2010 3:42:36 AM
>> I want to insert records [sic: rows are not records] when they do not already exist in the destination table. <<

1) That is what a MERGE statement does. Read about it; Microsoft has
some nice extensions to the ANSI/ISO Standards.

2) Rows are not records. Totally different concepts. You need to add a
book on RDBMS fundamentals to your list.

3) Destination table? Talking in terms of moving data from physical
place to physical place (used to be mag tapes in the 1960's) makes
sense in a procdural, record oriented language.  But SQL is a
declarativ, set-oriented language.  See #2.

0
CELKO
1/30/2010 5:47:37 PM
This is just pedantic - he clearly knows what he is talking about, so give 
the books a rest.  Destination table is clearly a valid semantic when 
discussing an _insert_ operation regardless of the physical storage.

"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:fa7c5fa9-6133-4040-985c-b26476eab793@c4g2000yqa.googlegroups.com...
>>> I want to insert records [sic: rows are not records] when they do not 
>>> already exist in the destination table. <<
>
> 1) That is what a MERGE statement does. Read about it; Microsoft has
> some nice extensions to the ANSI/ISO Standards.
>
> 2) Rows are not records. Totally different concepts. You need to add a
> book on RDBMS fundamentals to your list.
>
> 3) Destination table? Talking in terms of moving data from physical
> place to physical place (used to be mag tapes in the 1960's) makes
> sense in a procdural, record oriented language.  But SQL is a
> declarativ, set-oriented language.  See #2.
> 
0
m
2/4/2010 2:50:28 AM
Reply:

Similar Artilces:

How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman Numerlas in my document? "ckj" <ckj@discussions.microsoft.com> wrote in message news:6CF0C580-BA6E-463D-B4FD-E6A6FDD9E814@microsoft.com... > New to Word 2007 would someone please explain to me how to insert Roman > Numerlas in my document? Just type them... I II III IV V VI VII VIII IX X XI etc etc. Type a number e.g. 21, select it and run the following macro Dim oRng As Range Set oRng = Selection.Range oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...

conditional formatting
I am trying to evaluate publisher to see what limitations there are for printing a smal booklet? How can I turn images on and off or assign an image depending on a data field on a merge without coding VB? Is it possible or do I have resort to writting code? I would like to dynamically change images on a template and also turn on and off. What is the best way possible to do this? Thanks, AL AL_LONGO <ALLONGO@discussions.microsoft.com> was very recently heard to utter: > How can I turn images on and off or assign an image depending on a > data field on a merge without coding...

Insert trigger
Looking for some advice on SQL 2005. I have a table that will usually be populated by an SSIS package. I want to set the "loaddate" column to the current time after a record is inserted. Should i do this via trigger or should i just build a step in the SSIS package to update the column after the file loads? If trigger is the way to go, what is the syntax to create the after insert trigger? Thanks in advance. You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date assignment without any need for coding. -...

inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex- "40:15" is there a way to make it more like this...40hrs,15mins -- Message posted from http://www.ExcelForum.com Use a custom format hh"hrs",mm"mins" -- Regards, Peo Sjoblom "-Brian-H- >" <<Brian-H-.110wgs@excelforum-nospam.com> wrote in message news:Brian-H-.110wgs@excelforum-nospam.com... > I have a cell in my time card that displays total weekly time -ex- > "40:15" is there a way to make it more like this...40hrs,15mins ? > > > ...

Automatic conditional formatting from a list
Hi All I need a macro that colours cells if the product name in column A on it's row matches any in the product list. There is a list of product names in C17:End(xlUp).Row, that have different backgrounds applied to each cell in the list. The macro looks at Column A in the worksheet & compares it to the product name list. Where it finds a product name match, it goes across to columns I:BJ (on the matched row) and applies the product name's background colour to any "active cells" (i.e. value >0). The following code does this but it's applies...

conditional formulating
Hi, like to know if there's other ways besides validating the data changes by colours for conditional formating. Looking for audio validation..ie having the spreadsheet alerting me by making a sound when there's changes in the data. Having pop up alerts will do. THe main purpose is that I do not want to keep on monitoring the spreadsheet all the time to capture the changes in data. The data are linked real time to websites, ie Foreign exchange rates. use msgbox and it'll pop up if you are running a macro which goe through the data. really need more info to be more help :...

Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options" On making new-email with Attachment-File(s), ** File Menu | Insert | File >>> (Brows and select File to insert ) then we can see the button "Insert", and write side Drop Down Arrow lower-right side of Dialog Box; If it clicked, we can see three options as follows: ** Insert Insert as text Insert as Attachment I can not recognize/understand the difference between "Insert" and "Insert as Attachment" *** What is the difference between Insert and Insert As Attachment ? I would appreciate y...

insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then save it was as a web page, but I want to keep the excel part interactive. Any ideas? ...

Inserting Hyperlinks in a Protected Sheet
Hi I run Excel 2000 and I have a protected worksheet that I share wit users in my organisation. I want to allow the insertion of a hyperlin to a specific file type within a specified directory on our server. 3 Questions: 1.Protection on disables the insert hyperlink command. Can this b overcome with worksheet activate code? 2.Can I limit the types of files (preferably by requiring the file t meet a mask format eg "z-*.xls")? 3.Can I limit the directory that can be linked, by referring to pathname stored in a cell on the active sheet? Would appreciate your suggestions. Thanks S...

difficulty with conditional formatting
I have a spreadsheet that gets updated monthly and looks like the example below Jan Feb march April May June 21,000 23,500 22,224 22111 20,111 25,000 I've asked this question before and have gotten great tips but each month when refesh the pivot table it looses all the conditional formatting and I start over again. How do I get the pivot table to keep it's formatting when refresehd? The conditional formatting needed is if #'s go down for three consecutive months in a row they are highlighted in red. I need the first...

Insert with a where condition
Hi, sql 2005 I have an insert statement that is ignoring the where condition. That is, I want to insert records when they do not already exist in the destination table. INSERT INTO dbo.tblmnuGroupPerm ( gId ,mtfID ,... ) SELECT @gID ,mtfID ,... FROM dbo.locmnuTabFunction AS ltf WHERE ltf.mtfID NOT IN ( SELECT gp.mtfID FROM dbo.tblmnuGroupPerm AS gp WHERE gp.gId=@gID AND gp.Deleted=0 ) Any ideas or recommendations appreciated :-) Many thanks, Jonathan It's OK... <oops "redFace">I did not correctly se...

Conditional Format Percentage
Hi, How can I conditionally format a cell based on the percentage value of another cell rather than the value of its own cell?. All values ar Example, I wish to format "A1" based on the value of B1. If B1 = -5% then A1 would colour Amber, if B1 = -10% then A1 would colour Red, if B1 => 0 then A1 would go Green. Three format to apply - (1) B1 => 0% [Green] (2) B1 <= minus 9.99% [Red] (3) B1 between minus 0.01% & -9.99% [Amber] Can this be done, possibly in one formula for conditional formatting? Thanks, Mickey In the Conditional Formatting dialog, change &quo...

Insert
I want to overtype in a Publisher text box. I find I can only insert. The "insert" key doesn't do anything. ...

Conditional Formating, contain space
Hi All, How do I HIGHLIGHT a block of data that CONTAIN space?? For an Example, COLOUMN A1-A1000 contains TEXT data. I would like to highlight those cells that contains data. Thanks in advance. New Rule>Use a formula. =NOT(ISBLANK(cellref)) Gord Dibben MS Excel MVP On Wed, 3 Feb 2010 15:51:01 -0800, apache007 <apache007@discussions.microsoft.com> wrote: >Hi All, > >How do I HIGHLIGHT a block of data that CONTAIN space?? > >For an Example, COLOUMN A1-A1000 contains TEXT data. >I would like to highlight those cells that contains data....

How do you insert page numbers larger than 1000?
I have my purchase orders set up as a Publisher document. When our organization upgraded from Publisher 2000 to Publisher 2002, the new version set parameters on the page numbers. This was one of those things that worked just fine in the previous version... Does anyone know how to turn it off or change it? Hi mregen (mregen@discussions.microsoft.com), in the newsgroups you posted: || I have my purchase orders set up as a Publisher document. When our || organization upgraded from Publisher 2000 to Publisher 2002, the new || version set parameters on the page numbers. This was one of those...

how to insert data in a table
Hi Exprets; I am creating an access database in which I want to insert data in already created table. Kindly help. Regards, Vikky Vikky <love.excel@gmail.com> wrote in news:1194124711.012302.269990 @e34g2000pro.googlegroups.com: > Hi Exprets; > > I am creating an access database in which I want to insert data in > already created table. > > Kindly help. > > Regards, > > Vikky > Data from where? Do you want to import it from excel, from a text file, copy it from another table or type it in manually? -- Bob Quintal PA is y I've altere...

Get a percentage based on multiple conditions
I would like to determine a percentage in Excel that is based on multiple conditions. This is for a serious a products on different displays and the quantity on those displays. I need to know out of all the stores surveyed, what percentage of those stores had a lobby display with more than 150 cases. If I had a small set of data I'm sure I could just pivot the data a few ways and be done, but I have over 50,000 rows. See the sample set of data below for more detail. Store Display Product Qty ------- -------------- -------------- ---------- 123 Lobby Soda Cans 50 123 Aisle Soda Cans...

insert downloads into power point
i downloaded an application called "BioDigital Simulator" of an animated cleft lip/palate surgery and need it put into my power point presentation, but can't fiugre out how to do so.... very frustrating... What kind of file is this application? Is it a video? If so, what kind? MPEG? AVI? MOV? Or is it an EXE file? Something else? Which version of PPT are you using? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover...

Insert dataset to another database
I'm posting this to this group also since it pertains to queries, primarily. Hello, Using VB6/ADO, I'm thinking I can create a recordset and insert it into another table in a different Jet database, all within the execution of a single query. But, I can't seem to get it to work, even in an experiment in Access 2K. My sql in Access 2K: SELECT D.lorder as Ord, A.Lorder FROM [;Database = C:\MyDocuments\Acc2K\Wrk.mdb].OrdersData as A INNER JOIN [;Database = C:\Access\Work\Sales06.mdb].Detl1 as D On D.Lorder = a.lorder WHERE ((D.fg)= 'MXX-NC' Or (D.fg)= 'MXX.NC')...

Insert a blank row
Hi, I need some help to insert a blank row in a range where column A has a series of dates. There will be several of the same dates and I need to both sort the dates and then insert the blank row at the end of each sequence. In the blank row I need to total figures that will be in columns B through to G. Thanks, Jim S Hi Jim maybe an easier solution 1. Sort your data (use 'Data - Sort', e.g., column A ascending) 2. Use 'Data - Subtotals' This will insert a row after each date and calculate subtotals automatically for you HTH Frank anon wrote: > Hi, > I need some...

help with conditional formula for a group of values
I need help with a conditional formula. Lets pretend A1 has a value of 15 and in the A2 cell I wish to have a conditional formula that states if the A1 value is greater than > 10 and less than < 20 - if this is true (which it is) the value will be "dog." If A1 has a value of 21 then this falls in the "cat" category i need to add another condition so that if the value in A1 is greater than >20.1 and less than <28.99 if this is true than the value input for A2 should read "cat." All false inputs should read "0" so if the A1 cell has ...

Another Conditional formatting question
Hi all, date box, I want it to be red when it is older than a year. I have this set in my CF condition: Field value is Greater Than Now()-365 (red,bold) Well, it is red bold anyways. when i change the date to something LESS then a year old, it flashes normal quickly, but then goes back to red/bold. Thanks in advance! On Mon, 15 Feb 2010 09:58:02 -0800, Steph wrote: > Hi all, date box, I want it to be red when it is older than a year. > > I have this set in my CF condition: > > Field value is Greater Than Now()-365 (red,bold) > > Wel...

conditional page break
I have a report detail section with several "can grow" text boxes. As such the detail section can vary from being able to fit two to a page through one record filling most of a page to one record flowing over into a second page. I'd like to put a conditional page break in where the top and bottom of the detail section are not on the same page. I am considering using two page number boxes at top and bottom of the section with the isvisible property of the page break but wondered if there is any other easier method thanks Richard Richard G wrote: >I have a report detail ...

How do I insert a letter into an existing word document?
I am working on a large document that I need to add several letters that are on our letterhead. If I cut and paste the letter, the letterhead header becomes skewed. This is just one issue I'm having with the document! Also need to know how to merge 3 separate documents together once I insert the letters that I need! Help! What do you mean by "skewed"? To preserve the data from the header of the document, you will need to insert it into a Section of its own separated from the rest of the document by Next page Section breaks before and after it with the headers...

How do I insert backround into my picture inserts?
Could you please elaborate on your question, and give us details such as your version of Publisher. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. "TAMOS" <TAMOS@discussions.microsoft.com> wrote in message news:A1B3ABEC-F208-4A23-B695-9C0EEFBF649F@microsoft.com... > How to Compose a Good Newsgroup Post http://dts-l.org/goodpost.htm How to Act Smart on Usenet http://www.catb.org/~esr/faqs/smart-questions.html Getting Your Post Noticed - and Answered http:...