Dynamic SQL - Matching Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic SQL - Matching Columns

Author

Topic

matt.ryles
Starting Member

3 Posts

Posted - 2009-11-10 : 10:14:31

Hi,

Im new around here, but will prob spend a bit of time here. I have started working with SQL, but I am very new in the field.

I have two tables that need comparing, the problem is the column names are different. Now I have been informed I can complete this using a loop and dynamic sql.

I can do loop statements in SQL, but from what I have read about Dynamic SQL I really don't see how this will help me. Can somebody please point me in the right direction.

Regards

Matt

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-10 : 10:48:49

can you post table structure and some data example?

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

matt.ryles
Starting Member

3 Posts

Posted - 2009-11-10 : 11:02:44

Sorry I can't really post data. But here is the structure, I need everything in table One, be crossed checked in Table two and if its different it needs to be displayed in the query. I am really puzzled on how to do this, got to a point where my head is hurting. Doesn't help suffering from lack of sleep.
The thing is a query like this would be useful in the future also. So any help would be really appriciated.

Table One:
table_name ordinal_position column_name data_type is_nullable character_maximum_length
UKMaster 1 SAP-Number float YES NULL
UKMaster 2 Address Number float YES NULL
UKMaster 3 Order Block (AUFSD) nvarchar YES 255
UKMaster 4 SFDC Number nvarchar YES 255
UKMaster 5 VAT Number nvarchar YES 255
UKMaster 6 Name 1 nvarchar YES 255
UKMaster 7 Name 2 float YES NULL
UKMaster 8 Name 4 nvarchar YES 255
UKMaster 9 City nvarchar YES 255
UKMaster 10 Postal Code nvarchar YES 255
UKMaster 11 Postal Code PO Box float YES NULL
UKMaster 12 Po Box Number float YES NULL
UKMaster 13 Po Box City float YES NULL
UKMaster 14 Po Box Region float YES NULL
UKMaster 15 Po Box Country float YES NULL
UKMaster 16 Street nvarchar YES 255
UKMaster 17 Street Number float YES NULL
UKMaster 18 Country nvarchar YES 255
UKMaster 19 Language nvarchar YES 255
UKMaster 20 Region float YES NULL
UKMaster 21 Phone Number nvarchar YES 255
UKMaster 22 Fax Number nvarchar YES 255
UKMaster 23 Industry nvarchar YES 255
UKMaster 24 Customer Group float YES NULL
UKMaster 25 Sales District nvarchar YES 255
UKMaster 26 Currency nvarchar YES 255
UKMaster 27 ABC nvarchar YES 255
UKMaster 28 Payment Term nvarchar YES 255
UKMaster 29 Sales Group nvarchar YES 255
UKMaster 30 Sales Office nvarchar YES 255
UKMaster 31 EKP Number float YES NULL
UKMaster 32 Credit Limit float YES NULL
UKMaster 33 Sales Employee float YES NULL
UKMaster 34 First Name nvarchar YES 255
UKMaster 35 Last Name nvarchar YES 255
UKMaster 36 Code nvarchar YES 255

Table 2:
table_name ordinal_position column_name data_type is_nullable character_maximum_length
Account 1 Account_Lock_Down__c varchar NO 5
Account 2 Account_Sub_Category__c nvarchar YES 255
Account 3 AccountID_10__c nvarchar YES 30
Account 4 AccountNumber nvarchar YES 40
Account 5 Additional_Number__c nvarchar YES 20
Account 6 Agency_Id__c nvarchar YES 10
Account 7 AM_Product_Customer_del__c ntext YES 1073741823
Account 8 Approved_Prospect__c varchar NO 5
Account 9 BIK_code__c nvarchar YES 7
Account 10 Bus_Activity__c ntext YES 1073741823
Account 11 Business_Relationship__c nvarchar YES 255
Account 12 Collection_Notes__c nvarchar YES 255
Account 13 Count_Set_Up_Form__c decimal YES NULL
Account 14 CreatedById nchar YES 18
Account 15 CreatedDate datetime NO NULL
Account 16 CurrencyIsoCode nvarchar YES 3
Account 17 Current_Supplier__c ntext YES 1073741823
Account 18 Customer_Service_Resp__c nvarchar YES 255
Account 19 Description ntext YES 1073741823
Account 20 DM_Product_Customer_del__c ntext YES 1073741823
Account 21 Fax nvarchar YES 40
Account 22 GKA_ID__c nvarchar YES 6
Account 23 Global_Key_Account__c nchar YES 18
Account 24 Global_Mail_Region__c nvarchar YES 255
Account 25 GM_Business__c varchar NO 5
Account 26 GM_Direct__c varchar NO 5
Account 27 GM_Publication__c varchar NO 5
Account 28 Id nchar NO 18
Account 29 IF_AccountID__c nvarchar YES 1300
Account 30 IF_City__c nvarchar YES 40
Account 31 IF_Classification__c nvarchar YES 255
Account 32 IF_Country__c nvarchar YES 255
Account 33 IF_Credit_Block__c varchar NO 5
Account 34 IF_Credit_Limit__c decimal YES NULL
Account 35 IF_Currency__c nvarchar YES 255
Account 36 IF_CustomerGroup__c nvarchar YES 255
Account 37 IF_EKP__c nvarchar YES 10
Account 38 IF_Email__c nvarchar YES 80
Account 39 IF_Fax__c nvarchar YES 30
Account 40 IF_Language__c nvarchar YES 255
Account 41 IF_MIRR_NetRevenuePrevYear__c decimal YES NULL
Account 42 IF_MIRR_NetRevenueYTD__c decimal YES NULL
Account 43 IF_Name2__c nvarchar YES 40
Account 44 IF_Name3__c nvarchar YES 40
Account 45 IF_Name4__c nvarchar YES 40
Account 46 IF_Name__c nvarchar YES 1300
Account 47 IF_POBox_City__c nvarchar YES 40
Account 48 IF_POBox_State_Prov__c nvarchar YES 255
Account 49 IF_POBoxCountry__c nvarchar YES 255
Account 50 IF_POBoxNumber__c nvarchar YES 10
Account 51 IF_POBoxPostalCode__c nvarchar YES 10
Account 52 IF_PostalCode__c nvarchar YES 10
Account 53 IF_ProductType__c nvarchar YES 255
Account 54 IF_Region__c nvarchar YES 255
Account 55 IF_SalesDistrict__c nvarchar YES 255
Account 56 IF_SalesGroup__c nvarchar YES 255
Account 57 IF_SalesOffice__c nvarchar YES 255
Account 58 IF_SAP__c nvarchar YES 10
Account 59 IF_SAPBatch__c varchar NO 5
Account 60 IF_SIC__c nvarchar YES 40
Account 61 IF_Street__c nvarchar YES 60
Account 62 IF_StreetNo__c nvarchar YES 10
Account 63 IF_VAT__c nvarchar YES 20
Account 64 ILM_Debtor_number__c nvarchar YES 15
Account 65 ILM_Product__c varchar NO 5
Account 66 Industry nvarchar YES 80
Account 67 IsDeleted varchar NO 5
Account 68 Known_Shipper_Form__c datetime YES NULL
Account 69 KSMS_Registry_ID__c nvarchar YES 55
Account 70 KvK_code__c nvarchar YES 16
Account 71 KWP__c nvarchar YES 10
Account 72 Last_Modified__c datetime YES NULL
Account 73 LastActivityDate datetime YES NULL
Account 74 LastModifiedById nchar YES 18
Account 75 LastModifiedDate datetime NO NULL
Account 76 Lead_Source__c nvarchar YES 255
Account 77 Mailer_ID__c nvarchar YES 9
Account 78 Mark_for_account_cloning__c varchar NO 5
Account 79 MasterRecordId nchar YES 18
Account 80 Move_Update_Status__c nvarchar YES 255
Account 81 NA_Account_Invoice_via__c ntext YES 1073741823
Account 82 NA_Account_Name_Shipping__c nvarchar YES 65
Account 83 NA_Average_lbs_piece__c decimal YES NULL
Account 84 NA_Average_oz_piece__c decimal YES NULL
Account 85 NA_Competitor__c nvarchar YES 255
Account 86 NA_Credit_Date__c datetime YES NULL
Account 87 NA_Credit_Notes__c nvarchar YES 255
Account 88 NA_Credit_Status__c nvarchar YES 255
Account 89 NA_Current_Vendor__c ntext YES 1073741823
Account 90 NA_DBA_or_AKA__c nvarchar YES 25
Account 91 NA_DHL_Express_Number__c nvarchar YES 50
Account 92 NA_DHL_EZ_Return__c nvarchar YES 50
Account 93 NA_FDA__c nvarchar YES 255
Account 94 NA_Feedback_Comments__c ntext YES 1073741823
Account 95 NA_If_Inactive_why__c nvarchar YES 255
Account 96 NA_Inactive__c datetime YES NULL
Account 97 NA_Key_Account__c ntext YES 1073741823
Account 98 NA_Legacy_Worldmail_Number__c nvarchar YES 5
Account 99 NA_Mailing_Frequency__c ntext YES 1073741823
Account 100 NA_Mailing_Region__c ntext YES 1073741823
Account 101 NA_Mailing_Volume__c nvarchar YES 255
Account 102 NA_New_Account_Form_Rec_d__c datetime YES NULL
Account 103 NA_Payment_Method__c nvarchar YES 255
Account 104 NA_Product_Group__c ntext YES 1073741823
Account 105 NA_QP_Alpha_Code__c nvarchar YES 4
Account 106 NA_QP_Client_ID__c nvarchar YES 18
Account 107 NA_QP_SAP_Number__c nvarchar YES 15
Account 108 NA_SAP_ST_Number__c nvarchar YES 7
Account 109 NA_SmartCharge_Customer__c varchar NO 5
Account 110 NA_TSA_Approved__c nvarchar YES 255
Account 111 NA_TSA_Web_Site__c nvarchar YES 255
Account 112 NA_Type_of_Domestic_Sent_Items__c ntext YES 1073741823
Account 113 NA_Type_of_International_Sent_Items__c ntext YES 1073741823
Account 114 NA_WM_Phase__c nvarchar YES 255
Account 115 Name nvarchar NO 255
Account 116 Name3__c nvarchar YES 40
Account 117 Name4__c nvarchar YES 40
Account 118 Net_Revenue_YTD__c decimal YES NULL
Account 119 NetRevenuePrevYear__c decimal YES NULL
Account 120 NL_Qua_dom_bus_mail__c nvarchar YES 25
Account 121 NL_Quan_int_bus_mail__c nvarchar YES 25
Account 122 NL_Quantity_domestic_dm__c nvarchar YES 25
Account 123 NL_Quantity_international_dm__c nvarchar YES 25
Account 124 NumberOfEmployees int YES NULL
Account 125 OwnerId nchar NO 18
Account 126 ParentId nchar YES 18
Account 127 Payment_Terms__c nvarchar YES 255
Account 128 Phone nvarchar YES 40
Account 129 Potential_GM_Mail__c decimal YES NULL
Account 130 Potential_GM_Other_Product__c decimal YES NULL
Account 131 Potential_GM_Parcel__c decimal YES NULL
Account 132 Potential_GM_Plus__c decimal YES NULL
Account 133 Potential_nonGerman_Cross_Border__c decimal YES NULL
Account 134 PotentialGMBusiness__c decimal YES NULL
Account 135 PotentialGMDirect__c decimal YES NULL
Account 136 PotentialGMParcels__c decimal YES NULL
Account 137 PotentialGMPublication__c decimal YES NULL
Account 138 Rating nvarchar YES 40
Account 139 RecordTypeId nchar YES 18
Account 140 Reference_Account_Number__c nvarchar YES 10
Account 141 Servedby__c ntext YES 1073741823
Account 142 Shipping_Contract__c datetime YES NULL
Account 143 ShippingCity nvarchar YES 40
Account 144 ShippingCountry nvarchar YES 40
Account 145 ShippingPostalCode nvarchar YES 20
Account 146 ShippingState nvarchar YES 20
Account 147 ShippingStreet nvarchar YES 255
Account 148 Sic nvarchar YES 20
Account 149 Site nvarchar YES 80
Account 150 SMN_Accessible_Pot_EUR__c decimal YES NULL
Account 151 SMN_Debtor_Number_del__c nvarchar YES 12
Account 152 SMN_Non_Acc_CP24hrs_del__c decimal YES NULL
Account 153 SMN_Non_Acc_CP_24_78hrs__c decimal YES NULL
Account 154 SMN_Total_Volume__c decimal YES NULL
Account 155 SystemModstamp datetime NO NULL
Account 156 TargetMarketGeneral__c nvarchar YES 255
Account 157 Test_TWA__c nvarchar YES 10
Account 158 TickerSymbol nvarchar YES 20
Account 159 Total_Realistic_Potential__c decimal YES NULL
Account 160 Type nvarchar YES 40
Account 161 Type_Detail__c nvarchar YES 255
Account 162 UK_Express_Region__c nvarchar YES 255
Account 163 UK_Express_Rep_Number__c nvarchar YES 5
Account 164 UK_Express_Service_Centre__c ntext YES 1073741823
Account 165 UnId__c nvarchar YES 18
Account 166 UsePOBoxAddress__c nvarchar YES 255
Account 167 VATNumber__c nvarchar YES 40
Account 168 Website nvarchar YES 255
Account 169 x2ndCustResp__c nchar YES 18

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-10 : 11:29:05

at the very least you need a mapping of the columns from tableA to tableB

Do you know how to created DDL

What version SQL Server are you using

Are the tables in same database, different database, or on different servers?

Let us know

I'm sure we can help you out

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

matt.ryles
Starting Member

3 Posts

Posted - 2009-11-10 : 11:36:25

Thanks man, I been told its possible using sp_executesql, but anyway is a good way. Im really stuck, I have no clue how they expect me do it using the Dynamic SQL at all.

Im using 2005, they are in the same database.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-10 : 12:14:33

1 tables has 30+ columns, the other has over 100

Unless you have a mapping of the coluns, how are you going to do a compare?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources