Filter Large table using small one

Question

Post reply

Filter Large table using small one

tmccar

SSC Eights!

Points: 990
More actions
December 1, 2011 at 1:24 am

#247724

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 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

GER2259 GER 2259 17703 1 2 5664 4 A4 5664 1 10 Detail drawing

NZE0292 NZE 292 20755 1 2 5664 4 A4 5664 1 10 Detail drawing

NZE0310 NZE 310 35696 1 2 827843 4 A4 5664 1 10 Detail drawing

IDO0237 IDO 237 36531 1 2 828695 4 A4 5664 2 1 10 Detail drawing

TAI0131 TAI 131 44123 1 2 836493 4 A4 5664 2 1 10 Detail drawing

TYR0160 TYR 160 47171 1 2 839541 4 A4 5664 2 1 10 Detail drawing

SVE0599 SVE 599 51008 1 2 843378 4 A4 5664 2 1 10 Detail drawing

GER2932 GER 2932 62543 1 2 854913 4 A4 5664 2 1 10 Detail drawing

GER2932 GER 2932 62448 1 2 854818 4 A4 5665 2 1 10 Detail drawing

231 1 1 10461 3 A3 10461 11 Chart, graph, nomogram

czk0503 czk 503 232 1 1 10462 1 A1 10462 10 Detail drawing

POL0427 POL 427 239 1 1 10469 3 A3 10469 10 Detail drawing

czk0503 czk 503 240 1 1 10470 1 A1 10470 10 Detail drawing

DAN0893 DAN 893 243 1 1 10473 1 A1 10473 4 Flow diagram

czk0503 czk 503 244 1 1 10474 1 A1 10474 3 6 Flow diagram with service connections

NZE0273 NZE 273 245 1 1 10475 2 A2 10475 8 Flow diagram with PI-symbols

POL0467 POL 467 1387 1 1 10495 1 A1 10495 1 8 Flow diagram with PI-symbols

POL0427 POL 427 1419 1 1 10507 1 A1 10507 1 6 Flow diagram with service connections

POL0427 POL 427 1420 1 1 10508 1 A1 10508 1 6 Flow diagram with service connections

POL0427 POL 427 1421 1 1 10509 1 A1 10509 1 6 Flow diagram with service connections

POL0427 POL 427 1441 1 1 10532 3 A3 10532 1 9 Assembly drawing

1442 1 1 10533 1 A1 10533 1 4 Flow diagram

UNG0700 UNG 700 1443 1 1 10534 1 A1 10534 1 4 Flow diagram

ENG1226 ENG 1226 1444 1 1 10535 1 A1 10535 1 4 Flow diagram

1445 1 1 10538 1 A1 10538 1 4 Flow diagram

UNG0700 UNG 700 1446 1 1 10539 1 A1 10539 1 4 Flow diagram

AUS0309 AUS 309 1447 1 1 10540 1 A1 10540 1 4 Flow diagram

1672 1 1 10566 1 A1 10566 1 4 Flow diagram

1673 1 1 10567 1 A1 10567 1 4 Flow diagram

SVE0562 SVE 562 1737 1 1 10578 3 A3 10578 1 4 Flow diagram

NEP0006 NEP 6 1739 1 1 10580 1 A1 10580 1 4 Flow diagram

RUS0461 RUS 461 1740 1 1 10581 1 A1 10581 1 4 Flow diagram

RUS0461 RUS 461 1741 1 1 10582 1 A1 10582 1 4 Flow diagram

DAN0822 DAN 822 1838 1 1 10605 1 A1 10605 1 8 Flow diagram with PI-symbols

GER2077 GER 2077 1850 1 1 10617 1 A1 10617 1 4 Flow diagram

ENG1226 ENG 1226 2013 1 1 10633 1 A1 10633 1 9 Assembly drawing

ENG1226 ENG 1226 2014 1 1 10634 1 A1 10634 1 9 Assembly drawing

ENG1226 ENG 1226 2015 1 1 10635 1 A1 10635 1 9 Assembly drawing

ENG1226 ENG 1226 2017 1 1 10637 2 A2 10637 1 9 Assembly drawing

IRL0319 IRL 319 2039 1 1 10659 1 A1 10659 1 4 Flow diagram

AUS0309 AUS 309 2300 1 1 10673 1 A1 10673 1 1 Lay out

RUS0461 RUS 461 2301 1 1 10674 1 A1 10674 1 4 Flow diagram

2302 1 1 10675 1 A1 10675 1 9 Assembly drawing

2310 1 1 10676 1 A1 10676 1 10 Detail drawing

2311 1 1 10677 1 A1 10677 1 10 Detail drawing

RUS0461 RUS 461 2312 1 1 10678 1 A1 10678 1 1 Lay out

IRL3191 IRL 3191 2390 1 1 10686 1 A1 10686 1

IRL3192 IRL 3192 2391 1 1 10687 1 A1 10687 1

2392 1 1 10688 1 A1 10688 1

2398 1 1 10694 2 A2 10694 1

DAN0814 DAN 814 2399 1 1 10695 1 A1 10695 1 8 Flow diagram with PI-symbols

DAN0814 DAN 814 2400 1 1 10696 1 A1 10696 1 8 Flow diagram with PI-symbols

ENG1226 ENG 1226 2403 1 1 10699 4 A4 10699 1

DAN0814 DAN 814 2416 1 1 10712 1 A1 10712 1 1 Lay out

ENG1226 ENG 1226 2419 1 1 10715 1 A1 10715 1 10 Detail drawing

ENG1226 ENG 1226 2444 1 1 10718 4 A4 10718 1 10 Detail drawing

AUS0309 AUS 309 2447 1 1 10721 1 A1 10721 1 1 Lay out

ENG1226 ENG 1226 2449 1 1 10723 1 A1 10723 1 8 Flow diagram with PI-symbols

TWN0154 TWN 154 2450 1 1 10724 1 A1 10724 1 4 Flow diagram

TWN0154 TWN 154 2451 1 1 10725 1 A1 10725 1 1 Lay out

ENG1226 ENG 1226 2657 1 1 10742 1 A1 10742 1 8 Flow diagram with PI-symbols

ENG1226 ENG 1226 2681 1 1 10753 1 A1 10753 1 13 Pipe diagram

ENG1226 ENG 1226 2684 1 1 10754 1 A1 10754 1 13 Pipe diagram

ENG1226 ENG 1226 2687 1 1 10755 1 A1 10755 1 13 Pipe diagram

RUM0321 RUM 321 2715 1 1 10765 1 A1 10765 1 8 Flow diagram with PI-symbols

IRA0086 IRA 86 2794 1 1 10768 1 A1 10768 1 4 Flow diagram

IRA0086 IRA 86 2795 1 1 10769 1 A1 10769 1 4 Flow diagram

FIN0151 FIN 151 2796 1 1 10770 1 A1 10770 1 4 Flow diagram

SKO0329 SKO 329 2800 1 1 10776 1 A1 10776 1 8 Flow diagram with PI-symbols

czk0513 czk 513 3159 1 1 10777 1 A1 10777 1 8 Flow diagram with PI-symbols

ENG1226 ENG 1226 3160 1 1 10778 1 A1 10778 1 8 Flow diagram with PI-symbols

NZE0267 NZE 267 3165 1 1 10783 1 A1 10783 1 1 Lay out

NZE0267 NZE 267 3166 1 1 10784 1 A1 10784 1 8 Flow diagram with PI-symbols

NZE0267 NZE 267 3167 1 1 10785 1 A1 10785 1 8 Flow diagram with PI-symbols

NZE0267 NZE 267 3168 1 1 10786 1 A1 10786 1 8 Flow diagram with PI-symbols

NZE0267 NZE 267 3169 1 1 10787 1 A1 10787 1 8 Flow diagram with PI-symbols

NZE0267 NZE 267 3170 1 1 10788 1 A1 10788 1 8 Flow diagram with PI-symbols

NZE0267 NZE 267 3171 1 1 10789 1 A1 10789 1 8 Flow diagram with PI-symbols

NZE0267 NZE 267 3172 1 1 10790 1 A1 10790 1 8 Flow diagram with PI-symbols

NZE0267 NZE 267 3173 1 1 10791 1 A1 10791 1 6 Flow diagram with service connections

NZE0267 NZE 267 3174 1 1 10792 1 A1 10792 1 6 Flow diagram with service connections

NZE0267 NZE 267 3175 1 1 10793 1 A1 10793 1 6 Flow diagram with service connections

NZE0267 NZE 267 3176 1 1 10794 1 A1 10794 1 6 Flow diagram with service connections

NZE0267 NZE 267 3177 1 1 10795 1 A1 10795 1 19 Isometric drawing

NZE0267 NZE 267 3178 1 1 10796 1 A1 10796 1 19 Isometric drawing

NZE0267 NZE 267 3179 1 1 10797 1 A1 10797 1 19 Isometric drawing

NZE0267 NZE 267 3180 1 1 10798 2 A2 10798 1 19 Isometric drawing

NZE0267 NZE 267 3181 1 1 10799 2 A2 10799 1 19 Isometric drawing

NZE0267 NZE 267 3182 1 1 10800 3 A3 10800 1 19 Isometric drawing

NZE0267 NZE 267 3183 1 1 10801 1 A1 10801 1 19 Isometric drawing

NZE0267 NZE 267 3184 1 1 10802 2 A2 10802 1 1 Lay out

NZE0267 NZE 267 3185 1 1 10803 2 A2 10803 1 1 Lay out

NZE0267 NZE 267 3186 1 1 10804 2 A2 10804 1 1 Lay out

NZE0267 NZE 267 3187 1 1 10805 3 A3 10805 1 1 Lay out

NZE0267 NZE 267 3188 1 1 10806 3 A3 10806 1 1 Lay out

NZE0267 NZE 267 3189 1 1 10807 3 A3 10807 1 10 Detail drawing

NZE0267 NZE 267 3190 1 1 10808 3 A3 10808 1 10 Detail drawing

NZE0267 NZE 267 3191 1 1 10809 3 A3 10809 1 10 Detail drawing

NZE0267 NZE 267 3192 1 1 10810 3 A3 10810 1 10 Detail 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.dwg 5664B.dwg

S:\Drawings\056\5665A.dwg 5665A.dwg

S:\Drawings\010-019\10340A.DWG 10340A.DWG

S:\Drawings\010-019\10341.DWG 10341.DWG

S:\Drawings\010-019\10353.DWG 10353.DWG

S:\Drawings\010-019\10363.DWG 10363.DWG

S:\Drawings\010-019\10364.DWG 10364.DWG

S:\Drawings\010-019\10365.DWG 10365.DWG

S:\Drawings\010-019\10366.DWG 10366.DWG

S:\Drawings\010-019\10368.DWG 10368.DWG

S:\Drawings\010-019\10370.DWG 10370.DWG

S:\Drawings\010-019\10397H.DWG 10397H.DWG

S:\Drawings\010-019\10407.DWG 10407.DWG

S:\Drawings\010-019\10417A.DWG 10417A.DWG

S:\Drawings\010-019\10423A.DWG 10423A.DWG

S:\Drawings\010-019\10429.DWG 10429.DWG

S:\Drawings\010-019\10443E.DWG 10443E.DWG

S:\Drawings\010-019\10445.DWG 10445.DWG

S:\Drawings\010-019\10448.DWG 10448.DWG

S:\Drawings\010-019\10449.DWG 10449.DWG

S:\Drawings\010-019\10461.DWG 10461.DWG

S:\Drawings\010-019\10462.DWG 10462.DWG

S:\Drawings\010-019\10469.DWG 10469.DWG

S:\Drawings\010-019\10470.DWG 10470.DWG

S:\Drawings\010-019\10473.DWG 10473.DWG

S:\Drawings\010-019\10474C.DWG 10474C.DWG

S:\Drawings\010-019\10475.DWG 10475.DWG

S:\Drawings\010-019\10495.DWG 10495.DWG

S:\Drawings\010-019\10507.DWG 10507.DWG

S:\Drawings\010-019\10508.DWG 10508.DWG

S:\Drawings\010-019\10509.DWG 10509.DWG

S:\Drawings\010-019\10532.DWG 10532.DWG

S:\Drawings\010-019\10533.DWG 10533.DWG

S:\Drawings\010-019\10534.DWG 10534.DWG

S:\Drawings\010-019\10535E.DWG 10535E.DWG

S:\Drawings\010-019\10538A.DWG 10538A.DWG

S:\Drawings\010-019\10539.DWG 10539.DWG

S:\Drawings\010-019\10540B.DWG 10540B.DWG

S:\Drawings\010-019\10566.DWG 10566.DWG

S:\Drawings\010-019\10567.DWG 10567.DWG

S:\Drawings\010-019\10578.DWG 10578.DWG

S:\Drawings\010-019\10580.DWG 10580.DWG

S:\Drawings\010-019\10581A.DWG 10581A.DWG

S:\Drawings\010-019\10582.DWG 10582.DWG

S:\Drawings\010-019\10605.DWG 10605.DWG

S:\Drawings\010-019\10617.DWG 10617.DWG

S:\Drawings\010-019\10633B.DWG 10633B.DWG

S:\Drawings\010-019\10634B.DWG 10634B.DWG

S:\Drawings\010-019\10635C.DWG 10635C.DWG

S:\Drawings\010-019\10637.DWG 10637.DWG

S:\Drawings\010-019\10659.DWG 10659.DWG

S:\Drawings\010-019\10673.DWG 10673.DWG

S:\Drawings\010-019\10674.DWG 10674.DWG

S:\Drawings\010-019\10675A.DWG 10675A.DWG

S:\Drawings\010-019\10676B.DWG 10676B.DWG

S:\Drawings\010-019\10677A.DWG 10677A.DWG

S:\Drawings\010-019\10678.DWG 10678.DWG

S:\Drawings\010-019\10686.DWG 10686.DWG

S:\Drawings\010-019\10687A.DWG 10687A.DWG

S:\Drawings\010-019\10688A.DWG 10688A.DWG

S:\Drawings\010-019\10694.DWG 10694.DWG

S:\Drawings\010-019\10695A.DWG 10695A.DWG

S:\Drawings\010-019\10696.DWG 10696.DWG

S:\Drawings\010-019\10699A.DWG 10699A.DWG

S:\Drawings\010-019\10712.DWG 10712.DWG

S:\Drawings\010-019\10715.DWG 10715.DWG

S:\Drawings\010-019\10718.DWG 10718.DWG

S:\Drawings\010-019\10721.DWG 10721.DWG

S:\Drawings\010-019\10723.DWG 10723.DWG

S:\Drawings\010-019\10724.DWG 10724.DWG

S:\Drawings\010-019\10725.DWG 10725.DWG

S:\Drawings\010-019\10742.DWG 10742.DWG

S:\Drawings\010-019\10753.DWG 10753.DWG

S:\Drawings\010-019\10754.DWG 10754.DWG

S:\Drawings\010-019\10755.DWG 10755.DWG

S:\Drawings\010-019\10765.DWG 10765.DWG

S:\Drawings\010-019\10768.DWG 10768.DWG

S:\Drawings\010-019\10769.DWG 10769.DWG

S:\Drawings\010-019\10770.DWG 10770.DWG

S:\Drawings\010-019\10776.DWG 10776.DWG

S:\Drawings\010-019\10777.DWG 10777.DWG

S:\Drawings\010-019\10778E.DWG 10778E.DWG

S:\Drawings\010-019\10783A.DWG 10783A.DWG

S:\Drawings\010-019\10784B.DWG 10784B.DWG

S:\Drawings\010-019\10785A.DWG 10785A.DWG

S:\Drawings\010-019\10786A.DWG 10786A.DWG

S:\Drawings\010-019\10787A.DWG 10787A.DWG

S:\Drawings\010-019\10788A.DWG 10788A.DWG

S:\Drawings\010-019\10789A.DWG 10789A.DWG

S:\Drawings\010-019\10790A.DWG 10790A.DWG

S:\Drawings\010-019\10791.DWG 10791.DWG

S:\Drawings\010-019\10792.DWG 10792.DWG

S:\Drawings\010-019\10793.DWG 10793.DWG

S:\Drawings\010-019\10794.DWG 10794.DWG

S:\Drawings\010-019\10795.DWG 10795.DWG

S:\Drawings\010-019\10796.DWG 10796.DWG

S:\Drawings\010-019\10797.DWG 10797.DWG

S:\Drawings\010-019\10798.DWG 10798.DWG

S:\Drawings\010-019\10799.DWG 10799.DWG

S:\Drawings\010-019\10800.DWG 10800.DWG

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

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

tmccar SSC Eights! Points: 990 More actions · Answer 2

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.

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 3

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

tmccar (12/1/2011)

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.

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

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

tmccar SSC Eights! Points: 990 More actions · Answer 5

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" 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.

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

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.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

tmccar SSC Eights! Points: 990 More actions · Answer 7

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 8

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])

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

tmccar SSC Eights! Points: 990 More actions · Answer 9

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.

LutzM SSC Guru Points: 107049 More actions · Answer 10

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?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

JonFox SSCrazy Points: 2157 More actions · Answer 11

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...

tmccar SSC Eights! Points: 990 More actions · Answer 12

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'	)

tmccar SSC Eights! Points: 990 More actions · Answer 13

tmccar

SSC Eights!

Points: 990

December 3, 2011 at 8:51 am

#1416311

...The "first occurrence" will be the lowest value of "Project_" in "Large". I hope that's clear!

Tom

JonFox SSCrazy Points: 2157 More actions · Answer 14

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.