· 7 years ago · Nov 16, 2018, 05:02 PM
1DECLARE @TempTableList TABLE
2(
3 TABLE_NAME varchar(50)
4)
5
6INSERT INTO @TempTableList (TABLE_NAME)
7SELECT TABLE_NAME
8FROM INFORMATION_SCHEMA.TABLES
9WHERE TABLE_NAME like 'Log_%'
10AND
11NOT TABLE_NAME = 'login_token'
12
13DECLARE @tName as Varchar(50);
14SELECT @tName = TABLE_NAME
15FROM @TempTableList
16
17IF OBJECT_ID('tempdb..#TempResultTable') IS NOT NULL DROP TABLE #TempResultTable
18CREATE TABLE #TempResultTable
19(
20 [RecordNumber] [int] NOT NULL,
21 [TrackID] [int] NOT NULL,
22 [Zipcode] [varchar](5) NOT NULL,
23 [CIN] [varchar](3) NOT NULL,
24 [DayField] [varchar](1) NOT NULL,
25 [AIN] [varchar](1) NOT NULL,
26 [StarShipBC] [varchar](10) NULL,
27 [MailType] [varchar](30) NOT NULL,
28 [Discharge] [varchar](2) NOT NULL,
29 [Container] [smallint] NULL,
30 [Disposition] [varchar](2) NOT NULL,
31 [Date_Processed] [datetime] NULL,
32 [Date_Discharged] [datetime] NULL,
33 [UniqueTypeID] [varchar](30) NULL,
34 [BinNum] [varchar](2) NULL,
35 [RouteName] [varchar](30) NULL,
36 [SchemeUsed] [varchar](25) NULL,
37 [Weight] [real] NULL,
38 [KeyedOrScanned] [smallint] NULL,
39 [BC24Digit] [varchar](24) NULL,
40 [TenDigitMorph] [bit] NULL,
41 [SPECSERV1] [varchar](40) NULL,
42 [SPECSERV2] [varchar](40) NULL,
43 [SPECSERV3] [varchar](40) NULL,
44 [SPECSERV4] [varchar](40) NULL,
45 [SystemType] [smallint] NULL,
46 [INDMode] [varchar](15) NULL,
47 [RecordTypeChoosen] [varchar](3) NULL,
48 [TC_Response] [varchar](450) NULL,
49 [Dimensions] [varchar](20) NULL
50)
51while exists (select 1 from @TempTableList)
52begin
53 select top 1 @tName = TABLE_NAME from @TempTableList
54
55 SET QUOTED_IDENTIFIER OFF;
56 EXEC(" INSERT INTO #TempResultTable
57 (
58 RecordNumber,
59 TrackID,
60 Zipcode,
61 CIN,
62 DayField,
63 AIN,
64 StarShipBC,
65 MailType,
66 Discharge,
67 Container,
68 Disposition,
69 Date_Processed,
70 Date_Discharged,
71 UniqueTypeID,
72 BinNum,
73 RouteName,
74 SchemeUsed,
75 Weight,
76 KeyedOrScanned,
77 BC24Digit,
78 TenDigitMorph,
79 SPECSERV1,
80 SPECSERV2,
81 SPECSERV3,
82 SPECSERV4,
83 SystemType,
84 INDMode,
85 RecordTypeChoosen,
86 TC_Response,
87 Dimensions)
88 SELECT
89 RecordNumber,
90 TrackID,
91 Zipcode,
92 CIN,
93 DayField,
94 AIN,
95 StarShipBC,
96 MailType,
97 Discharge,
98 Container,
99 Disposition,
100 Date_Processed,
101 Date_Discharged,
102 UniqueTypeID,
103 BinNum,
104 RouteName,
105 SchemeUsed,
106 Weight,
107 KeyedOrScanned,
108 BC24Digit,
109 TenDigitMorph,
110 SPECSERV1,
111 SPECSERV2,
112 SPECSERV3,
113 SPECSERV4,
114 SystemType,
115 INDMode,
116 RecordTypeChoosen,
117 TC_Response,
118 Dimensions
119 FROM " + @tName)
120 SET QUOTED_IDENTIFIER ON;
121 delete from @TempTableList where TABLE_NAME = @tName
122end
123
124SELECT
125Zipcode as Zip5,
126CIN as CIN3,
127DayField as Day1,
128AIN as AIN1,
129TenDigitMorph as Barcode,
130BC24Digit as Barcode24,
131Date_Discharged as [DateTime],
132RouteName as FamilyName,
133UniqueTypeID as ID,
134Date_Processed as Inducted,
135Discharge as LaneID,
136'MAIN' as ScannerArray,
137SchemeUsed as SchemeName,
138'1' as TrayType
139
140FROM #TempResultTable
141WHERE (Date_Discharged >= CONVERT(varchar, ?, 103) And Date_Discharged < CONVERT(varchar, ?, 103)) AND (Discharge<>10)
142ORDER BY Date_Discharged