February 26, 2010 at 7:02 pm
I have this query to add missing vendors to the vpsvendor table which runs very slow as I have 100k records in projectdata table. I have to check if a vendor is in projectdata table but not in vpsvendor table and add it to vpsvendor table. How can I make this run faster. I have not written insert statement for this yet and just the select because it is very slow but if you can give me a fast insert statement it would be very helpful as I have spent 2 hours on this and could not get it to work.
Select projectid,VendorNumber,vendorname from projectdata
where
vendorname Not IN
(select vendorname FROM vpsvendor(nolock) Where projectid =104)
AND
VendorNumber Not IN
(select VendorCode FROM vpsvendor(nolock) Where projectid =104)
February 26, 2010 at 8:00 pm
I did not post the create and insert statements in my last post
CREATE TABLE [dbo].[projectdata](
[InvoiceId] [int] IDENTITY(1,1) NOT NULL,
[ProjectId] [int] NOT NULL,
[CompanyCode] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VendorNumber] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CheckNumber] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceNumber] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VoucherNumber] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DocumentNumber] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Ap1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Ap2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Ap3] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserId] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PurchaseOrdNumber] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CheckStatus] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VendorName] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceDate] [datetime] NULL,
[InvoiceNo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CheckDate] [datetime] NULL,
[InvoiceAmount] [float] NULL,
[CheckAmount] [float] NULL,
[tagging] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_mjtest_tagging] DEFAULT ('no'),
[auto_tag] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[duplicate_type] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_mjtest_duplicate_type] DEFAULT ('no'),
[convertion_done] [tinyint] NULL CONSTRAINT [DF_mjtest_convertion_done] DEFAULT ((0)),
[duplicate_with] [bigint] NULL CONSTRAINT [DF_mjtest_duplicate_with] DEFAULT ((0)),
[StatusId] [int] NULL CONSTRAINT [DF_mjtest_StatusId] DEFAULT ((0)),
[unmark] [tinyint] NULL,
[reason] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[discount] [float] NULL,
[tagstatus] [int] NULL CONSTRAINT [DF_mjtest_tagstatus] DEFAULT ((0)),
[sortbyid] [bigint] NULL,
[chkfornegative] [tinyint] NULL CONSTRAINT [DF_mjtest_chkfornegative] DEFAULT ((0)),
[id] [bigint] NULL,
CONSTRAINT [PK_dbo.mjtest] PRIMARY KEY CLUSTERED
(
[InvoiceId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Insert statements
INSERT INTO [projectdata] ([InvoiceId],[ProjectId],[CompanyCode],[VendorNumber],[CheckNumber],[InvoiceNumber],[VoucherNumber],[DocumentNumber],[Ap1],[Ap2],[Ap3],[UserId],[PurchaseOrdNumber],[CheckStatus],[VendorName],[InvoiceDate],[InvoiceNo],[CheckDate],[InvoiceAmount],[CheckAmount],[tagging],[auto_tag],[duplicate_type],[convertion_done],[duplicate_with],[StatusId],[unmark],[reason],[discount],[tagstatus],[sortbyid],[chkfornegative],[id])VALUES(1,50,'MTGI3','NOR03','A-286','286','16270','16270','a1414','b1211','c15281 ','11111','A134590','P','NORTHEAST SALES DIST INC','Oct 15 2008 12:00:00:000AM',NULL,'Oct 16 2008 12:00:00:000AM',-5.220000000000000e+002,-5.220000000000000e+002,'TD','YES','TRUE DUPLICATE',0,1,0,0,NULL,4.800000000000000e+001,0,1,0,0)
INSERT INTO [projectdata] ([InvoiceId],[ProjectId],[CompanyCode],[VendorNumber],[CheckNumber],[InvoiceNumber],[VoucherNumber],[DocumentNumber],[Ap1],[Ap2],[Ap3],[UserId],[PurchaseOrdNumber],[CheckStatus],[VendorName],[InvoiceDate],[InvoiceNo],[CheckDate],[InvoiceAmount],[CheckAmount],[tagging],[auto_tag],[duplicate_type],[convertion_done],[duplicate_with],[StatusId],[unmark],[reason],[discount],[tagstatus],[sortbyid],[chkfornegative],[id])VALUES(2,50,'MTGI3','NOR03','5315','286','16370','16370','a1415','b1212','c15282 ','11112','A134591','V','NORTHEAST SALES DIST INC','Oct 15 2008 12:00:00:000AM',NULL,'Nov 14 2008 12:00:00:000AM',2.000000000000000e-002,2.000000000000000e-002,'TDCR','MATCHED','MATCHED CREDIT',0,2,0,0,NULL,4.800000000000000e+001,0,2,0,0)
INSERT INTO [projectdata] ([InvoiceId],[ProjectId],[CompanyCode],[VendorNumber],[CheckNumber],[InvoiceNumber],[VoucherNumber],[DocumentNumber],[Ap1],[Ap2],[Ap3],[UserId],[PurchaseOrdNumber],[CheckStatus],[VendorName],[InvoiceDate],[InvoiceNo],[CheckDate],[InvoiceAmount],[CheckAmount],[tagging],[auto_tag],[duplicate_type],[convertion_done],[duplicate_with],[StatusId],[unmark],[reason],[discount],[tagstatus],[sortbyid],[chkfornegative],[id])VALUES(3,50,'MTGI3','NOR03','5315','286','16339','16339','a1416','b1213','c15283 ','11113','A134592','P','NORTHEAST SALES DIST INC','Oct 15 2008 12:00:00:000AM',NULL,'Nov 13 2008 12:00:00:000AM',-2.000000000000000e-002,-2.000000000000000e-002,'TDCR','MATCHED','MATCHED CREDIT',0,2,0,0,NULL,4.800000000000000e+001,0,2,0,0)
INSERT INTO [projectdata] ([InvoiceId],[ProjectId],[CompanyCode],[VendorNumber],[CheckNumber],[InvoiceNumber],[VoucherNumber],[DocumentNumber],[Ap1],[Ap2],[Ap3],[UserId],[PurchaseOrdNumber],[CheckStatus],[VendorName],[InvoiceDate],[InvoiceNo],[CheckDate],[InvoiceAmount],[CheckAmount],[tagging],[auto_tag],[duplicate_type],[convertion_done],[duplicate_with],[StatusId],[unmark],[reason],[discount],[tagstatus],[sortbyid],[chkfornegative],[id])VALUES(4,50,'MTGI3','NOR03','5328','286','16425','16425','a1417','b1214','c15284 ','11114','A134593','P','NORTHEAST SALES DIST INC','Oct 15 2008 12:00:00:000AM',NULL,'Nov 26 2008 12:00:00:000AM',-5.220000000000000e+002,-5.220000000000000e+002,'TD','YES','TRUE DUPLICATE',0,1,0,0,NULL,4.800000000000000e+001,0,1,0,0)
INSERT INTO [projectdata] ([InvoiceId],[ProjectId],[CompanyCode],[VendorNumber],[CheckNumber],[InvoiceNumber],[VoucherNumber],[DocumentNumber],[Ap1],[Ap2],[Ap3],[UserId],[PurchaseOrdNumber],[CheckStatus],[VendorName],[InvoiceDate],[InvoiceNo],[CheckDate],[InvoiceAmount],[CheckAmount],[tagging],[auto_tag],[duplicate_type],[convertion_done],[duplicate_with],[StatusId],[unmark],[reason],[discount],[tagstatus],[sortbyid],[chkfornegative],[id])VALUES(5,50,'MTGI3','NOR03','7899','DM00000286','16426','16426','a1418','b1215','c15285 ','11115','A134594','D','NORTHEAST SALES DIST INC','Oct 15 2008 12:00:00:000AM',NULL,'Oct 15 2008 12:00:00:000AM',2.000000000000000e-002,2.000000000000000e-002,'no','no',NULL,0,0,0,0,NULL,4.800000000000000e+001,0,22,0,0)
INSERT INTO [projectdata] ([InvoiceId],[ProjectId],[CompanyCode],[VendorNumber],[CheckNumber],[InvoiceNumber],[VoucherNumber],[DocumentNumber],[Ap1],[Ap2],[Ap3],[UserId],[PurchaseOrdNumber],[CheckStatus],[VendorName],[InvoiceDate],[InvoiceNo],[CheckDate],[InvoiceAmount],[CheckAmount],[tagging],[auto_tag],[duplicate_type],[convertion_done],[duplicate_with],[StatusId],[unmark],[reason],[discount],[tagstatus],[sortbyid],[chkfornegative],[id])VALUES(6,50,'MTGI3','NOR03','A-286','286','16353','16353','a1419','b1216','c15286 ','11116','A134595','V','NORTHEAST SALES DIST INC','Oct 15 2008 12:00:00:000AM',NULL,'Nov 13 2008 12:00:00:000AM',5.220000000000000e+002,5.220000000000000e+002,'TD','YES','TRUE DUPLICATE',0,6,0,0,NULL,4.800000000000000e+001,0,1,0,0)
INSERT INTO [projectdata] ([InvoiceId],[ProjectId],[CompanyCode],[VendorNumber],[CheckNumber],[InvoiceNumber],[VoucherNumber],[DocumentNumber],[Ap1],[Ap2],[Ap3],[UserId],[PurchaseOrdNumber],[CheckStatus],[VendorName],[InvoiceDate],[InvoiceNo],[CheckDate],[InvoiceAmount],[CheckAmount],[tagging],[auto_tag],[duplicate_type],[convertion_done],[duplicate_with],[StatusId],[unmark],[reason],[discount],[tagstatus],[sortbyid],[chkfornegative],[id])VALUES(7,50,'MTGI3','RAM03','5318','CK RQST 10/31/0','16342','16342','a1420','b1217','c15287 ','11117','A134596','P','FELIPE RAMIREZ','Oct 31 2008 12:00:00:000AM',NULL,'Nov 13 2008 12:00:00:000AM',-4.136400000000000e+002,-4.136400000000000e+002,'no','no',NULL,0,0,0,0,NULL,4.800000000000000e+001,0,22,0,0)
INSERT INTO [projectdata] ([InvoiceId],[ProjectId],[CompanyCode],[VendorNumber],[CheckNumber],[InvoiceNumber],[VoucherNumber],[DocumentNumber],[Ap1],[Ap2],[Ap3],[UserId],[PurchaseOrdNumber],[CheckStatus],[VendorName],[InvoiceDate],[InvoiceNo],[CheckDate],[InvoiceAmount],[CheckAmount],[tagging],[auto_tag],[duplicate_type],[convertion_done],[duplicate_with],[StatusId],[unmark],[reason],[discount],[tagstatus],[sortbyid],[chkfornegative],[id])VALUES(8,50,'MTGI3','SYS03','101214','810220564','17146','17146','a1421','b1218','c15288 ','11118','A134597','P','SYSCO FOOD SVS OF ATLANTA','Nov 1 2008 12:00:00:000AM',NULL,'Dec 16 2008 12:00:00:000AM',-8.834999999999999e+001,-8.834999999999999e+001,'A2','YES','INVOICE DATE',0,8,0,0,NULL,4.800000000000000e+001,0,11,0,0)
INSERT INTO [projectdata] ([InvoiceId],[ProjectId],[CompanyCode],[VendorNumber],[CheckNumber],[InvoiceNumber],[VoucherNumber],[DocumentNumber],[Ap1],[Ap2],[Ap3],[UserId],[PurchaseOrdNumber],[CheckStatus],[VendorName],[InvoiceDate],[InvoiceNo],[CheckDate],[InvoiceAmount],[CheckAmount],[tagging],[auto_tag],[duplicate_type],[convertion_done],[duplicate_with],[StatusId],[unmark],[reason],[discount],[tagstatus],[sortbyid],[chkfornegative],[id])VALUES(9,50,'MTGI3','NOR03','5328','286','16725','16725','a1422','b1219','c15289 ','11119','A134598','V','NORTHEAST SALES DIST INC','Oct 15 2008 12:00:00:000AM',NULL,'Nov 30 2008 12:00:00:000AM',5.220000000000000e+002,5.220000000000000e+002,'TD','YES','TRUE DUPLICATE',0,6,0,0,NULL,4.800000000000000e+001,0,1,0,0)
INSERT INTO [projectdata] ([InvoiceId],[ProjectId],[CompanyCode],[VendorNumber],[CheckNumber],[InvoiceNumber],[VoucherNumber],[DocumentNumber],[Ap1],[Ap2],[Ap3],[UserId],[PurchaseOrdNumber],[CheckStatus],[VendorName],[InvoiceDate],[InvoiceNo],[CheckDate],[InvoiceAmount],[CheckAmount],[tagging],[auto_tag],[duplicate_type],[convertion_done],[duplicate_with],[StatusId],[unmark],[reason],[discount],[tagstatus],[sortbyid],[chkfornegative],[id])VALUES(10,50,'MTGI3','SYS03','101218','812231389','17160','17160','a1423','b1220','c15290 ','11120','A134599','P','SYSCO FOOD SVS OF ATLANTA','Dec 23 2008 12:00:00:000AM',NULL,'Dec 30 2008 12:00:00:000AM',-9.509270000000000e+003,-9.509270000000000e+003,'no','no',NULL,0,0,0,0,NULL,4.800000000000000e+001,0,22,0,0)
CREATE TABLE [dbo].[vpsVendor](
[VendorID] [int] IDENTITY(1,1) NOT NULL,
[VendorCode] [varchar](max) NOT NULL,
[VendorName] [varchar](max) NOT NULL,
[Address1] [varchar](max) NULL,
[Address2] [varchar](max) NULL,
[City] [varchar](max) NULL,
[State] [varchar](2) NULL,
[Zip] [varchar](max) NULL,
[Phone] [varchar](max) NULL,
[Fax] [varchar](max) NULL,
[Email] [varchar](max) NULL,
[CountryOutsideUSA] [varchar](max) NULL,
[OpenField] [varchar](max) NULL,
[ProjectID] [int] NOT NULL,
[DateAdded] [datetime] NULL CONSTRAINT [DF_vpsVendor_DateAdded] DEFAULT (getdate()),
[DateUpdated] [datetime] NULL CONSTRAINT [DF_vpsVendor_DateUpdated] DEFAULT (getdate()),
[vpstransaction] [bigint] NULL CONSTRAINT [DF_vpsVendor_vpstransaction] DEFAULT ((0)),
[vpsmoney] [bigint] NULL CONSTRAINT [DF_vpsVendor_vpsmoney] DEFAULT ((0)),
[MailStatus] [varchar](max) NULL CONSTRAINT [DF_vpsVendor_MailStatus] DEFAULT (' '),
[name_duplicate_with] [bigint] NULL,
[addedfrominvoice] [bigint] NULL CONSTRAINT [DF_vpsVendor_addedfrominvoice] DEFAULT ((0)),
[tagged] [tinyint] NULL CONSTRAINT [DF_vpsVendor_tagged] DEFAULT ((0)),
[cgc] [varchar](320) NULL,
[checked] [tinyint] NULL CONSTRAINT [DF_vpsVendor_checked] DEFAULT ((0)),
CONSTRAINT [PK_vpsVendor] PRIMARY KEY CLUSTERED
(
[VendorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
February 26, 2010 at 8:28 pm
manojkanoi (2/26/2010)
I have this query to add missing vendors to the vpsvendor table which runs very slow as I have 100k records in projectdata table. I have to check if a vendor is in projectdata table but not in vpsvendor table and add it to vpsvendor table. How can I make this run faster. I have not written insert statement for this yet and just the select because it is very slow but if you can give me a fast insert statement it would be very helpful as I have spent 2 hours on this and could not get it to work.Select projectid,VendorNumber,vendorname from projectdata
where
vendorname Not IN
(select vendorname FROM vpsvendor(nolock) Where projectid =104)
AND
VendorNumber Not IN
(select VendorCode FROM vpsvendor(nolock) Where projectid =104)
You only showed one of the tables and no indexes. Do you have any indexes on these tables other than the PK?
Also, you might want to take at the second link in my signature below when posting performance problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2010 at 8:36 pm
Thanks. Indexes won't help a lot in this case and I need to change the way I am doing this comparison. I had posted the second table create statement but forgot insert statements which are posted below.
INSERT INTO [vpsvendor] ([VendorID],[VendorCode],[VendorName],[Address1],[Address2],[City],[State],[Zip],[Phone],[Fax],[Email],[CountryOutsideUSA],[OpenField],[ProjectID],[DateAdded],[DateUpdated],[vpstransaction],[vpsmoney],[MailStatus],[name_duplicate_with],[addedfrominvoice],[tagged],[cgc],[checked])VALUES(550637,'TEX21','TEXAS NARCOTICS OFFICERS ASSOC','P.O. BOX 9049','0','DALLAS','TX','75209','0','0','0','0',' ',90,'Feb 19 2010 11:34:14:337PM','Feb 21 2010 8:09:43:767PM',0,0,'',NULL,0,0,NULL,0)
INSERT INTO [vpsvendor] ([VendorID],[VendorCode],[VendorName],[Address1],[Address2],[City],[State],[Zip],[Phone],[Fax],[Email],[CountryOutsideUSA],[OpenField],[ProjectID],[DateAdded],[DateUpdated],[vpstransaction],[vpsmoney],[MailStatus],[name_duplicate_with],[addedfrominvoice],[tagged],[cgc],[checked])VALUES(550638,'LEG01','LEGACY CHRISTIAN ACADEMY','5000 ACADEMY DRIVE','0','FRISCO','TX','75034','0','0','0','0',' ',90,'Feb 19 2010 11:34:14:353PM','Feb 22 2010 11:45:56:223PM',1,59,'3 Mailing',NULL,0,1,'MCI',1)
INSERT INTO [vpsvendor] ([VendorID],[VendorCode],[VendorName],[Address1],[Address2],[City],[State],[Zip],[Phone],[Fax],[Email],[CountryOutsideUSA],[OpenField],[ProjectID],[DateAdded],[DateUpdated],[vpstransaction],[vpsmoney],[MailStatus],[name_duplicate_with],[addedfrominvoice],[tagged],[cgc],[checked])VALUES(550639,'CHI01','DAVID CHILDS','DALLAS COUNTY ASSESSOR-COLLEC','TP.O. BOX 139066','DALLAS','TX','75313','0','0','0','0',' ',90,'Feb 19 2010 11:34:14:370PM','Feb 22 2010 11:45:56:223PM',16,267031,'3 Mailing',NULL,0,1,'MCI',1)
INSERT INTO [vpsvendor] ([VendorID],[VendorCode],[VendorName],[Address1],[Address2],[City],[State],[Zip],[Phone],[Fax],[Email],[CountryOutsideUSA],[OpenField],[ProjectID],[DateAdded],[DateUpdated],[vpstransaction],[vpsmoney],[MailStatus],[name_duplicate_with],[addedfrominvoice],[tagged],[cgc],[checked])VALUES(550640,'MCL01','MC LEATHER','1816 WESTWOOD CR.','0','CARROLLTON','TX','75006','0','0','0','0',' ',90,'Feb 19 2010 11:34:14:370PM','Feb 22 2010 11:45:56:223PM',69,44225,'3 Mailing',NULL,0,1,'MCI',1)
INSERT INTO [vpsvendor] ([VendorID],[VendorCode],[VendorName],[Address1],[Address2],[City],[State],[Zip],[Phone],[Fax],[Email],[CountryOutsideUSA],[OpenField],[ProjectID],[DateAdded],[DateUpdated],[vpstransaction],[vpsmoney],[MailStatus],[name_duplicate_with],[addedfrominvoice],[tagged],[cgc],[checked])VALUES(550641,'D&B01','D&B','P.O. BOX 75542','0','CHICAGO','IL','606755542','(800)234-3867','0','0','0',' ',90,'Feb 19 2010 11:34:14:383PM','Feb 22 2010 11:45:56:223PM',35,1503,'3 Mailing',NULL,0,1,'MCI',1)
INSERT INTO [vpsvendor] ([VendorID],[VendorCode],[VendorName],[Address1],[Address2],[City],[State],[Zip],[Phone],[Fax],[Email],[CountryOutsideUSA],[OpenField],[ProjectID],[DateAdded],[DateUpdated],[vpstransaction],[vpsmoney],[MailStatus],[name_duplicate_with],[addedfrominvoice],[tagged],[cgc],[checked])VALUES(550642,'WIN04','WINSTON WATER COOLER, LTD.','P.O. BOX 567608','0','DALLAS','TX','75356','(214)748-1484','(214)748-1486','0','0',' ',90,'Feb 19 2010 11:34:14:400PM','Feb 21 2010 8:09:43:767PM',0,0,'',NULL,0,0,NULL,0)
INSERT INTO [vpsvendor] ([VendorID],[VendorCode],[VendorName],[Address1],[Address2],[City],[State],[Zip],[Phone],[Fax],[Email],[CountryOutsideUSA],[OpenField],[ProjectID],[DateAdded],[DateUpdated],[vpstransaction],[vpsmoney],[MailStatus],[name_duplicate_with],[addedfrominvoice],[tagged],[cgc],[checked])VALUES(550643,'HOS04','HOSE-FAST INC.','2614 INDUSTRIAL LN. #103','0','GARLAND','TX','75041','(972)271-8591','(972)840-1550','0','0',' ',90,'Feb 19 2010 11:34:14:417PM','Feb 21 2010 8:09:43:767PM',0,0,'',NULL,0,0,NULL,0)
INSERT INTO [vpsvendor] ([VendorID],[VendorCode],[VendorName],[Address1],[Address2],[City],[State],[Zip],[Phone],[Fax],[Email],[CountryOutsideUSA],[OpenField],[ProjectID],[DateAdded],[DateUpdated],[vpstransaction],[vpsmoney],[MailStatus],[name_duplicate_with],[addedfrominvoice],[tagged],[cgc],[checked])VALUES(550644,'HAR01','HARRISON LANDSCAPE INC.','P.O. BOX 1447','0','ADDISON','TX','75001','0','0','0','0',' ',90,'Feb 19 2010 11:34:14:430PM','Feb 22 2010 11:45:56:223PM',41,28124,'3 Mailing',NULL,0,1,'MCI',1)
INSERT INTO [vpsvendor] ([VendorID],[VendorCode],[VendorName],[Address1],[Address2],[City],[State],[Zip],[Phone],[Fax],[Email],[CountryOutsideUSA],[OpenField],[ProjectID],[DateAdded],[DateUpdated],[vpstransaction],[vpsmoney],[MailStatus],[name_duplicate_with],[addedfrominvoice],[tagged],[cgc],[checked])VALUES(550645,'HAR04','HARPER WOOD ELECTRIC CO.','621 CHESNUT','0','SAN ANTONIO','TX','78202','0','0','0','0',' ',90,'Feb 19 2010 11:34:14:447PM','Feb 22 2010 11:45:56:223PM',2,102,'3 Mailing',NULL,0,1,'MCI',1)
INSERT INTO [vpsvendor] ([VendorID],[VendorCode],[VendorName],[Address1],[Address2],[City],[State],[Zip],[Phone],[Fax],[Email],[CountryOutsideUSA],[OpenField],[ProjectID],[DateAdded],[DateUpdated],[vpstransaction],[vpsmoney],[MailStatus],[name_duplicate_with],[addedfrominvoice],[tagged],[cgc],[checked])VALUES(550646,'HAT01','HATCHER BUILDING & CONST.','308 OAK FOREST','0','HIGHLAND VILLAG','TX','75077','(972)471-2147','0','0','0',' ',90,'Feb 19 2010 11:34:14:447PM','Feb 22 2010 11:45:56:223PM',22,353551,'3 Mailing',NULL,0,1,'MCI',1)
February 26, 2010 at 11:29 pm
Appropriate indexes make all the difference, as does posting code that actually runs.
(You forgot the SET IDENTITY_INSERT statements, and the COLLATE clauses for the second table are missing))
-- Indexes required
CREATE NONCLUSTERED INDEX nc1 ON dbo.projectdata (ProjectID ASC) INCLUDE (VendorNumber, VendorName);
CREATE NONCLUSTERED INDEX nc1 ON dbo.vpsVendor (ProjectID ASC) INCLUDE (VendorName, VendorCode);
GO
-- Add missing vendors for project 104 (maybe)
INSERT dbo.vpsVendor
(
ProjectID,
VendorCode,
VendorName
)
SELECT ProjectId,
VendorNumber,
VendorName
FROM dbo.projectdata PD
WHERE PD.ProjectId = 104
AND NOT EXISTS
(
SELECT *
FROM dbo.vpsVendor V
WHERE V.VendorName COLLATE SQL_Latin1_General_CP1_CI_AS = PD.VendorName
AND V.VendorCode COLLATE SQL_Latin1_General_CP1_CI_AS = PD.VendorNumber
AND V.ProjectID = PD.ProjectId
);
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2010 at 10:29 am
Thanks for your help. I will test the queries I generate before posting them here to make sure they work.
February 27, 2010 at 10:50 pm
Thank you.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply