December 1, 2011 at 1:24 am
I would like to filter the large table using the small one, so that I end up with only those records that have a common "Drawing Number". I would like to incluse the "Path" from the small table in the output.
Here are the table details:
CREATE TABLE "dbo"."Largetable" (
"Project__" varchar (7) NULL ,
"Country_code" varchar (3) NULL ,
"Project_number" varchar (4) NULL ,
"Unique_number" char (6) NOT NULL ,
"Old_Dwg_Number" "tinyint" NULL ,
"XReuse" "tinyint" NULL ,
"Drawing_number_seq" varchar (6) NULL ,
"Size_of_drawing" char (1) NOT NULL ,
"ISO_CODE" varchar (2) NULL ,
"Drawing_number" char (6) NOT NULL ,
"Part_list" "tinyint" NULL ,
"Revision" "tinyint" NULL ,
"type_of_drawing_code" varchar (2) NULL ,
"Type_of_drawing_text" varchar (40) NULL
)
GO
The first 100 records are:
Project_Country CodeProject NumberUnique NumberOld dwg numberReuseDrawing no. seqSize of drawingISO CodeDrawing NumberPart ListRevision1Type of Dwg codeType of Dwg Text
GER2259GER2259177031256644A45664110Detail drawing
NZE0292NZE292207551256644A45664110Detail drawing
NZE0310NZE31035696128278434A45664110Detail drawing
IDO0237IDO23736531128286954A456642110Detail drawing
TAI0131TAI13144123128364934A456642110Detail drawing
TYR0160TYR16047171128395414A456642110Detail drawing
SVE0599SVE59951008128433784A456642110Detail drawing
GER2932GER293262543128549134A456642110Detail drawing
GER2932GER293262448128548184A456652110Detail drawing
23111104613A31046111Chart, graph, nomogram
czk0503czk50323211104621A11046210Detail drawing
POL0427POL42723911104693A31046910Detail drawing
czk0503czk50324011104701A11047010Detail drawing
DAN0893DAN89324311104731A1104734Flow diagram
czk0503czk50324411104741A11047436Flow diagram with service connections
NZE0273NZE27324511104752A2104758Flow diagram with PI-symbols
POL0467POL467138711104951A11049518Flow diagram with PI-symbols
POL0427POL427141911105071A11050716Flow diagram with service connections
POL0427POL427142011105081A11050816Flow diagram with service connections
POL0427POL427142111105091A11050916Flow diagram with service connections
POL0427POL427144111105323A31053219Assembly drawing
144211105331A11053314Flow diagram
UNG0700UNG700144311105341A11053414Flow diagram
ENG1226ENG1226144411105351A11053514Flow diagram
144511105381A11053814Flow diagram
UNG0700UNG700144611105391A11053914Flow diagram
AUS0309AUS309144711105401A11054014Flow diagram
167211105661A11056614Flow diagram
167311105671A11056714Flow diagram
SVE0562SVE562173711105783A31057814Flow diagram
NEP0006NEP6173911105801A11058014Flow diagram
RUS0461RUS461174011105811A11058114Flow diagram
RUS0461RUS461174111105821A11058214Flow diagram
DAN0822DAN822183811106051A11060518Flow diagram with PI-symbols
GER2077GER2077185011106171A11061714Flow diagram
ENG1226ENG1226201311106331A11063319Assembly drawing
ENG1226ENG1226201411106341A11063419Assembly drawing
ENG1226ENG1226201511106351A11063519Assembly drawing
ENG1226ENG1226201711106372A21063719Assembly drawing
IRL0319IRL319203911106591A11065914Flow diagram
AUS0309AUS309230011106731A11067311Lay out
RUS0461RUS461230111106741A11067414Flow diagram
230211106751A11067519Assembly drawing
231011106761A110676110Detail drawing
231111106771A110677110Detail drawing
RUS0461RUS461231211106781A11067811Lay out
IRL3191IRL3191239011106861A1106861
IRL3192IRL3192239111106871A1106871
239211106881A1106881
239811106942A2106941
DAN0814DAN814239911106951A11069518Flow diagram with PI-symbols
DAN0814DAN814240011106961A11069618Flow diagram with PI-symbols
ENG1226ENG1226240311106994A4106991
DAN0814DAN814241611107121A11071211Lay out
ENG1226ENG1226241911107151A110715110Detail drawing
ENG1226ENG1226244411107184A410718110Detail drawing
AUS0309AUS309244711107211A11072111Lay out
ENG1226ENG1226244911107231A11072318Flow diagram with PI-symbols
TWN0154TWN154245011107241A11072414Flow diagram
TWN0154TWN154245111107251A11072511Lay out
ENG1226ENG1226265711107421A11074218Flow diagram with PI-symbols
ENG1226ENG1226268111107531A110753113Pipe diagram
ENG1226ENG1226268411107541A110754113Pipe diagram
ENG1226ENG1226268711107551A110755113Pipe diagram
RUM0321RUM321271511107651A11076518Flow diagram with PI-symbols
IRA0086IRA86279411107681A11076814Flow diagram
IRA0086IRA86279511107691A11076914Flow diagram
FIN0151FIN151279611107701A11077014Flow diagram
SKO0329SKO329280011107761A11077618Flow diagram with PI-symbols
czk0513czk513315911107771A11077718Flow diagram with PI-symbols
ENG1226ENG1226316011107781A11077818Flow diagram with PI-symbols
NZE0267NZE267316511107831A11078311Lay out
NZE0267NZE267316611107841A11078418Flow diagram with PI-symbols
NZE0267NZE267316711107851A11078518Flow diagram with PI-symbols
NZE0267NZE267316811107861A11078618Flow diagram with PI-symbols
NZE0267NZE267316911107871A11078718Flow diagram with PI-symbols
NZE0267NZE267317011107881A11078818Flow diagram with PI-symbols
NZE0267NZE267317111107891A11078918Flow diagram with PI-symbols
NZE0267NZE267317211107901A11079018Flow diagram with PI-symbols
NZE0267NZE267317311107911A11079116Flow diagram with service connections
NZE0267NZE267317411107921A11079216Flow diagram with service connections
NZE0267NZE267317511107931A11079316Flow diagram with service connections
NZE0267NZE267317611107941A11079416Flow diagram with service connections
NZE0267NZE267317711107951A110795119Isometric drawing
NZE0267NZE267317811107961A110796119Isometric drawing
NZE0267NZE267317911107971A110797119Isometric drawing
NZE0267NZE267318011107982A210798119Isometric drawing
NZE0267NZE267318111107992A210799119Isometric drawing
NZE0267NZE267318211108003A310800119Isometric drawing
NZE0267NZE267318311108011A110801119Isometric drawing
NZE0267NZE267318411108022A21080211Lay out
NZE0267NZE267318511108032A21080311Lay out
NZE0267NZE267318611108042A21080411Lay out
NZE0267NZE267318711108053A31080511Lay out
NZE0267NZE267318811108063A31080611Lay out
NZE0267NZE267318911108073A310807110Detail drawing
NZE0267NZE267319011108083A310808110Detail drawing
NZE0267NZE267319111108093A310809110Detail drawing
NZE0267NZE267319211108103A310810110Detail drawing
And the small table:
CREATE TABLE "dbo"."Smalltable" (
"Path" varchar (255) ,
"Drawing Number" char (6)
)
GO
First 100 records of small table:
Path Drawing Number
S:\Drawings\056\5664B.dwg5664B.dwg
S:\Drawings\056\5665A.dwg5665A.dwg
S:\Drawings\010-019\10340A.DWG10340A.DWG
S:\Drawings\010-019\10341.DWG10341.DWG
S:\Drawings\010-019\10353.DWG10353.DWG
S:\Drawings\010-019\10363.DWG10363.DWG
S:\Drawings\010-019\10364.DWG10364.DWG
S:\Drawings\010-019\10365.DWG10365.DWG
S:\Drawings\010-019\10366.DWG10366.DWG
S:\Drawings\010-019\10368.DWG10368.DWG
S:\Drawings\010-019\10370.DWG10370.DWG
S:\Drawings\010-019\10397H.DWG10397H.DWG
S:\Drawings\010-019\10407.DWG10407.DWG
S:\Drawings\010-019\10417A.DWG10417A.DWG
S:\Drawings\010-019\10423A.DWG10423A.DWG
S:\Drawings\010-019\10429.DWG10429.DWG
S:\Drawings\010-019\10443E.DWG10443E.DWG
S:\Drawings\010-019\10445.DWG10445.DWG
S:\Drawings\010-019\10448.DWG10448.DWG
S:\Drawings\010-019\10449.DWG10449.DWG
S:\Drawings\010-019\10461.DWG10461.DWG
S:\Drawings\010-019\10462.DWG10462.DWG
S:\Drawings\010-019\10469.DWG10469.DWG
S:\Drawings\010-019\10470.DWG10470.DWG
S:\Drawings\010-019\10473.DWG10473.DWG
S:\Drawings\010-019\10474C.DWG10474C.DWG
S:\Drawings\010-019\10475.DWG10475.DWG
S:\Drawings\010-019\10495.DWG10495.DWG
S:\Drawings\010-019\10507.DWG10507.DWG
S:\Drawings\010-019\10508.DWG10508.DWG
S:\Drawings\010-019\10509.DWG10509.DWG
S:\Drawings\010-019\10532.DWG10532.DWG
S:\Drawings\010-019\10533.DWG10533.DWG
S:\Drawings\010-019\10534.DWG10534.DWG
S:\Drawings\010-019\10535E.DWG10535E.DWG
S:\Drawings\010-019\10538A.DWG10538A.DWG
S:\Drawings\010-019\10539.DWG10539.DWG
S:\Drawings\010-019\10540B.DWG10540B.DWG
S:\Drawings\010-019\10566.DWG10566.DWG
S:\Drawings\010-019\10567.DWG10567.DWG
S:\Drawings\010-019\10578.DWG10578.DWG
S:\Drawings\010-019\10580.DWG10580.DWG
S:\Drawings\010-019\10581A.DWG10581A.DWG
S:\Drawings\010-019\10582.DWG10582.DWG
S:\Drawings\010-019\10605.DWG10605.DWG
S:\Drawings\010-019\10617.DWG10617.DWG
S:\Drawings\010-019\10633B.DWG10633B.DWG
S:\Drawings\010-019\10634B.DWG10634B.DWG
S:\Drawings\010-019\10635C.DWG10635C.DWG
S:\Drawings\010-019\10637.DWG10637.DWG
S:\Drawings\010-019\10659.DWG10659.DWG
S:\Drawings\010-019\10673.DWG10673.DWG
S:\Drawings\010-019\10674.DWG10674.DWG
S:\Drawings\010-019\10675A.DWG10675A.DWG
S:\Drawings\010-019\10676B.DWG10676B.DWG
S:\Drawings\010-019\10677A.DWG10677A.DWG
S:\Drawings\010-019\10678.DWG10678.DWG
S:\Drawings\010-019\10686.DWG10686.DWG
S:\Drawings\010-019\10687A.DWG10687A.DWG
S:\Drawings\010-019\10688A.DWG10688A.DWG
S:\Drawings\010-019\10694.DWG10694.DWG
S:\Drawings\010-019\10695A.DWG10695A.DWG
S:\Drawings\010-019\10696.DWG10696.DWG
S:\Drawings\010-019\10699A.DWG10699A.DWG
S:\Drawings\010-019\10712.DWG10712.DWG
S:\Drawings\010-019\10715.DWG10715.DWG
S:\Drawings\010-019\10718.DWG10718.DWG
S:\Drawings\010-019\10721.DWG10721.DWG
S:\Drawings\010-019\10723.DWG10723.DWG
S:\Drawings\010-019\10724.DWG10724.DWG
S:\Drawings\010-019\10725.DWG10725.DWG
S:\Drawings\010-019\10742.DWG10742.DWG
S:\Drawings\010-019\10753.DWG10753.DWG
S:\Drawings\010-019\10754.DWG10754.DWG
S:\Drawings\010-019\10755.DWG10755.DWG
S:\Drawings\010-019\10765.DWG10765.DWG
S:\Drawings\010-019\10768.DWG10768.DWG
S:\Drawings\010-019\10769.DWG10769.DWG
S:\Drawings\010-019\10770.DWG10770.DWG
S:\Drawings\010-019\10776.DWG10776.DWG
S:\Drawings\010-019\10777.DWG10777.DWG
S:\Drawings\010-019\10778E.DWG10778E.DWG
S:\Drawings\010-019\10783A.DWG10783A.DWG
S:\Drawings\010-019\10784B.DWG10784B.DWG
S:\Drawings\010-019\10785A.DWG10785A.DWG
S:\Drawings\010-019\10786A.DWG10786A.DWG
S:\Drawings\010-019\10787A.DWG10787A.DWG
S:\Drawings\010-019\10788A.DWG10788A.DWG
S:\Drawings\010-019\10789A.DWG10789A.DWG
S:\Drawings\010-019\10790A.DWG10790A.DWG
S:\Drawings\010-019\10791.DWG10791.DWG
S:\Drawings\010-019\10792.DWG10792.DWG
S:\Drawings\010-019\10793.DWG10793.DWG
S:\Drawings\010-019\10794.DWG10794.DWG
S:\Drawings\010-019\10795.DWG10795.DWG
S:\Drawings\010-019\10796.DWG10796.DWG
S:\Drawings\010-019\10797.DWG10797.DWG
S:\Drawings\010-019\10798.DWG10798.DWG
S:\Drawings\010-019\10799.DWG10799.DWG
S:\Drawings\010-019\10800.DWG10800.DWG
December 1, 2011 at 1:45 am
First of all, your Drawing number column is char(6), and yet the values in the second table are longer than six characters. Please provide some real sample data in the form of INSERT statements.
Second, this is really a very easy problem. You just need to use an INNER JOIN. If you want to show rows in the small table that don't have a match in the large table, use a LEFT JOIN with the small table as the left hand table.
Try it out, and post back if you're struggling with anything in particular.
John
December 1, 2011 at 3:15 am
Yes, you are right - the drawing number should not have the ".dwg" extension
OK, here is what I've tried:
SELECT Smalltable.[Drawing Number], "Path"
FROM Smalltable
INNER JOIN Largetable
ON Smalltable.[Drawing Number]=Largetable.[Drawing Number]
But I'm not getting unique drawing number records - I'm getting 8n records where the drawing number is "5664". I'm looking for just one (the first occurrence)
Sorry but I don't have INSERT INTO statements - I import the records from an Excel file or a text file, using the Import and Export tool.
December 1, 2011 at 3:29 am
Assuming the drawing numbers in the small table are unique, just do a SELECT DISTINCT.
I'm afraid that if you don't have INSERT statements, we can't provide you with a tested solution. You say you have the data in Excel - how difficult is it to write a formula to generate the INSERT statements?
Edit: your original post says you want to filter the large table using the small table, but your query attempt filters the small table using the large. Which one is it you need to do? If you want to display the "first" row from the large table for each drawing number, you will need to decide what you mean by "first" - in other words, sorted by which column?
John
December 1, 2011 at 3:44 am
tmccar (12/1/2011)
Yes, you are right - the drawing number should not have the ".dwg" extensionOK, here is what I've tried:
SELECT Smalltable.[Drawing Number], "Path"
FROM Smalltable
INNER JOIN Largetable
ON Smalltable.[Drawing Number]=Largetable.[Drawing Number]
But I'm not getting unique drawing number records - I'm getting 8n records where the drawing number is "5664". I'm looking for just one (the first occurrence)
Sorry but I don't have INSERT INTO statements - I import the records from an Excel file or a text file, using the Import and Export tool.
Please write your sample data in this format: -
BEGIN TRAN
SELECT [Project Country Code], [Project Number], [Unique Number], [Old dwg number], [Reuse], [Drawing no. seq],
[Size of drawing], [ISO Code], [Drawing Number], [Part List], [Revision1], [Type of Dwg code], [Type of Dwg Text]
INTO #Largetable
FROM (SELECT 'NZE0267', 'NZE', '267', '3165', '1', '1', '10783', '1', 'A1', '10783', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3166', '1', '1', '10784', '1', 'A1', '10784', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3167', '1', '1', '10785', '1', 'A1', '10785', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3168', '1', '1', '10786', '1', 'A1', '10786', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3169', '1', '1', '10787', '1', 'A1', '10787', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3170', '1', '1', '10788', '1', 'A1', '10788', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3171', '1', '1', '10789', '1', 'A1', '10789', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3172', '1', '1', '10790', '1', 'A1', '10790', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3173', '1', '1', '10791', '1', 'A1', '10791', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3174', '1', '1', '10792', '1', 'A1', '10792', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3175', '1', '1', '10793', '1', 'A1', '10793', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3176', '1', '1', '10794', '1', 'A1', '10794', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3177', '1', '1', '10795', '1', 'A1', '10795', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3178', '1', '1', '10796', '1', 'A1', '10796', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3179', '1', '1', '10797', '1', 'A1', '10797', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3180', '1', '1', '10798', '2', 'A2', '10798', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3181', '1', '1', '10799', '2', 'A2', '10799', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3182', '1', '1', '10800', '3', 'A3', '10800', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3183', '1', '1', '10801', '1', 'A1', '10801', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3184', '1', '1', '10802', '2', 'A2', '10802', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3185', '1', '1', '10803', '2', 'A2', '10803', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3186', '1', '1', '10804', '2', 'A2', '10804', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3187', '1', '1', '10805', '3', 'A3', '10805', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3188', '1', '1', '10806', '3', 'A3', '10806', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3189', '1', '1', '10807', '3', 'A3', '10807', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3190', '1', '1', '10808', '3', 'A3', '10808', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3191', '1', '1', '10809', '3', 'A3', '10809', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3192', '1', '1', '10810', '3', 'A3', '10810', ' 1', '10', 'Detail drawing') a
([Project Country Code], [Project Number], [Unique Number], [Old dwg number], [Reuse], [Drawing no. seq],
[Size of drawing], [ISO Code], [Drawing Number], [Part List], [Revision1], [Type of Dwg code], [Type of Dwg Text])
SELECT [Path], [Drawing Number]
INTO #Smalltable
FROM (SELECT 'S:\Drawings\056\5664B.dwg', '5664B.dwg'
UNION ALL SELECT 'S:\Drawings\056\5665A.dwg', '5665A.dwg'
UNION ALL SELECT 'S:\Drawings\010-019\10340A.DWG', '10340A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10341.DWG', '10341.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10353.DWG', '10353.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10363.DWG', '10363.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10364.DWG', '10364.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10365.DWG', '10365.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10366.DWG', '10366.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10368.DWG', '10368.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10370.DWG', '10370.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10397H.DWG', '10397H.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10407.DWG', '10407.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10417A.DWG', '10417A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10423A.DWG', '10423A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10429.DWG', '10429.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10443E.DWG', '10443E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10445.DWG', '10445.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10448.DWG', '10448.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10449.DWG', '10449.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10461.DWG', '10461.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10462.DWG', '10462.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10469.DWG', '10469.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10470.DWG', '10470.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10473.DWG', '10473.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10474C.DWG', '10474C.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10475.DWG', '10475.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10495.DWG', '10495.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10507.DWG', '10507.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10508.DWG', '10508.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10509.DWG', '10509.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10532.DWG', '10532.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10533.DWG', '10533.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10534.DWG', '10534.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10535E.DWG', '10535E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10538A.DWG', '10538A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10539.DWG', '10539.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10540B.DWG', '10540B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10566.DWG', '10566.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10567.DWG', '10567.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10578.DWG', '10578.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10580.DWG', '10580.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10581A.DWG', '10581A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10582.DWG', '10582.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10605.DWG', '10605.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10617.DWG', '10617.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10633B.DWG', '10633B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10634B.DWG', '10634B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10635C.DWG', '10635C.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10637.DWG', '10637.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10659.DWG', '10659.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10673.DWG', '10673.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10674.DWG', '10674.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10675A.DWG', '10675A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10676B.DWG', '10676B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10677A.DWG', '10677A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10678.DWG', '10678.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10686.DWG', '10686.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10687A.DWG', '10687A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10688A.DWG', '10688A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10694.DWG', '10694.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10695A.DWG', '10695A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10696.DWG', '10696.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10699A.DWG', '10699A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10712.DWG', '10712.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10715.DWG', '10715.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10718.DWG', '10718.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10721.DWG', '10721.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10723.DWG', '10723.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10724.DWG', '10724.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10725.DWG', '10725.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10742.DWG', '10742.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10753.DWG', '10753.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10754.DWG', '10754.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10755.DWG', '10755.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10765.DWG', '10765.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10768.DWG', '10768.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10769.DWG', '10769.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10770.DWG', '10770.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10776.DWG', '10776.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10777.DWG', '10777.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10778E.DWG', '10778E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10783A.DWG', '10783A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10784B.DWG', '10784B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10785A.DWG', '10785A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10786A.DWG', '10786A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10787A.DWG', '10787A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10788A.DWG', '10788A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10789A.DWG', '10789A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10790A.DWG', '10790A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10791.DWG', '10791.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10792.DWG', '10792.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10793.DWG', '10793.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10794.DWG', '10794.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10795.DWG', '10795.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10796.DWG', '10796.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10797.DWG', '10797.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10798.DWG', '10798.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10799.DWG', '10799.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10800.DWG', '10800.DWG') a([Path], [Drawing Number])
ROLLBACK
December 1, 2011 at 4:06 am
Sorry but I'm not sure what this does. I ran the query and it says
(28 row(s) affected)
(100 row(s) affected)
Tom
Cadavre (12/1/2011)
tmccar (12/1/2011)
Yes, you are right - the drawing number should not have the ".dwg" extensionOK, here is what I've tried:
SELECT Smalltable.[Drawing Number], "Path"
FROM Smalltable
INNER JOIN Largetable
ON Smalltable.[Drawing Number]=Largetable.[Drawing Number]
But I'm not getting unique drawing number records - I'm getting 8n records where the drawing number is "5664". I'm looking for just one (the first occurrence)
Sorry but I don't have INSERT INTO statements - I import the records from an Excel file or a text file, using the Import and Export tool.
Please write your sample data in this format: -
BEGIN TRANSELECT [Project Country Code], [Project Number], [Unique Number], [Old dwg number], [Reuse], [Drawing no. seq],
[Size of drawing], [ISO Code], [Drawing Number], [Part List], [Revision1], [Type of Dwg code], [Type of Dwg Text]
INTO #Largetable
FROM (SELECT 'NZE0267', 'NZE', '267', '3165', '1', '1', '10783', '1', 'A1', '10783', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3166', '1', '1', '10784', '1', 'A1', '10784', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3167', '1', '1', '10785', '1', 'A1', '10785', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3168', '1', '1', '10786', '1', 'A1', '10786', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3169', '1', '1', '10787', '1', 'A1', '10787', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3170', '1', '1', '10788', '1', 'A1', '10788', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3171', '1', '1', '10789', '1', 'A1', '10789', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3172', '1', '1', '10790', '1', 'A1', '10790', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3173', '1', '1', '10791', '1', 'A1', '10791', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3174', '1', '1', '10792', '1', 'A1', '10792', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3175', '1', '1', '10793', '1', 'A1', '10793', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3176', '1', '1', '10794', '1', 'A1', '10794', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3177', '1', '1', '10795', '1', 'A1', '10795', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3178', '1', '1', '10796', '1', 'A1', '10796', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3179', '1', '1', '10797', '1', 'A1', '10797', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3180', '1', '1', '10798', '2', 'A2', '10798', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3181', '1', '1', '10799', '2', 'A2', '10799', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3182', '1', '1', '10800', '3', 'A3', '10800', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3183', '1', '1', '10801', '1', 'A1', '10801', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3184', '1', '1', '10802', '2', 'A2', '10802', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3185', '1', '1', '10803', '2', 'A2', '10803', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3186', '1', '1', '10804', '2', 'A2', '10804', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3187', '1', '1', '10805', '3', 'A3', '10805', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3188', '1', '1', '10806', '3', 'A3', '10806', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3189', '1', '1', '10807', '3', 'A3', '10807', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3190', '1', '1', '10808', '3', 'A3', '10808', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3191', '1', '1', '10809', '3', 'A3', '10809', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3192', '1', '1', '10810', '3', 'A3', '10810', ' 1', '10', 'Detail drawing') a
([Project Country Code], [Project Number], [Unique Number], [Old dwg number], [Reuse], [Drawing no. seq],
[Size of drawing], [ISO Code], [Drawing Number], [Part List], [Revision1], [Type of Dwg code], [Type of Dwg Text])
SELECT [Path], [Drawing Number]
INTO #Smalltable
FROM (SELECT 'S:\Drawings\056\5664B.dwg', '5664B.dwg'
UNION ALL SELECT 'S:\Drawings\056\5665A.dwg', '5665A.dwg'
UNION ALL SELECT 'S:\Drawings\010-019\10340A.DWG', '10340A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10341.DWG', '10341.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10353.DWG', '10353.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10363.DWG', '10363.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10364.DWG', '10364.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10365.DWG', '10365.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10366.DWG', '10366.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10368.DWG', '10368.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10370.DWG', '10370.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10397H.DWG', '10397H.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10407.DWG', '10407.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10417A.DWG', '10417A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10423A.DWG', '10423A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10429.DWG', '10429.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10443E.DWG', '10443E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10445.DWG', '10445.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10448.DWG', '10448.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10449.DWG', '10449.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10461.DWG', '10461.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10462.DWG', '10462.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10469.DWG', '10469.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10470.DWG', '10470.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10473.DWG', '10473.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10474C.DWG', '10474C.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10475.DWG', '10475.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10495.DWG', '10495.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10507.DWG', '10507.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10508.DWG', '10508.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10509.DWG', '10509.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10532.DWG', '10532.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10533.DWG', '10533.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10534.DWG', '10534.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10535E.DWG', '10535E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10538A.DWG', '10538A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10539.DWG', '10539.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10540B.DWG', '10540B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10566.DWG', '10566.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10567.DWG', '10567.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10578.DWG', '10578.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10580.DWG', '10580.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10581A.DWG', '10581A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10582.DWG', '10582.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10605.DWG', '10605.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10617.DWG', '10617.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10633B.DWG', '10633B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10634B.DWG', '10634B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10635C.DWG', '10635C.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10637.DWG', '10637.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10659.DWG', '10659.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10673.DWG', '10673.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10674.DWG', '10674.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10675A.DWG', '10675A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10676B.DWG', '10676B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10677A.DWG', '10677A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10678.DWG', '10678.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10686.DWG', '10686.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10687A.DWG', '10687A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10688A.DWG', '10688A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10694.DWG', '10694.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10695A.DWG', '10695A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10696.DWG', '10696.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10699A.DWG', '10699A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10712.DWG', '10712.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10715.DWG', '10715.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10718.DWG', '10718.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10721.DWG', '10721.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10723.DWG', '10723.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10724.DWG', '10724.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10725.DWG', '10725.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10742.DWG', '10742.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10753.DWG', '10753.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10754.DWG', '10754.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10755.DWG', '10755.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10765.DWG', '10765.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10768.DWG', '10768.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10769.DWG', '10769.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10770.DWG', '10770.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10776.DWG', '10776.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10777.DWG', '10777.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10778E.DWG', '10778E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10783A.DWG', '10783A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10784B.DWG', '10784B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10785A.DWG', '10785A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10786A.DWG', '10786A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10787A.DWG', '10787A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10788A.DWG', '10788A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10789A.DWG', '10789A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10790A.DWG', '10790A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10791.DWG', '10791.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10792.DWG', '10792.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10793.DWG', '10793.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10794.DWG', '10794.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10795.DWG', '10795.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10796.DWG', '10796.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10797.DWG', '10797.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10798.DWG', '10798.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10799.DWG', '10799.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10800.DWG', '10800.DWG') a([Path], [Drawing Number])
ROLLBACK
December 1, 2011 at 5:27 am
tmccar (12/1/2011)
Sorry but I'm not sure what this does. I ran the query and it says
Ummm. . .
Cadavre (12/1/2011)
Please write your sample data in this format
I was showing you how you need to write your sample data for people to be able to help with your issue.
December 1, 2011 at 6:01 am
OK, thanks for your help.
So, with the 2 tables created details below, I want to create a third table that contains the fields from "Smalltable" and the corresponding record from "Largetable", but with one unique record for each "Drawing number" - the first occurrence.
SELECT [Project Country Code], [Project Number], [Unique Number], [Old dwg number], [Reuse], [Drawing no. seq],
[Size of drawing], [ISO Code], [Drawing Number], [Part List], [Revision1], [Type of Dwg code], [Type of Dwg Text]
INTO dbo.Largetable
FROM (SELECT 'NZE0267', 'NZE', '267', '3165', '1', '1', '10783', '1', 'A1', '10783', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3166', '1', '1', '10784', '1', 'A1', '10784', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3167', '1', '1', '10785', '1', 'A1', '10785', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3168', '1', '1', '10786', '1', 'A1', '10786', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3169', '1', '1', '10787', '1', 'A1', '10787', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3170', '1', '1', '10788', '1', 'A1', '10788', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3171', '1', '1', '10789', '1', 'A1', '10789', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3172', '1', '1', '10790', '1', 'A1', '10790', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3173', '1', '1', '10791', '1', 'A1', '10791', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3174', '1', '1', '10792', '1', 'A1', '10792', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3175', '1', '1', '10793', '1', 'A1', '10793', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3176', '1', '1', '10794', '1', 'A1', '10794', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3177', '1', '1', '10795', '1', 'A1', '10795', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3178', '1', '1', '10796', '1', 'A1', '10796', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3179', '1', '1', '10797', '1', 'A1', '10797', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3180', '1', '1', '10798', '2', 'A2', '10798', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3181', '1', '1', '10799', '2', 'A2', '10799', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3182', '1', '1', '10800', '3', 'A3', '10800', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3183', '1', '1', '10801', '1', 'A1', '10801', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3184', '1', '1', '10802', '2', 'A2', '10802', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3185', '1', '1', '10803', '2', 'A2', '10803', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3186', '1', '1', '10804', '2', 'A2', '10804', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3187', '1', '1', '10805', '3', 'A3', '10805', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3188', '1', '1', '10806', '3', 'A3', '10806', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3189', '1', '1', '10807', '3', 'A3', '10807', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3190', '1', '1', '10808', '3', 'A3', '10808', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3191', '1', '1', '10809', '3', 'A3', '10809', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3192', '1', '1', '10810', '3', 'A3', '10810', ' 1', '10', 'Detail drawing') a
([Project Country Code], [Project Number], [Unique Number], [Old dwg number], [Reuse], [Drawing no. seq],
[Size of drawing], [ISO Code], [Drawing Number], [Part List], [Revision1], [Type of Dwg code], [Type of Dwg Text])
SELECT [Path], [Drawing Number]
INTO #Smalltable
FROM (SELECT 'S:\Drawings\056\5664B.dwg', '5664B.dwg'
UNION ALL SELECT 'S:\Drawings\056\5665A.dwg', '5665A.dwg'
UNION ALL SELECT 'S:\Drawings\010-019\10340A.DWG', '10340A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10341.DWG', '10341.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10353.DWG', '10353.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10363.DWG', '10363.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10364.DWG', '10364.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10365.DWG', '10365.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10366.DWG', '10366.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10368.DWG', '10368.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10370.DWG', '10370.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10397H.DWG', '10397H.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10407.DWG', '10407.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10417A.DWG', '10417A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10423A.DWG', '10423A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10429.DWG', '10429.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10443E.DWG', '10443E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10445.DWG', '10445.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10448.DWG', '10448.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10449.DWG', '10449.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10461.DWG', '10461.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10462.DWG', '10462.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10469.DWG', '10469.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10470.DWG', '10470.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10473.DWG', '10473.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10474C.DWG', '10474C.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10475.DWG', '10475.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10495.DWG', '10495.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10507.DWG', '10507.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10508.DWG', '10508.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10509.DWG', '10509.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10532.DWG', '10532.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10533.DWG', '10533.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10534.DWG', '10534.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10535E.DWG', '10535E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10538A.DWG', '10538A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10539.DWG', '10539.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10540B.DWG', '10540B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10566.DWG', '10566.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10567.DWG', '10567.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10578.DWG', '10578.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10580.DWG', '10580.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10581A.DWG', '10581A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10582.DWG', '10582.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10605.DWG', '10605.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10617.DWG', '10617.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10633B.DWG', '10633B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10634B.DWG', '10634B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10635C.DWG', '10635C.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10637.DWG', '10637.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10659.DWG', '10659.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10673.DWG', '10673.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10674.DWG', '10674.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10675A.DWG', '10675A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10676B.DWG', '10676B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10677A.DWG', '10677A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10678.DWG', '10678.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10686.DWG', '10686.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10687A.DWG', '10687A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10688A.DWG', '10688A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10694.DWG', '10694.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10695A.DWG', '10695A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10696.DWG', '10696.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10699A.DWG', '10699A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10712.DWG', '10712.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10715.DWG', '10715.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10718.DWG', '10718.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10721.DWG', '10721.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10723.DWG', '10723.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10724.DWG', '10724.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10725.DWG', '10725.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10742.DWG', '10742.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10753.DWG', '10753.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10754.DWG', '10754.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10755.DWG', '10755.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10765.DWG', '10765.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10768.DWG', '10768.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10769.DWG', '10769.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10770.DWG', '10770.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10776.DWG', '10776.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10777.DWG', '10777.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10778E.DWG', '10778E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10783A.DWG', '10783A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10784B.DWG', '10784B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10785A.DWG', '10785A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10786A.DWG', '10786A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10787A.DWG', '10787A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10788A.DWG', '10788A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10789A.DWG', '10789A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10790A.DWG', '10790A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10791.DWG', '10791.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10792.DWG', '10792.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10793.DWG', '10793.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10794.DWG', '10794.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10795.DWG', '10795.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10796.DWG', '10796.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10797.DWG', '10797.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10798.DWG', '10798.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10799.DWG', '10799.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10800.DWG', '10800.DWG') a([Path], [Drawing Number])
Go
December 1, 2011 at 6:48 am
Define the "first occurrence". SQL is unordered sets of data, so there is no defined "first" unless specified by the code in an ORDER BY.
With the sample data you have posted, this works.
SELECT a.Path, b.*
INTO #ThirdTable
FROM #Smalltable a
INNER JOIN #Largetable b ON b.[Drawing Number] = REPLACE(a.[Drawing Number],'.DWG','')
SELECT * FROM #ThirdTable
But I know that isn't what you're after, so please go through the script that I produced for inserting your sample data into two tables, make the necessary corrections and then post it. Also include what your expected result is.
SELECT [Project Country Code], [Project Number], [Unique Number], [Old dwg number], [Reuse], [Drawing no. seq],
[Size of drawing], [ISO Code], [Drawing Number], [Part List], [Revision1], [Type of Dwg code], [Type of Dwg Text]
INTO #Largetable
FROM (SELECT 'NZE0267', 'NZE', '267', '3165', '1', '1', '10783', '1', 'A1', '10783', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3166', '1', '1', '10784', '1', 'A1', '10784', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3167', '1', '1', '10785', '1', 'A1', '10785', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3168', '1', '1', '10786', '1', 'A1', '10786', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3169', '1', '1', '10787', '1', 'A1', '10787', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3170', '1', '1', '10788', '1', 'A1', '10788', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3171', '1', '1', '10789', '1', 'A1', '10789', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3172', '1', '1', '10790', '1', 'A1', '10790', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3173', '1', '1', '10791', '1', 'A1', '10791', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3174', '1', '1', '10792', '1', 'A1', '10792', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3175', '1', '1', '10793', '1', 'A1', '10793', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3176', '1', '1', '10794', '1', 'A1', '10794', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3177', '1', '1', '10795', '1', 'A1', '10795', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3178', '1', '1', '10796', '1', 'A1', '10796', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3179', '1', '1', '10797', '1', 'A1', '10797', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3180', '1', '1', '10798', '2', 'A2', '10798', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3181', '1', '1', '10799', '2', 'A2', '10799', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3182', '1', '1', '10800', '3', 'A3', '10800', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3183', '1', '1', '10801', '1', 'A1', '10801', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3184', '1', '1', '10802', '2', 'A2', '10802', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3185', '1', '1', '10803', '2', 'A2', '10803', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3186', '1', '1', '10804', '2', 'A2', '10804', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3187', '1', '1', '10805', '3', 'A3', '10805', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3188', '1', '1', '10806', '3', 'A3', '10806', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3189', '1', '1', '10807', '3', 'A3', '10807', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3190', '1', '1', '10808', '3', 'A3', '10808', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3191', '1', '1', '10809', '3', 'A3', '10809', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3192', '1', '1', '10810', '3', 'A3', '10810', ' 1', '10', 'Detail drawing') a
([Project Country Code], [Project Number], [Unique Number], [Old dwg number], [Reuse], [Drawing no. seq],
[Size of drawing], [ISO Code], [Part List], [Drawing Number], [Revision1], [Type of Dwg code], [Type of Dwg Text])
SELECT [Path], [Drawing Number]
INTO #Smalltable
FROM (SELECT 'S:\Drawings\056\5664B.dwg', '5664B.dwg'
UNION ALL SELECT 'S:\Drawings\056\5665A.dwg', '5665A.dwg'
UNION ALL SELECT 'S:\Drawings\010-019\10340A.DWG', '10340A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10341.DWG', '10341.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10353.DWG', '10353.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10363.DWG', '10363.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10364.DWG', '10364.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10365.DWG', '10365.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10366.DWG', '10366.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10368.DWG', '10368.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10370.DWG', '10370.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10397H.DWG', '10397H.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10407.DWG', '10407.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10417A.DWG', '10417A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10423A.DWG', '10423A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10429.DWG', '10429.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10443E.DWG', '10443E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10445.DWG', '10445.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10448.DWG', '10448.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10449.DWG', '10449.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10461.DWG', '10461.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10462.DWG', '10462.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10469.DWG', '10469.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10470.DWG', '10470.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10473.DWG', '10473.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10474C.DWG', '10474C.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10475.DWG', '10475.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10495.DWG', '10495.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10507.DWG', '10507.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10508.DWG', '10508.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10509.DWG', '10509.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10532.DWG', '10532.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10533.DWG', '10533.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10534.DWG', '10534.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10535E.DWG', '10535E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10538A.DWG', '10538A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10539.DWG', '10539.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10540B.DWG', '10540B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10566.DWG', '10566.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10567.DWG', '10567.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10578.DWG', '10578.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10580.DWG', '10580.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10581A.DWG', '10581A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10582.DWG', '10582.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10605.DWG', '10605.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10617.DWG', '10617.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10633B.DWG', '10633B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10634B.DWG', '10634B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10635C.DWG', '10635C.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10637.DWG', '10637.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10659.DWG', '10659.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10673.DWG', '10673.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10674.DWG', '10674.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10675A.DWG', '10675A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10676B.DWG', '10676B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10677A.DWG', '10677A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10678.DWG', '10678.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10686.DWG', '10686.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10687A.DWG', '10687A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10688A.DWG', '10688A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10694.DWG', '10694.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10695A.DWG', '10695A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10696.DWG', '10696.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10699A.DWG', '10699A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10712.DWG', '10712.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10715.DWG', '10715.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10718.DWG', '10718.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10721.DWG', '10721.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10723.DWG', '10723.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10724.DWG', '10724.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10725.DWG', '10725.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10742.DWG', '10742.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10753.DWG', '10753.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10754.DWG', '10754.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10755.DWG', '10755.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10765.DWG', '10765.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10768.DWG', '10768.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10769.DWG', '10769.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10770.DWG', '10770.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10776.DWG', '10776.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10777.DWG', '10777.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10778E.DWG', '10778E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10783A.DWG', '10783A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10784B.DWG', '10784B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10785A.DWG', '10785A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10786A.DWG', '10786A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10787A.DWG', '10787A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10788A.DWG', '10788A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10789A.DWG', '10789A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10790A.DWG', '10790A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10791.DWG', '10791.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10792.DWG', '10792.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10793.DWG', '10793.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10794.DWG', '10794.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10795.DWG', '10795.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10796.DWG', '10796.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10797.DWG', '10797.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10798.DWG', '10798.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10799.DWG', '10799.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10800.DWG', '10800.DWG') a([Path], [Drawing Number])
December 3, 2011 at 5:04 am
Thanks for all the help so far.
I still don't have a solution.
If I run these queries:
SELECT [Project Country Code], [Project Number], [Unique Number], [Old dwg number], [Reuse], [Drawing no. seq],
[Size of drawing], [ISO Code], [Drawing Number], [Part List], [Revision1], [Type of Dwg code], [Type of Dwg Text]
INTO Largetable
FROM (SELECT 'NZE0267', 'NZE', '267', '3165', '1', '1', '10783', '1', 'A1', '10783', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3166', '1', '1', '10784', '1', 'A1', '10784', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3167', '1', '1', '10785', '1', 'A1', '10785', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3168', '1', '1', '10786', '1', 'A1', '10786', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3169', '1', '1', '10787', '1', 'A1', '10787', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3170', '1', '1', '10788', '1', 'A1', '10788', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3171', '1', '1', '10789', '1', 'A1', '10789', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3172', '1', '1', '10790', '1', 'A1', '10790', ' 1', '8', 'Flow diagram with PI-symbols'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3173', '1', '1', '10791', '1', 'A1', '10791', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3174', '1', '1', '10792', '1', 'A1', '10792', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3175', '1', '1', '10793', '1', 'A1', '10793', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3176', '1', '1', '10794', '1', 'A1', '10794', ' 1', '6', 'Flow diagram with service connections'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3177', '1', '1', '10795', '1', 'A1', '10795', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3178', '1', '1', '10796', '1', 'A1', '10796', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3179', '1', '1', '10797', '1', 'A1', '10797', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3180', '1', '1', '10798', '2', 'A2', '10798', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3181', '1', '1', '10799', '2', 'A2', '10799', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3182', '1', '1', '10800', '3', 'A3', '10800', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3183', '1', '1', '10801', '1', 'A1', '10801', ' 1', '19', 'Isometric drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3184', '1', '1', '10802', '2', 'A2', '10802', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3185', '1', '1', '10803', '2', 'A2', '10803', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3186', '1', '1', '10804', '2', 'A2', '10804', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3187', '1', '1', '10805', '3', 'A3', '10805', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3188', '1', '1', '10806', '3', 'A3', '10806', ' 1', '1', 'Lay out'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3189', '1', '1', '10807', '3', 'A3', '10807', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3190', '1', '1', '10808', '3', 'A3', '10808', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3191', '1', '1', '10809', '3', 'A3', '10809', ' 1', '10', 'Detail drawing'
UNION ALL SELECT 'NZE0267', 'NZE', '267', '3192', '1', '1', '10810', '3', 'A3', '10810', ' 1', '10', 'Detail drawing') a
([Project Country Code], [Project Number], [Unique Number], [Old dwg number], [Reuse], [Drawing no. seq],
[Size of drawing], [ISO Code], [Drawing Number], [Part List], [Revision1], [Type of Dwg code], [Type of Dwg Text])
SELECT [Path], [Drawing Number]
INTO Smalltable
FROM (SELECT 'S:\Drawings\056\5664B.dwg', '5664B.dwg'
UNION ALL SELECT 'S:\Drawings\056\5665A.dwg', '5665A.dwg'
UNION ALL SELECT 'S:\Drawings\010-019\10340A.DWG', '10340A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10341.DWG', '10341.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10353.DWG', '10353.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10363.DWG', '10363.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10364.DWG', '10364.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10365.DWG', '10365.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10366.DWG', '10366.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10368.DWG', '10368.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10370.DWG', '10370.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10397H.DWG', '10397H.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10407.DWG', '10407.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10417A.DWG', '10417A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10423A.DWG', '10423A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10429.DWG', '10429.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10443E.DWG', '10443E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10445.DWG', '10445.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10448.DWG', '10448.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10449.DWG', '10449.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10461.DWG', '10461.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10462.DWG', '10462.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10469.DWG', '10469.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10470.DWG', '10470.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10473.DWG', '10473.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10474C.DWG', '10474C.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10475.DWG', '10475.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10495.DWG', '10495.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10507.DWG', '10507.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10508.DWG', '10508.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10509.DWG', '10509.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10532.DWG', '10532.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10533.DWG', '10533.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10534.DWG', '10534.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10535E.DWG', '10535E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10538A.DWG', '10538A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10539.DWG', '10539.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10540B.DWG', '10540B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10566.DWG', '10566.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10567.DWG', '10567.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10578.DWG', '10578.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10580.DWG', '10580.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10581A.DWG', '10581A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10582.DWG', '10582.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10605.DWG', '10605.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10617.DWG', '10617.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10633B.DWG', '10633B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10634B.DWG', '10634B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10635C.DWG', '10635C.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10637.DWG', '10637.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10659.DWG', '10659.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10673.DWG', '10673.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10674.DWG', '10674.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10675A.DWG', '10675A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10676B.DWG', '10676B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10677A.DWG', '10677A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10678.DWG', '10678.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10686.DWG', '10686.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10687A.DWG', '10687A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10688A.DWG', '10688A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10694.DWG', '10694.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10695A.DWG', '10695A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10696.DWG', '10696.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10699A.DWG', '10699A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10712.DWG', '10712.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10715.DWG', '10715.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10718.DWG', '10718.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10721.DWG', '10721.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10723.DWG', '10723.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10724.DWG', '10724.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10725.DWG', '10725.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10742.DWG', '10742.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10753.DWG', '10753.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10754.DWG', '10754.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10755.DWG', '10755.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10765.DWG', '10765.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10768.DWG', '10768.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10769.DWG', '10769.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10770.DWG', '10770.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10776.DWG', '10776.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10777.DWG', '10777.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10778E.DWG', '10778E.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10783A.DWG', '10783A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10784B.DWG', '10784B.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10785A.DWG', '10785A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10786A.DWG', '10786A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10787A.DWG', '10787A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10788A.DWG', '10788A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10789A.DWG', '10789A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10790A.DWG', '10790A.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10791.DWG', '10791.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10792.DWG', '10792.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10793.DWG', '10793.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10794.DWG', '10794.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10795.DWG', '10795.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10796.DWG', '10796.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10797.DWG', '10797.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10798.DWG', '10798.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10799.DWG', '10799.DWG'
UNION ALL SELECT 'S:\Drawings\010-019\10800.DWG', '10800.DWG') a([Path], [Drawing Number])
I get 2 tables, Smalltable and Largetable
I want to filter Largetable with Smalltable, to produce a table of records from Largetable that have a common "Drawing Number",
and I only want the first occurrence of each Drawing Number - there are many repetitions of this field.
BUT - I want to add the Path from Smalltable to this third table. I hope this is clear.
December 3, 2011 at 5:33 am
No, it's not clear yet.
We still need a definition of "first occurence" as Cadavre already pointed out.
The following requirement is more than vague:
I want to add the Path from Smalltable to this third table.
What third table do you refer to?
What Path value are you looking for assuming there are more than one for the same drawing?
December 3, 2011 at 6:42 am
tmccar (12/3/2011)
I want to filter Largetable with Smalltable, to produce a table of records from Largetable that have a common "Drawing Number",and I only want the first occurrence of each Drawing Number - there are many repetitions of this field.
BUT - I want to add the Path from Smalltable to this third table. I hope this is clear.
In the sample data you posted, there are no instances where the value of the Drawing Number column in Largetable also exists in Smalltable. Are you sure that's the common key between the two tables? In fact, looking at the data, I'm wondering if you've got the right column names in the sample data for Largetable. For example, the values in the Size of Drawing column are the same as the Part List column, and appear to be a unique sequence of some sort, which definitely isn't what I'd expect to see in a column named Size of Drawing. In fact, I suspect the column names starting with "Drawing No. Seq." need to be all shifted to the right by one column; can't say for sure since I'm not familiar with your data, but that looks like it would make more sense to me. If I make that assumption, then the column currently known as Part List becomes Drawing Number, and the relationship between the two tables starts to make a lot more sense...I think...
December 3, 2011 at 8:46 am
You are correct Journeyman. The data in this table got corrupted somehow. I will try to post the correct data.
I'm not familiar with inserting Excel files into SQL so I'm going to look into it and post the relevant query here.
But the principle is the same - I have a large database of records which has one field, "Drawing Number" which matches a fileld in a smaller table. Using the smaller table, I want to filter the large one - the resulting table having all the fields from the large table, but one unique "Drawing Number" record plus the path from the small table.
OK, here goes - 2 tables, "Large" and "Small"
CREATE TABLE "Large" (
"Project_" varchar (7) NULL ,
"Country code" varchar (3) NULL ,
"Project number" varchar (4) NULL ,
"Unique number" char (6) NOT NULL ,
"Old dwg number" "tinyint" NULL ,
"Reuse" "tinyint" NULL ,
"Drawing num seq" varchar (6) NULL ,
"Size of drawing" char (1) NOT NULL ,
"ISO Code" varchar (2) NULL ,
"Drawing number" char (6) NOT NULL ,
)
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2814','GER','2810','0','34','0','2','2','1','2')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('0','0','0','59060','1','2','851430','4','A4','429')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ARG0325','ARG','325','29996','1','2','2836','3','A3','2836')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1401','IND','1401','10928','1','2','5017','4','A4','5017')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2388','GER','2388','19133','1','2','5017','4','A4','5017')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2447','GER','2447','23865','1','2','5017','4','A4','5017')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1588','IND','1588','25901','1','2','5017','4','A4','5017')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1568','IND','1568','33512','1','2','825612','4','A4','5017')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1764','IND','1764','33954','1','2','826077','4','A4','5017')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2259','GER','2259','17701','1','2','5151','4','A4','5151')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2259','GER','2259','17703','1','2','5664','4','A4','5664')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('NZE0292','NZE','292','20755','1','2','5664','4','A4','5664')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('NZE0310','NZE','310','35696','1','2','827843','4','A4','5664')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IDO0237','IDO','237','36531','1','2','828695','4','A4','5664')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('TAI0131','TAI','131','44123','1','2','836493','4','A4','5664')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('TYR0160','TYR','160','47171','1','2','839541','4','A4','5664')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('SVE0599','SVE','599','51008','1','2','843378','4','A4','5664')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2932','GER','2932','62543','1','2','854913','4','A4','5664')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2932','GER','2932','62448','1','2','854818','4','A4','5665')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('AUS0354','AUS','354','29449','1','2','5666','4','A4','5666')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ENG1692','ENG','1692','63717','1','2','856087','4','A4','5666')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('TWN0221','TWN','221','45166','1','2','837536','4','A4','5667')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1401','IND','1401','10930','1','2','6347','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('CHI0141','CHI','141','15087','1','2','6347','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ger2151','ger','2151','15871','1','2','6347','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ind1514','ind','1514','15944','1','2','6347','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ger2151','ger','2151','17110','1','2','6347','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2388','GER','2388','19136','1','2','6347','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1588','IND','1588','25902','1','2','6347','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1568','IND','1568','33513','1','2','825613','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1764','IND','1764','33955','1','2','826078','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('FUP0000','FUP','0','38048','1','2','830278','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('AUS0384','AUS','384','42118','1','2','834440','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('FUP0000','FUP','0','44929','1','2','837299','3','A3','6347')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('CHI0141','CHI','141','15080','1','2','6436','4','A4','6436')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ind1514','ind','1514','15945','1','2','6436','4','A4','6436')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2447','GER','2447','25067','1','2','6436','4','A4','6436')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ISR0219','ISR','219','35945','1','2','828094','4','A4','6436')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER1856','GER','1856','47926','1','2','840296','4','A4','6436')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ARG0327','ARG','327','35649','1','2','827796','4','A4','6671')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IDO0237','IDO','237','36395','1','2','828554','4','A4','6671')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ITA0979','ITA','979','41743','1','2','834058','4','A4','6671')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2505','GER','2505','42677','1','2','835017','4','A4','6671')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2259','GER','2259','17704','1','2','6831','4','A4','6831')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2259','GER','2259','18276','1','2','9223','4','A4','9223')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1401','IND','1401','10931','1','2','9304','1','A1','9304')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('chi0141','chi','141','15159','1','2','9304','1','A1','9304')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ger2151','ger','2151','15872','1','2','9304','1','A1','9304')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ind1514','ind','1514','15946','1','2','9304','1','A1','9304')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ger2151','ger','2151','17111','1','2','9304','1','A1','9304')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2388','GER','2388','19127','1','2','9304','1','A1','9304')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2447','GER','2447','23866','1','2','9304','1','A1','9304')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1588','IND','1588','25903','1','2','9304','1','A1','9304')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1568','IND','1568','33514','1','2','825614','1','A1','9304')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1764','IND','1764','33946','1','2','826069','1','A1','9304')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1401','IND','1401','10932','1','2','9305','1','A1','9305')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('CHI0141','CHI','141','15082','1','2','9305','1','A1','9305')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ger2151','ger','2151','15873','1','2','9305','1','A1','9305')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ind1514','ind','1514','15947','1','2','9305','1','A1','9305')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ger2151','ger','2151','17112','1','2','9305','1','A1','9305')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2388','GER','2388','19128','1','2','9305','1','A1','9305')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2447','GER','2447','23867','1','2','9305','1','A1','9305')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1588','IND','1588','25904','1','2','9305','1','A1','9305')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1568','IND','1568','33515','1','2','825615','1','A1','9305')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1764','IND','1764','33947','1','2','826070','1','A1','9305')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1401','IND','1401','10956','1','2','9307','1','A1','9307')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1588','IND','1588','25906','1','2','9307','1','A1','9307')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1568','IND','1568','33516','1','2','825616','1','A1','9307')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1764','IND','1764','33948','1','2','826071','1','A1','9307')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1401','IND','1401','10933','1','2','9308','1','A1','9308')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('CHI0141','CHI','141','15083','1','2','9308','1','A1','9308')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ger2151','ger','2151','15874','1','2','9308','1','A1','9308')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ind1514','ind','1514','15948','1','2','9308','1','A1','9308')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ger2151','ger','2151','17113','1','2','9308','1','A1','9308')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2388','GER','2388','19135','1','2','9308','1','A1','9308')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2447','GER','2447','23868','1','2','9308','1','A1','9308')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1588','IND','1588','25905','1','2','9308','1','A1','9308')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1568','IND','1568','33517','1','2','825617','1','A1','9308')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1764','IND','1764','33949','1','2','826072','1','A1','9308')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1401','IND','1401','10924','1','2','9328','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('CHI0141','CHI','141','15084','1','2','9328','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ger2151','ger','2151','15875','1','2','9328','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ind1514','ind','1514','15949','1','2','9328','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ger2151','ger','2151','17114','1','2','9328','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('FRA0964','FRA','964','17929','1','2','9328','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2388','GER','2388','19129','1','2','9328','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2447','GER','2447','23869','1','2','9328','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1588','IND','1588','25898','1','2','9328','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('GER2358','GER','2358','27999','1','2','819840','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1568','IND','1568','33518','1','2','825618','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IND1764','IND','1764','33950','1','2','826073','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('ISR0219','ISR','219','35940','1','2','828089','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('BAN0032','BAN','32','37351','1','2','829562','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('AUS0384','AUS','384','38041','1','2','830271','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('AUS0384','AUS','384','42111','1','2','834433','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('IRL0403','IRL','403','44922','1','2','837292','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('AUS0411','AUS','411','45036','1','2','837406','4','A4','9328')
INSERT INTO Large ([Project_], [Country Code], [Project Number],[Unique Number], [Old dwg number], [Reuse],[Drawing num seq],[Size of drawing],[ISO Code], [Drawing Number]) VALUES ('JPN0566','JPN','566','46895','1','2','839265','4','A4','9328')
CREATE TABLE "Small" (
"Path" varchar (255) NULL ,
"Drawing Number" char (6) NOT NULL ,
)
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','5664')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','5665')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10340')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10341')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10353')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10363')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10364')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10365')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10366')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10368')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10370')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10397')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10407')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10417')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10423')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10429')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10443')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10445')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10448')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10449')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10461')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10462')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10469')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10470')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10473')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10474')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10475')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10495')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10507')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10508')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10509')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10532')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10533')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10534')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10535')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10538')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10539')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10540')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10566')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10567')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10578')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10580')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10581')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10582')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10605')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10617')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10633')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10634')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10635')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10637')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10659')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10673')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10674')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10675')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10676')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10677')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10678')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10686')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10687')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10688')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10694')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10695')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10696')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10699')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10712')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10715')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10718')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10721')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10723')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10724')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10725')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10742')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10753')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10754')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10755')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10765')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10768')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10769')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10770')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10776')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10777')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10778')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10783')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10784')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10785')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10786')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10787')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10788')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10789')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10790')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10791')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10792')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10793')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10794')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10795')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10796')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10797')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10798')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10799')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10800')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10801')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10802')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10803')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10804')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10805')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10806')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10807')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10808')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10809')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10810')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10811')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10812')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10813')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10814')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10815')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10816')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10817')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10818')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10825')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10827')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10828')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10829')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10829')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10829')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10830')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10831')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10832')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10833')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10835')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10839')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10840')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10841')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10844')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10845')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10846')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10847')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10851')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10852')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10854')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10855')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10861')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10862')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10863')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10864')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10865')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10866')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10867')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10870')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10871')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10872')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10879')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10883')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10903')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10904')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10905')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10918')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10920')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10921')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10922')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10922')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10922')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10922')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10922')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10922')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10923')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10933')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10933')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10933')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10933')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10934')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10934')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10934')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10934')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10935')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10935')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10935')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10936')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10936')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10936')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10936')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10937')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10937')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10937')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10937')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10942')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10956')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10964')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10965')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10966')
INSERT INTO Small ([Path], [Drawing Number]) VALUES ('S:\Drawings\056\5664B.dwg','10968')
December 3, 2011 at 8:51 am
...The "first occurrence" will be the lowest value of "Project_" in "Large". I hope that's clear!
Tom
December 3, 2011 at 9:22 am
tmccar (12/3/2011)
...The "first occurrence" will be the lowest value of "Project_" in "Large". I hope that's clear!Tom
Yep, that helps a lot! Try this:
SELECT Large.*, Small.[Path]
FROM (
-- Create a derived table to return the first value of Project_ for each Drawing Number
SELECT [Drawing Number] AS [Drawing Number], MIN(Project_) AS Project_
FROM Large
GROUP BY [Drawing Number]
) AS drv INNER JOIN-- Limit the result set to Drawing Numbers that exist in Small
Small ON Small.[Drawing Number] = drv.[Drawing Number] INNER JOIN-- Join back to Large to get the remaining columns
Large ON Large.[Drawing Number] = drv.[Drawing Number] AND
Large.Project_ = drv.Project_-- Have to include Project_ in the join in order to get only the first row
Per the comments in the code above, the idea is to create a set that contains the lowest value of Project_ for each Drawing Number. Then you can join that to your Small table in order to filter out rows that only exist in Large and to get the value of Path. Finally, join that back to Large to get all the remaining columns.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply