November 15, 2008 at 4:32 am
Hi all,
I need to have a query which compares the fields of 2 tables with each other.
The naming of the fields in both tables are the same but TABLE_1 contains more fields than TABLE_2.
In other words: all fields that exist in TABLE_2 also exist (with the same fieldname) in TABLE_1 and I want to compare the values of the fields.
If the value of a certain field differs, I want to add a record to another table (TABLE_COMPARISON) in which I want to store: the name of the field that differs, the value of table 1 and the value of TABLE_2 which offcourse will differ from each other otherwise they won't be inserted in this table and offcourse a unique identifier.
The reason why there are 2 tables is the following:
we do data entry and sometimes we offshore the work.
They process everything and provide us with an export which we import into TABLE_2 in this example.
But we always perform quality control, for example 50% of all records will be entered ourselves and will be stored in TABLE_1.
This means that for one delivery you can find all records in TABLE_2 and 50% in TABLE_1 and then I want to compare the values.
For example
Fields in TABLE_1:
LSD_ID (unique identifier)
DOCUMENT_NR
DOCUMENT_NR_IS_CORRECTED
NAME
NAME_IS_CORRECTED
STREET
STREET_IS_CORRECTED
Fields in TABLE_2:
LSD_ID (unique identifier)
DOCUMENT_NR
NAME
STREET
As you can see the "IS_CORRECTED" fields don't exist in TABLE_2.
The field LSD_ID is the field with which you can link the tables (unique identifier).
Technically I want to compare the values of all fields of TABLE_2 and compare the values with the same fields as in TABLE_1.
For this example I can easily create a normal stored procedure, but in reality my table contains over 140 columns and I don't want to type them all.
That's why I created a cursor which gets all records of TABLE_1 (in order to have the records we entered ourselves).
Per record I have another cursor which enumerates all field of TABLE_2 and then I use a parametrized sp_executesql statement.
This is my code but it is not performant, can someone help me make it more efficient using the above example?
DECLARE @PDELIVERY VARCHAR(300)
SET @PDELIVERY = '831800'
DECLARE @PSTATUS INT
SET @PSTATUS = 1
DECLARE @VLKL_DOC_NR VARCHAR(300)
DECLARE @VLKL_LSD_ID VARCHAR(300)
DECLARE @VLKL_BARCODE VARCHAR(300)
DECLARE @VLKL_DOCUMENT_NR VARCHAR(300)
DECLARE @VLKL_LAYOUT_NR VARCHAR(300)
DECLARE @VLKL_DEB_NR VARCHAR(300)
DECLARE @VLKL_DATE VARCHAR(300)
DECLARE @VLKL_IS_EMPTY VARCHAR(300)
DECLARE @VT1 VARCHAR(300)
DECLARE @VT1_IS_CORRECTED VARCHAR(300)
DECLARE @VT2 VARCHAR(300)
DECLARE @VT2_IS_CORRECTED VARCHAR(300)
DECLARE @VT3 VARCHAR(300)
DECLARE @VT3_IS_CORRECTED VARCHAR(300)
DECLARE @vt4 VARCHAR(300)
DECLARE @VT4_IS_CORRECTED VARCHAR(300)
DECLARE @VT5 VARCHAR(300)
DECLARE @VT5_IS_CORRECTED VARCHAR(300)
DECLARE @VT6 VARCHAR(300)
DECLARE @VT6_IS_CORRECTED VARCHAR(300)
DECLARE @VT7 VARCHAR(300)
DECLARE @VT7_IS_CORRECTED VARCHAR(300)
DECLARE @VT8 VARCHAR(300)
DECLARE @VT8_IS_CORRECTED VARCHAR(300)
DECLARE @VT9 VARCHAR(300)
DECLARE @VT9_IS_CORRECTED VARCHAR(300)
DECLARE @VT10 VARCHAR(300)
DECLARE @VT10_IS_CORRECTED VARCHAR(300)
DECLARE @VT11 VARCHAR(300)
DECLARE @VT11_IS_CORRECTED VARCHAR(300)
DECLARE @VT12 VARCHAR(300)
DECLARE @VT12_IS_CORRECTED VARCHAR(300)
DECLARE @VT13 VARCHAR(300)
DECLARE @VT13_IS_CORRECTED VARCHAR(300)
DECLARE @VT14 VARCHAR(300)
DECLARE @VT14_IS_CORRECTED VARCHAR(300)
DECLARE @VT15 VARCHAR(300)
DECLARE @VT15_IS_CORRECTED VARCHAR(300)
DECLARE @VT16 VARCHAR(300)
DECLARE @VT16_IS_CORRECTED VARCHAR(300)
DECLARE @VT17 VARCHAR(300)
DECLARE @VT17_IS_CORRECTED VARCHAR(300)
DECLARE @VT18 VARCHAR(300)
DECLARE @VT18_IS_CORRECTED VARCHAR(300)
DECLARE @VT19 VARCHAR(300)
DECLARE @VT19_IS_CORRECTED VARCHAR(300)
DECLARE @VT20 VARCHAR(300)
DECLARE @VT20_IS_CORRECTED VARCHAR(300)
DECLARE @VLKL_G1 VARCHAR(300)
DECLARE @VLKL_G2 VARCHAR(300)
DECLARE @VLKL_G3 VARCHAR(300)
DECLARE @VLKL_G4 VARCHAR(300)
DECLARE @VLKL_G5 VARCHAR(300)
DECLARE @VLKL_G6 VARCHAR(300)
DECLARE @VLKL_G7 VARCHAR(300)
DECLARE @VLKL_G8 VARCHAR(300)
DECLARE @VLKL_G9 VARCHAR(300)
DECLARE @VLKL_G10 VARCHAR(300)
DECLARE @VLKL_R1 VARCHAR(300)
DECLARE @VLKL_R2 VARCHAR(300)
DECLARE @VLKL_R3 VARCHAR(300)
DECLARE @VLKL_R4 VARCHAR(300)
DECLARE @VLKL_R5 VARCHAR(300)
DECLARE @VLKL_R6 VARCHAR(300)
DECLARE @VLKL_R7 VARCHAR(300)
DECLARE @VLKL_R8 VARCHAR(300)
DECLARE @VLKL_R9 VARCHAR(300)
DECLARE @VLKL_R10 VARCHAR(300)
DECLARE @VLKL_R11 VARCHAR(300)
DECLARE @VLKL_R12 VARCHAR(300)
DECLARE @VLKL_R13 VARCHAR(300)
DECLARE @VLKL_R14 VARCHAR(300)
DECLARE @VLKL_R15 VARCHAR(300)
DECLARE @VLKL_R16 VARCHAR(300)
DECLARE @VLKL_R17 VARCHAR(300)
DECLARE @VLKL_R18 VARCHAR(300)
DECLARE @VLKL_R19 VARCHAR(300)
DECLARE @VLKL_R20 VARCHAR(300)
DECLARE @vb1 VARCHAR(300)
DECLARE @VB1_IS_CORRECTED VARCHAR(300)
DECLARE @VB2 VARCHAR(300)
DECLARE @VB2_IS_CORRECTED VARCHAR(300)
DECLARE @VB3 VARCHAR(300)
DECLARE @VB3_IS_CORRECTED VARCHAR(300)
DECLARE @VB4 VARCHAR(300)
DECLARE @VB4_IS_CORRECTED VARCHAR(300)
DECLARE @VLKL_BVK_1 VARCHAR(300)
DECLARE @VLKL_BVK_2 VARCHAR(300)
DECLARE @VLKL_BVK_3 VARCHAR(300)
DECLARE @VLKL_BVK_4 VARCHAR(300)
DECLARE @VLKL_BVK_5 VARCHAR(300)
DECLARE @VLKL_BLK_1 VARCHAR(300)
DECLARE @VLKL_BLK_2 VARCHAR(300)
DECLARE @VLKL_BLK_3 VARCHAR(300)
DECLARE @VLKL_BLK_4 VARCHAR(300)
DECLARE @VLKL_BLK_5 VARCHAR(300)
DECLARE @VLKL_BLF_1 VARCHAR(300)
DECLARE @VO1 VARCHAR(300)
DECLARE @VO1_IS_CORRECTED VARCHAR(300)
DECLARE @VO2 VARCHAR(300)
DECLARE @VO2_IS_CORRECTED VARCHAR(300)
DECLARE @VO3 VARCHAR(300)
DECLARE @VO3_IS_CORRECTED VARCHAR(300)
DECLARE @VO4 VARCHAR(300)
DECLARE @VO4_IS_CORRECTED VARCHAR(300)
DECLARE @VO5 VARCHAR(300)
DECLARE @VO5_IS_CORRECTED VARCHAR(300)
DECLARE @VLKL_OVK_1 VARCHAR(300)
DECLARE @VLKL_OVK_2 VARCHAR(300)
DECLARE @VLKL_OVK_3 VARCHAR(300)
DECLARE @VLKL_OVK_4 VARCHAR(300)
DECLARE @VLKL_OVK_5 VARCHAR(300)
DECLARE @VLKL_OVK_6 VARCHAR(300)
DECLARE @VF1 VARCHAR(300)
DECLARE @VF1_IS_CORRECTED VARCHAR(300)
DECLARE @VF2 VARCHAR(300)
DECLARE @VF2_IS_CORRECTED VARCHAR(300)
DECLARE @VF3 VARCHAR(300)
DECLARE @VF3_IS_CORRECTED VARCHAR(300)
DECLARE @VF4 VARCHAR(300)
DECLARE @VF4_IS_CORRECTED VARCHAR(300)
DECLARE @VF5 VARCHAR(300)
DECLARE @VF5_IS_CORRECTED VARCHAR(300)
DECLARE @VF6 VARCHAR(300)
DECLARE @VF6_IS_CORRECTED VARCHAR(300)
DECLARE @VLKL_FVK_1 VARCHAR(300)
DECLARE @VLKL_FVK_2 VARCHAR(300)
DECLARE @VLKL_FVK_3 VARCHAR(300)
DECLARE @VLKL_FVK_4 VARCHAR(300)
DECLARE @VLKL_FVK_5 VARCHAR(300)
DECLARE @VLKL_FVK_6 VARCHAR(300)
DECLARE @VLKL_FLK_1 VARCHAR(300)
DECLARE @VLKL_FLK_2 VARCHAR(300)
DECLARE @VLKL_FLK_3 VARCHAR(300)
DECLARE @VLKL_FLK_4 VARCHAR(300)
DECLARE @VLKL_FLK_5 VARCHAR(300)
DECLARE @VLKL_FLK_6 VARCHAR(300)
DECLARE @VLKL_FLF_1 VARCHAR(300)
DECLARE @VLKL_FLF_5 VARCHAR(300)
DECLARE @VLKL_OMR1 VARCHAR(300)
DECLARE @VLKL_OMR2 VARCHAR(300)
DECLARE @VLKL_BATCH_NAME VARCHAR(300)
DECLARE @VLKL_IMAGE_NAME VARCHAR(300)
DECLARE @VLKL_IS_EXPORTED VARCHAR(300)
DECLARE @VLKL_DATE_FIRST_CREATED VARCHAR(300)
DECLARE @VLKL_DATE_LAST_MODIFIED VARCHAR(300)
DECLARE @VIMAGE_FORMATTED VARCHAR(300)
DECLARE @VFIELD_SP VARCHAR(300)
DECLARE @VVALUE_SP VARCHAR(300)
DECLARE @VVALUE_SPBE VARCHAR(300)
DECLARE @vsql NVARCHAR(4000)
DECLARE @PRESULT VARCHAR(300)
DELETE FROM DBO.COMPARISON WHERE LKC_DELIVERY = @PDELIVERY
DECLARE VCURSOR CURSOR FOR
SELECT --GET ALL VALIDATED RECORDS
[LKL_DOC_NR],[LKL_LSD_ID],[LKL_BARCODE],[LKL_DOCUMENT_NR],[LKL_LAYOUT_NR],[LKL_DEB_NR],[LKL_DATE],[LKL_IS_EMPTY],[T1],[T1_IS_CORRECTED],[T2],[T2_IS_CORRECTED],[T3],[T3_IS_CORRECTED],[T4],[T4_IS_CORRECTED],[T5],[T5_IS_CORRECTED],[T6],[T6_IS_CORRECTED],[T7],[T7_IS_CORRECTED],[T8],[T8_IS_CORRECTED],[T9],[T9_IS_CORRECTED],[T10],[T10_IS_CORRECTED],[T11],[T11_IS_CORRECTED],[T12],[T12_IS_CORRECTED],[T13],[T13_IS_CORRECTED],[T14],[T14_IS_CORRECTED],[T15],[T15_IS_CORRECTED],[T16],[T16_IS_CORRECTED],[T17],[T17_IS_CORRECTED],[T18],[T18_IS_CORRECTED],[T19],[T19_IS_CORRECTED],[T20],[T20_IS_CORRECTED],[LKL_G1],[LKL_G2],[LKL_G3],[LKL_G4],[LKL_G5],[LKL_G6],[LKL_G7],[LKL_G8],[LKL_G9],[LKL_G10],[LKL_R1],[LKL_R2],[LKL_R3],[LKL_R4],[LKL_R5],[LKL_R6],[LKL_R7],[LKL_R8],[LKL_R9],[LKL_R10],[LKL_R11],[LKL_R12],[LKL_R13],[LKL_R14],[LKL_R15],[LKL_R16],[LKL_R17],[LKL_R18],[LKL_R19],[LKL_R20],[B1],[B1_IS_CORRECTED],[B2],[B2_IS_CORRECTED],[B3],[B3_IS_CORRECTED],[B4],[B4_IS_CORRECTED],[LKL_BVK_1],[LKL_BVK_2],[LKL_BVK_3],[LKL_BVK_4],[LKL_BVK_5],[LKL_BLK_1],[LKL_BLK_2],[LKL_BLK_3],[LKL_BLK_4],[LKL_BLK_5],[LKL_BLF_1],[O1],[O1_IS_CORRECTED],[O2],[O2_IS_CORRECTED],[O3],[O3_IS_CORRECTED],[O4],[O4_IS_CORRECTED],[O5],[O5_IS_CORRECTED],[LKL_OVK_1],[LKL_OVK_2],[LKL_OVK_3],[LKL_OVK_4],[LKL_OVK_5],[LKL_OVK_6],[F1],[F1_IS_CORRECTED],[F2],[F2_IS_CORRECTED],[F3],[F3_IS_CORRECTED],[F4],[F4_IS_CORRECTED],[F5],[F5_IS_CORRECTED],[F6],[F6_IS_CORRECTED],[LKL_FVK_1],[LKL_FVK_2],[LKL_FVK_3],[LKL_FVK_4],[LKL_FVK_5],[LKL_FVK_6],[LKL_FLK_1],[LKL_FLK_2],[LKL_FLK_3],[LKL_FLK_4],[LKL_FLK_5],[LKL_FLK_6],[LKL_FLF_1],[LKL_FLF_5],[LKL_OMR1],[LKL_OMR2],[LKL_BATCH_NAME],[LKL_IMAGE],[LKL_IS_EXPORTED],[LKL_DATE_FIRST_CREATED],[LKL_DATE_LAST_MODIFIED]
FROM
DBO.INDEX
WHERE
LKL_IS_EXPORTED = @PSTATUS AND
SUBSTRING(LKL_BATCH_NAME,1 ,6) = @PDELIVERY
OPEN VCURSOR
FETCH NEXT FROM VCURSOR INTO @VLKL_DOC_NR , @VLKL_LSD_ID ,@VLKL_BARCODE ,@VLKL_DOCUMENT_NR ,@VLKL_LAYOUT_NR ,@VLKL_DEB_NR ,@VLKL_DATE ,@VLKL_IS_EMPTY ,@VT1 ,@VT1_IS_CORRECTED ,@VT2 ,@VT2_IS_CORRECTED ,@VT3 ,@VT3_IS_CORRECTED ,@VT4 ,@VT4_IS_CORRECTED ,@VT5 ,@VT5_IS_CORRECTED ,@VT6 ,@VT6_IS_CORRECTED ,@VT7 ,@VT7_IS_CORRECTED ,@VT8 ,@VT8_IS_CORRECTED ,@VT9 ,@VT9_IS_CORRECTED ,@VT10 ,@VT10_IS_CORRECTED ,@VT11 ,@VT11_IS_CORRECTED ,@VT12 ,@VT12_IS_CORRECTED ,@VT13 ,@VT13_IS_CORRECTED ,@VT14 ,@VT14_IS_CORRECTED ,@VT15 ,@VT15_IS_CORRECTED ,@VT16 ,@VT16_IS_CORRECTED ,@VT17 ,@VT17_IS_CORRECTED ,@VT18 ,@VT18_IS_CORRECTED ,@VT19 ,@VT19_IS_CORRECTED ,@VT20 ,@VT20_IS_CORRECTED ,@VLKL_G1 ,@VLKL_G2 ,@VLKL_G3 ,@VLKL_G4 ,@VLKL_G5 ,@VLKL_G6 ,@VLKL_G7 ,@VLKL_G8 ,@VLKL_G9 ,@VLKL_G10 ,@VLKL_R1 ,@VLKL_R2 ,@VLKL_R3 ,@VLKL_R4 ,@VLKL_R5 ,@VLKL_R6 ,@VLKL_R7 ,@VLKL_R8 ,@VLKL_R9 ,@VLKL_R10 ,@VLKL_R11 ,@VLKL_R12 ,@VLKL_R13 ,@VLKL_R14 ,@VLKL_R15 ,@VLKL_R16 ,@VLKL_R17 ,@VLKL_R18 ,@VLKL_R19 ,@VLKL_R20 ,@VB1 ,@VB1_IS_CORRECTED ,@VB2 ,@VB2_IS_CORRECTED ,@VB3 ,@VB3_IS_CORRECTED ,@VB4 ,@VB4_IS_CORRECTED ,@VLKL_BVK_1 ,@VLKL_BVK_2 ,@VLKL_BVK_3 ,@VLKL_BVK_4 ,@VLKL_BVK_5 ,@VLKL_BLK_1 ,@VLKL_BLK_2 ,@VLKL_BLK_3 ,@VLKL_BLK_4 ,@VLKL_BLK_5 ,@VLKL_BLF_1 ,@VO1 ,@VO1_IS_CORRECTED ,@VO2 ,@VO2_IS_CORRECTED ,@VO3 ,@VO3_IS_CORRECTED ,@VO4 ,@VO4_IS_CORRECTED ,@VO5 ,@VO5_IS_CORRECTED ,@VLKL_OVK_1 ,@VLKL_OVK_2 ,@VLKL_OVK_3 ,@VLKL_OVK_4 ,@VLKL_OVK_5 ,@VLKL_OVK_6 ,@VF1 ,@VF1_IS_CORRECTED ,@VF2 ,@VF2_IS_CORRECTED ,@VF3 ,@VF3_IS_CORRECTED ,@VF4 ,@VF4_IS_CORRECTED ,@VF5 ,@VF5_IS_CORRECTED ,@VF6 ,@VF6_IS_CORRECTED ,@VLKL_FVK_1 ,@VLKL_FVK_2 ,@VLKL_FVK_3 ,@VLKL_FVK_4 ,@VLKL_FVK_5 ,@VLKL_FVK_6 ,@VLKL_FLK_1 ,@VLKL_FLK_2 ,@VLKL_FLK_3 ,@VLKL_FLK_4 ,@VLKL_FLK_5 ,@VLKL_FLK_6 ,@VLKL_FLF_1 ,@VLKL_FLF_5 ,@VLKL_OMR1 ,@VLKL_OMR2 ,@VLKL_BATCH_NAME ,@VLKL_IMAGE_NAME ,@VLKL_IS_EXPORTED ,@VLKL_DATE_FIRST_CREATED ,@VLKL_DATE_LAST_MODIFIED
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @VIMAGE_FORMATTED = (SELECT LSD_IMAGE_NAME FROM DBO.SCAN_DATA WHERE LSD_ID = @VLKL_LSD_ID)
print 'LSD_ID: ' + @vlkl_LSD_ID
print 'IMAGE: ' + @vimage_formatted
DECLARE VCURSORFIELDS CURSOR FOR
SELECT
NAME
FROM
SYS.COLUMNS
WHERE
OBJECT_ID = 1574348723 AND
NAME
NOT IN ('LKL_DOC_NR', 'LKL_BATCH_NAME', 'LKL_IMAGE_NAME', 'LKL_IS_EXPORTED', 'LKL_DATE_FIRST_CREATED', 'LKL_DATE_LAST_MODIFIED')
ORDER BY
COLUMN_ID
OPEN VCURSORFIELDS
FETCH NEXT FROM VCURSORFIELDS INTO @VFIELD_SP
WHILE (@@FETCH_STATUS = 0)
BEGIN
--GET VALUE OFFSHORE
SET @vsql =
N'SELECT @PRESULT_INTERNAL = ' + @VFIELD_SP
+N' FROM DBO.INDEX_SP '
+N'WHERE LKL_LSD_ID = @PLSD_ID_INTERNAL'
EXEC sp_executesql
,N'@PLSD_ID_INTERNAL INT, @PRESULT_INTERNAL VARCHAR(300) OUTPUT'
,@PLSD_ID_INTERNAL = @VLKL_LSD_ID
,@PRESULT_INTERNAL = @PRESULT OUTPUT
SELECT @VVALUE_SP = @PRESULT
print 'VALUE_SP: ' + @VVALUE_SP
--GET OUR VALUE
SET @vsql =
N'SELECT @PRESULT_INTERNAL = ' + @VFIELD_SP
+N' FROM DBO.INDEX '
+N'WHERE LKL_LSD_ID = @PLSD_ID_INTERNAL'
EXEC sp_executesql
,N'@PLSD_ID_INTERNAL INT, @PRESULT_INTERNAL VARCHAR(300) OUTPUT'
,@PLSD_ID_INTERNAL = @VLKL_LSD_ID
,@PRESULT_INTERNAL = @PRESULT OUTPUT
SELECT @VVALUE_SPBE = @PRESULT
print 'VALUE_SPBE: ' + @VVALUE_SPBE
IF @VVALUE_SP <> @VVALUE_SPBE BEGIN
PRINT 'DIFF'
INSERT INTO DBO.COMPARISON
(LKC_DELIVERY, LKC_LSD_ID, LKC_FIELD, LKC_VALUE_SP, LKC_VALUE_CORRECT, LKC_IMAGE)
VALUES
(@PDELIVERY, @VLKL_LSD_ID, @VFIELD_SP, @VVALUE_SP, @VVALUE_SPBE, @VIMAGE_FORMATTED)
END
FETCH NEXT FROM VCURSORFIELDS INTO @VFIELD_SP
END
CLOSE VCURSORFIELDS
DEALLOCATE VCURSORFIELDS
FETCH NEXT FROM VCURSOR INTO @VLKL_DOC_NR , @VLKL_LSD_ID ,@VLKL_BARCODE ,@VLKL_DOCUMENT_NR ,@VLKL_LAYOUT_NR ,@VLKL_DEB_NR ,@VLKL_DATE ,@VLKL_IS_EMPTY ,@VT1 ,@VT1_IS_CORRECTED ,@VT2 ,@VT2_IS_CORRECTED ,@VT3 ,@VT3_IS_CORRECTED ,@VT4 ,@VT4_IS_CORRECTED ,@VT5 ,@VT5_IS_CORRECTED ,@VT6 ,@VT6_IS_CORRECTED ,@VT7 ,@VT7_IS_CORRECTED ,@VT8 ,@VT8_IS_CORRECTED ,@VT9 ,@VT9_IS_CORRECTED ,@VT10 ,@VT10_IS_CORRECTED ,@VT11 ,@VT11_IS_CORRECTED ,@VT12 ,@VT12_IS_CORRECTED ,@VT13 ,@VT13_IS_CORRECTED ,@VT14 ,@VT14_IS_CORRECTED ,@VT15 ,@VT15_IS_CORRECTED ,@VT16 ,@VT16_IS_CORRECTED ,@VT17 ,@VT17_IS_CORRECTED ,@VT18 ,@VT18_IS_CORRECTED ,@VT19 ,@VT19_IS_CORRECTED ,@VT20 ,@VT20_IS_CORRECTED ,@VLKL_G1 ,@VLKL_G2 ,@VLKL_G3 ,@VLKL_G4 ,@VLKL_G5 ,@VLKL_G6 ,@VLKL_G7 ,@VLKL_G8 ,@VLKL_G9 ,@VLKL_G10 ,@VLKL_R1 ,@VLKL_R2 ,@VLKL_R3 ,@VLKL_R4 ,@VLKL_R5 ,@VLKL_R6 ,@VLKL_R7 ,@VLKL_R8 ,@VLKL_R9 ,@VLKL_R10 ,@VLKL_R11 ,@VLKL_R12 ,@VLKL_R13 ,@VLKL_R14 ,@VLKL_R15 ,@VLKL_R16 ,@VLKL_R17 ,@VLKL_R18 ,@VLKL_R19 ,@VLKL_R20 ,@VB1 ,@VB1_IS_CORRECTED ,@VB2 ,@VB2_IS_CORRECTED ,@VB3 ,@VB3_IS_CORRECTED ,@VB4 ,@VB4_IS_CORRECTED ,@VLKL_BVK_1 ,@VLKL_BVK_2 ,@VLKL_BVK_3 ,@VLKL_BVK_4 ,@VLKL_BVK_5 ,@VLKL_BLK_1 ,@VLKL_BLK_2 ,@VLKL_BLK_3 ,@VLKL_BLK_4 ,@VLKL_BLK_5 ,@VLKL_BLF_1 ,@VO1 ,@VO1_IS_CORRECTED ,@VO2 ,@VO2_IS_CORRECTED ,@VO3 ,@VO3_IS_CORRECTED ,@VO4 ,@VO4_IS_CORRECTED ,@VO5 ,@VO5_IS_CORRECTED ,@VLKL_OVK_1 ,@VLKL_OVK_2 ,@VLKL_OVK_3 ,@VLKL_OVK_4 ,@VLKL_OVK_5 ,@VLKL_OVK_6 ,@VF1 ,@VF1_IS_CORRECTED ,@VF2 ,@VF2_IS_CORRECTED ,@VF3 ,@VF3_IS_CORRECTED ,@VF4 ,@VF4_IS_CORRECTED ,@VF5 ,@VF5_IS_CORRECTED ,@VF6 ,@VF6_IS_CORRECTED ,@VLKL_FVK_1 ,@VLKL_FVK_2 ,@VLKL_FVK_3 ,@VLKL_FVK_4 ,@VLKL_FVK_5 ,@VLKL_FVK_6 ,@VLKL_FLK_1 ,@VLKL_FLK_2 ,@VLKL_FLK_3 ,@VLKL_FLK_4 ,@VLKL_FLK_5 ,@VLKL_FLK_6 ,@VLKL_FLF_1 ,@VLKL_FLF_5 ,@VLKL_OMR1 ,@VLKL_OMR2 ,@VLKL_BATCH_NAME ,@VLKL_IMAGE_NAME ,@VLKL_IS_EXPORTED ,@VLKL_DATE_FIRST_CREATED ,@VLKL_DATE_LAST_MODIFIED
END
CLOSE VCURSOR
DEALLOCATE VCURSOR
November 16, 2008 at 9:56 pm
hi,
please try this.
select distinct A.A, case when A.A = B.B then B.B else 'null' end as B from
(
select COLUMN_NAME as A from
information_schema.columns
--pass first table name
where Table_Name='Orders'
)a ,
(
select COLUMN_NAME As B from
information_schema.columns
-- pass second table name
where Table_Name='Product'
)b group by A.A,B
in this query returns the table columns.
thnx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply