July 16, 2011 at 5:46 pm
Hi All,
I'm trying to do something like this:
INSERT tblArchive
(Col1, Col2, Col3)
OUTPUT
INSERTED.Col1, INSERTED.Col2, INSERTED.Col3, ID As ArchiveID
INTO tblProdcution
WHERE INSERTED.Col3 <> 0
SELECT Col1, Col2, Col3
FROM tblImport
IOW, I need all the records from the Import Table (tblImport) to go into the Archive Table (tblArchive), but only the records where Col3 <> 0 to go into the Production Table (tblProd) to keep from bloating my production tables with 0 valued rows, of which there are many.
Any suggestions will be greatly appreciated!
TIA!
July 16, 2011 at 7:41 pm
Why are you using the naming convention "tbl" as a prefix?
I don't know how many records you have in your table table you may want to reconsider the use of "<> ="
You may want to consider another option to achieve your objective.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 16, 2011 at 7:44 pm
That's not how the OUTPUT clause works: http://technet.microsoft.com/en-us/library/ms177564.aspx
To achieve the behavior I see a couple options:
Option 1: issue two inserts
- Insert 1: insert into tblProduction WHERE col3 <> 0 and also into tblArchive using the OUTPUT clause
- Insert 2: insert into tblArchive WHERE col3 = 0
Option 2: create an INSTEAD OF INSERT trigger on tblProduction that only inserts rows into the base table WHERE col3 <> 0. In this case you'll only need one INSERT INTO query for tblProduction. It will look exactly like you have it now except you'll remove the WHERE Col3 <> 0 clause and rely on the trigger to handle the filtering. EDIT: this is not a viable option...forgot about the trigger limitation with OUTPUT
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 16, 2011 at 9:30 pm
Thanks for the replies! And only 3 seconds apart! Amazing! 🙂
@ Corgi: Because it's an example.
I don't know how many records I have in my table table either. LOL And I can understand not using those operands with strings, but an int? IS NOT that the best practice? Maybe "IS NOT LIKE Convert(varchar(10), 0) + '%'" would be more efficient? 😉 But at your urging I did reconsider it, for a moment.
As my girlfriend will tell you, if you are going to answer a question, with a question, expect sarcasm. LOL All kidding aside, I do appreciate the reply!
Oh, and thanks for the links mate! Someday, when I have the luxury of extra time to surf around the best SQL Server resource, with the best and most informative forum around, I will definitely check those out!
--
@ opcI'll take Option 1 for $1000! 🙂 And thanks for the help! I got home about 3:45 AM, and was back in the office today (Saturday, ugh!) before 10 AM, so my brain's a little loopy trying to get this project done and stay out of the unemployment office! :-/ I should've thought of using an INSTEAD OF INSERT trigger. But the 2 insert method is what I ended up using.
When I didn't see anything remotely related to what I was trying to Google, I thought I'd throw it out there.
Thanks again! And I really do feel like I'm one of the nanos gigantium humeris insidentes!
Cheers!
July 16, 2011 at 9:38 pm
krushkoder (7/16/2011)
Thanks again! And I really do feel like I'm one of the nanos gigantium humeris insidentes!
Me too, every day mate...happy you got it sorted 😎
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 16, 2011 at 11:19 pm
Yes, you can filter an OUTPUT clause, and achieve what you want to do in one statement:
DECLARE @Production TABLE (col1 INT NULL, col2 INT NULL, col3 INT NULL);
DECLARE @archive TABLE (col1 INT NULL, col2 INT NULL, col3 INT NULL);
DECLARE @Import TABLE (col1 INT NULL, col2 INT NULL, col3 INT NULL);
INSERT @Import
(col1, col2, col3)
VALUES
(1, 100, 1),
(2, 200, 0),
(3, 300, 3),
(4, 400, 0),
(5, 500, 0);
INSERT @Production
(col1, col2, col3)
SELECT
import.col1,
import.col2,
import.col3
FROM
(
INSERT @archive
(col1, col2, col3)
OUTPUT
INSERTED.col1,
INSERTED.col2,
INSERTED.col3
SELECT
i.col1,
i.col2,
i.col3
FROM @Import AS i
) AS import
WHERE
import.col3 <> 0;
SELECT * FROM @archive AS a;
SELECT * FROM @Production AS p;
July 17, 2011 at 7:04 am
SQLkiwi (7/16/2011)
Yes, you can filter an OUTPUT clause
Can you? This would seem like Option (i.e. workaround) #3. The filtering is not technically part of the OUTPUT clause but it will work. I forgot you could use OUTPUT in a derived table but I think there are some restrictions in some scenarios (Adam Machanic's blog on MERGE comes to mind). This Option could likely perform better than two separate inserts due to only one pass over the source data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 17, 2011 at 9:31 am
opc.three (7/17/2011)
Can you? This would seem like Option (i.e. workaround) #3. The filtering is not technically part of the OUTPUT clause but it will work.
The OUTPUT rows are produced by the Table/Clustered Index Insert operator in the plan. The next iterator in the sequence is a Filter.
I forgot you could use OUTPUT in a derived table but I think there are some restrictions in some scenarios (Adam Machanic's blog on MERGE comes to mind).
There are a number of restrictions, the most serious being that the INSERT target cannot have triggers or foreign keys. The full set of restrictions are documented in BOL: http://msdn.microsoft.com/en-us/library/ms177564.aspx
This Option could likely perform better than two separate inserts due to only one pass over the source data.
Quite possibly. The operation is also automatically atomic (not the case with two statements unless an explicit transaction is used).
July 17, 2011 at 12:54 pm
SQLkiwi (7/17/2011)
I forgot you could use OUTPUT in a derived table but I think there are some restrictions in some scenarios (Adam Machanic's blog on MERGE comes to mind).
There are a number of restrictions, the most serious being that the INSERT target cannot have triggers or foreign keys. The full set of restrictions are documented in BOL: http://msdn.microsoft.com/en-us/library/ms177564.aspx
Agreed, there are definitely restrictions on inserting when it comes to the target table. I was referring to restrictions in terms of using OUTPUT results in a derived table. The only example BOL gave of this technique is when using OUTPUT from MERGE as a derived table in an INSERT...SELECT, and IIRC (it was a lengthy entry) that's what Adam's blog entry was highlighting. I think this entry was the one, there may be another one out there more specific to the derived table issue, I can't seem to verify that at the moment. BOL doesn't really talk about other use-cases for having OUTPUT results function a derived table so I am not sure it's supported outside of this one use-case.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 17, 2011 at 7:35 pm
opc.three (7/17/2011)
I was referring to restrictions in terms of using OUTPUT results in a derived table.
Right, I'm with you now - yes 'composable DML' is limited to being a source for INSERT right now. Haven't seen anything about Denali extending this support, and http://connect.microsoft.com/SQLServer/feedback/details/327594/composable-dml-remove-insert-limitation has been closed as Won't Fix.
July 17, 2011 at 10:38 pm
Option 2...down the drain. I completely spaced on the limitation re: triggers on the target table.
Option 4: issue two inserts where neither uses the OUTPUT clause
- Insert 1: insert into tblProduction WHERE col3 <> 0
- Insert 2: insert into tblArchive with no WHERE clause
This may outperform Option 1's pair of inserts and Paul's option of doing everything in one query may be faster still.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply