October 16, 2013 at 3:46 pm
We have three databases on one instance of SQL Server. Currently I have a job setup to insert Vendor records into DB2, that were created in DB1. I would like to do this for our third database as well, DB3. How do I include the third database in my code? Any help will be appreciated.
Current code:
INSERT INTO DB2.dbo.CUSTVEND
SELECT *
FROM DB1.dbo.CUSTVEND c1
where c1.ACCTNO+c1.SUBC not in(Select distinct ACCTNO+SUBC
from DB1.dbo.CUSTVEND)
October 16, 2013 at 3:57 pm
I wonder if the code you provided is actually correct...
As far as I can see, this code will never insert a single row into DB2.dbo.CUSTVEND, since
DB1.dbo.CUSTVEND c1
where c1.ACCTNO+c1.SUBC not in(Select distinct ACCTNO+SUBC
from DB1.dbo.CUSTVEND)
is actually a self-reference to the table CUSTVEND in DB1.
It's probably meant to be
INSERT INTO DB2.dbo.CUSTVEND
SELECT *
FROM DB1.dbo.CUSTVEND c1
where c1.ACCTNO+c1.SUBC not in(Select distinct ACCTNO+SUBC
from DB2.dbo.CUSTVEND)
If you'd like to copy the data from DB1 to DB3, too, then the code would be
INSERT INTO DB3.dbo.CUSTVEND
SELECT *
FROM DB1.dbo.CUSTVEND c1
where c1.ACCTNO+c1.SUBC not in(Select distinct ACCTNO+SUBC
from DB3.dbo.CUSTVEND)
October 16, 2013 at 4:06 pm
I would like to add it to the existing code. Is that possible?
October 16, 2013 at 4:42 pm
An insert statement can only have one target table at a time.
Even if it would technically be possible, I don't see any logical way to make it work: just assume DB3 was not accessible during the previous insert into DB2 (a simple DETACH DATABASE would do it...). Then you would have a different set of rows to insert into DB2 vs. DB3. How should this be coded?
Btw: The code snippet I provided can also be considered as being "added to the existing code" 😉
October 16, 2013 at 6:32 pm
This is untested and may not be possible but you can give it a try:
INSERT INTO DB2.dbo.CUSTVEND
OUTPUT Inserted.*
INTO DB3.dbo.CUSTVEND
SELECT *
FROM DB1.dbo.CUSTVEND c1
where c1.ACCTNO+c1.SUBC not in(Select distinct ACCTNO+SUBC
from DB1.dbo.CUSTVEND)
Not sure the OUTPUT clause will work across databases.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 17, 2013 at 12:00 pm
Thanks for all the replies on this. Much appreciated!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply