Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing to table from another table 1

Status
Not open for further replies.
Halliarse

Halliarse

IS-IT--Management
Jan 8, 2007
213
GB
Hi

I'm using MS MSQL 2012.

I'm hoping this should be relatively straightforward and just an issue due to my SQL inexperience!!!

I have 2 versions of the same table on different databases, however,the 2nd table has additional columns. One of these additional columns does not allow nulls which is where my problem occurs!

I'm running the following script

INSERT into CTS..SL_Accounts([CUCODE]
,[CUNAME]
,[CUADDRESS]
,[CUPOSTCODE]
,[CUPHONE]
,[CUPHONE2]
,[CUFAX]
,[CUPROSCODE]
,[CUCONTACT]
,[CU_MULTI_CURR]
,[CUSALUTE]
,[CU_PRO_CONDATE]
,[CU_EXPORT_CODE]
,[CU_COUNTRY_CODE]
,[CU_VAT_REG_NO]
,[CU_EC_DELIVERY]
,[CU_EC_T_NATURE]
,[CU_EC_T_MODE]
,[CUBALANCE]
,[CUBALANCE_C]
,[CUAGED_1]
,[CUAGED_2]
,[CUAGED_3]
,[CUAGED_4]
,[CUAGED_UNALLOC]
,[CUSORT]
,[CUUSER1]
,[CUUSER2]
,[CUUSER3]
,[CUUSED]
,[CUTURNOVERPTD]
,[CUTURNOVR_L1]
,[CUTURNOVR_L2]
,[CUTURNOVR_L3]
,[CUTURNOVR_L4]
,[CUTURNOVR_L5]
,[CUTURNOVR_L6]
,[CUTURNOVR_L7]
,[CUTURNOVR_L8]
,[CUTURNOVR_L9]
,[CUTURNOVR_L10]
,[CUTURNOVR_L11]
,[CUTURNOVR_L12]
,[CUTURNOVR_L13]
,[CUTURNOVR_C1]
,[CUTURNOVR_C2]
,[CUTURNOVR_C3]
,[CUTURNOVR_C4]
,[CUTURNOVR_C5]
,[CUTURNOVR_C6]
,[CUTURNOVR_C7]
,[CUTURNOVR_C8]
,[CUTURNOVR_C9]
,[CUTURNOVR_C10]
,[CUTURNOVR_C11]
,[CUTURNOVR_C12]
,[CUTURNOVR_C13]
,[CUTURNOVR_O1]
,[CUTURNOVR_O2]
,[CUTURNOVR_O3]
,[CUTURNOVR_O4]
,[CUTURNOVR_O5]
,[CUTURNOVR_O6]
,[CUTURNOVR_O7]
,[CUTURNOVR_O8]
,[CUTURNOVR_O9]
,[CUTURNOVR_O10]
,[CUTURNOVR_O11]
,[CUTURNOVR_O12]
,[CUTURNOVR_O13]
,[CUTURNOVERYTD]
,[CUCURRENCYCODE]
,[CUTURNOVR_L1_C]
,[CUTURNOVR_L2_C]
,[CUTURNOVR_L3_C]
,[CUTURNOVR_L4_C]
,[CUTURNOVR_L5_C]
,[CUTURNOVR_L6_C]
,[CUTURNOVR_L7_C]
,[CUTURNOVR_L8_C]
,[CUTURNOVR_L9_C]
,[CUTURNOVR_L10_C]
,[CUTURNOVR_L11_C]
,[CUTURNOVR_L12_C]
,[CUTURNOVR_L13_C]
,[CUTURNOVR_C1_C]
,[CUTURNOVR_C2_C]
,[CUTURNOVR_C3_C]
,[CUTURNOVR_C4_C]
,[CUTURNOVR_C5_C]
,[CUTURNOVR_C6_C]
,[CUTURNOVR_C7_C]
,[CUTURNOVR_C8_C]
,[CUTURNOVR_C9_C]
,[CUTURNOVR_C10_C]
,[CUTURNOVR_C11_C]
,[CUTURNOVR_C12_C]
,[CUTURNOVR_C13_C]
,[CUTURNOVR_O1_C]
,[CUTURNOVR_O2_C]
,[CUTURNOVR_O3_C]
,[CUTURNOVR_O4_C]
,[CUTURNOVR_O5_C]
,[CUTURNOVR_O6_C]
,[CUTURNOVR_O7_C]
,[CUTURNOVR_O8_C]
,[CUTURNOVR_O9_C]
,[CUTURNOVR_O10_C]
,[CUTURNOVR_O11_C]
,[CUTURNOVR_O12_C]
,[CUTURNOVR_O13_C]
,[CUTURNOVR_YTD_C]
,[CUTURNOVR_PTD_C]
,[CU_COSTVAL_1]
,[CU_COSTVAL_2]
,[CU_COSTVAL_3]
,[CU_COSTVAL_4]
,[CU_COSTVAL_5]
,[CU_COSTVAL_6]
,[CU_COSTVAL_7]
,[CU_COSTVAL_8]
,[CU_COSTVAL_9]
,[CU_COSTVAL_10]
,[CU_COSTVAL_11]
,[CU_COSTVAL_12]
,[CU_COSTVAL_13]
,[CU_SALEVAL_1]
,[CU_SALEVAL_2]
,[CU_SALEVAL_3]
,[CU_SALEVAL_4]
,[CU_SALEVAL_5]
,[CU_SALEVAL_6]
,[CU_SALEVAL_7]
,[CU_SALEVAL_8]
,[CU_SALEVAL_9]
,[CU_SALEVAL_10]
,[CU_COSTVAL_PTD]
,[CU_COSTVAL_YTD]
,[CU_SALEVAL_PTD]
,[CU_SALEVAL_YTD]
,[CU_COSTVALUE]
,[CU_SALEVALUE]
,[CU_SALEVAL_11]
,[CU_SALEVAL_12]
,[CU_SALEVAL_13]
,[CU_USERDATE1]
,[CU_USERDATE2]
,[CU_NOTES]
,[CU_MULTIADD_FLG]
,[CU_INV_ADD_CDE]
,[CU_DEL_ADD_CDE]
,[CU_STAT_ADD_CDE]
,[CU_DATE_INV]
,[CU_DATE_PAY]
,[CU_USER_PUTIN]
,[CU_DATE_PUTIN]
,[CU_DATE_EDITED]
,[CU_USER_EDITED]
,[CU_MU_STATUS]
,[CU_LINE_DISC]
,[CU_TOT_DISC]
,[CU_SETT_DISC_1]
,[CU_SETT_DISC_2]
,[CU_SETT_DAYS_1]
,[CU_SETT_DAYS_2]
,[CU_TERMS_OPTION]
,[CU_ON_STOP]
,[CU_CREDIT_LIMIT]
,[CU_TERMS]
,[CU_DEL_CHARGE]
,[CU_DEL_CHARGE_C]
,[CU_DEL_CHG_PCNT]
,[CU_MIN_ORDR]
,[CU_MIN_ORDR_C]
,[CU_DUE_DAYS]
,[CU_A_P_DAYS]
,[CU_DUEDATE_TYPE]
,[CU_PRICE_KEY]
,[CU_ANALYSIS]
,[CU_TAX_CODE]
,[CU_BANK_ANALYS]
,[CU_PRIMARY]
,[CU_DEALERCODE]
,[CU_ADDRESS_USER1]
,[CU_ADDRESS_USER2]
,[CU_EMAIL]
,[CU_WEB_PASSWORD]
,[CU_CURRENCY_CHANGED]
,[CU_SOURCE]
,[CU_ACCOUNT_TYPE]
,[CU_DOC_DESTINATION]
,[CU_COUNTRY]
,[CU_DO_NOT_USE]
,[CU_CREDIT_CONTROLLER]
,[CU_TERMS_LINK]
,[CU_CURR_CREDIT_LIMIT]
,[CU_ANT_DAYS_FROM_DATES_OPT]
,[CU_PAYMENT_PROMISED]
,[CU_PAYMENT_PROMISED_DATE]
,[CU_CUSTOM_TERMS_TEMPLATE]
,[CU_PAYMENT_PROMISED_CURRENCY]
,[CU_COMPANY_REG_NUMBER])
select [CUCODE]
,[CUNAME]
,[CUADDRESS]
,[CUPOSTCODE]
,[CUPHONE]
,[CUPHONE2]
,[CUFAX]
,[CUPROSCODE]
,[CUCONTACT]
,[CU_MULTI_CURR]
,[CUSALUTE]
,[CU_PRO_CONDATE]
,[CU_EXPORT_CODE]
,[CU_COUNTRY_CODE]
,[CU_VAT_REG_NO]
,[CU_EC_DELIVERY]
,[CU_EC_T_NATURE]
,[CU_EC_T_MODE]
,[CUBALANCE]
,[CUBALANCE_C]
,[CUAGED_1]
,[CUAGED_2]
,[CUAGED_3]
,[CUAGED_4]
,[CUAGED_UNALLOC]
,[CUSORT]
,[CUUSER1]
,[CUUSER2]
,[CUUSER3]
,[CUUSED]
,[CUTURNOVERPTD]
,[CUTURNOVR_L1]
,[CUTURNOVR_L2]
,[CUTURNOVR_L3]
,[CUTURNOVR_L4]
,[CUTURNOVR_L5]
,[CUTURNOVR_L6]
,[CUTURNOVR_L7]
,[CUTURNOVR_L8]
,[CUTURNOVR_L9]
,[CUTURNOVR_L10]
,[CUTURNOVR_L11]
,[CUTURNOVR_L12]
,[CUTURNOVR_L13]
,[CUTURNOVR_C1]
,[CUTURNOVR_C2]
,[CUTURNOVR_C3]
,[CUTURNOVR_C4]
,[CUTURNOVR_C5]
,[CUTURNOVR_C6]
,[CUTURNOVR_C7]
,[CUTURNOVR_C8]
,[CUTURNOVR_C9]
,[CUTURNOVR_C10]
,[CUTURNOVR_C11]
,[CUTURNOVR_C12]
,[CUTURNOVR_C13]
,[CUTURNOVR_O1]
,[CUTURNOVR_O2]
,[CUTURNOVR_O3]
,[CUTURNOVR_O4]
,[CUTURNOVR_O5]
,[CUTURNOVR_O6]
,[CUTURNOVR_O7]
,[CUTURNOVR_O8]
,[CUTURNOVR_O9]
,[CUTURNOVR_O10]
,[CUTURNOVR_O11]
,[CUTURNOVR_O12]
,[CUTURNOVR_O13]
,[CUTURNOVERYTD]
,[CUCURRENCYCODE]
,[CUTURNOVR_L1_C]
,[CUTURNOVR_L2_C]
,[CUTURNOVR_L3_C]
,[CUTURNOVR_L4_C]
,[CUTURNOVR_L5_C]
,[CUTURNOVR_L6_C]
,[CUTURNOVR_L7_C]
,[CUTURNOVR_L8_C]
,[CUTURNOVR_L9_C]
,[CUTURNOVR_L10_C]
,[CUTURNOVR_L11_C]
,[CUTURNOVR_L12_C]
,[CUTURNOVR_L13_C]
,[CUTURNOVR_C1_C]
,[CUTURNOVR_C2_C]
,[CUTURNOVR_C3_C]
,[CUTURNOVR_C4_C]
,[CUTURNOVR_C5_C]
,[CUTURNOVR_C6_C]
,[CUTURNOVR_C7_C]
,[CUTURNOVR_C8_C]
,[CUTURNOVR_C9_C]
,[CUTURNOVR_C10_C]
,[CUTURNOVR_C11_C]
,[CUTURNOVR_C12_C]
,[CUTURNOVR_C13_C]
,[CUTURNOVR_O1_C]
,[CUTURNOVR_O2_C]
,[CUTURNOVR_O3_C]
,[CUTURNOVR_O4_C]
,[CUTURNOVR_O5_C]
,[CUTURNOVR_O6_C]
,[CUTURNOVR_O7_C]
,[CUTURNOVR_O8_C]
,[CUTURNOVR_O9_C]
,[CUTURNOVR_O10_C]
,[CUTURNOVR_O11_C]
,[CUTURNOVR_O12_C]
,[CUTURNOVR_O13_C]
,[CUTURNOVR_YTD_C]
,[CUTURNOVR_PTD_C]
,[CU_COSTVAL_1]
,[CU_COSTVAL_2]
,[CU_COSTVAL_3]
,[CU_COSTVAL_4]
,[CU_COSTVAL_5]
,[CU_COSTVAL_6]
,[CU_COSTVAL_7]
,[CU_COSTVAL_8]
,[CU_COSTVAL_9]
,[CU_COSTVAL_10]
,[CU_COSTVAL_11]
,[CU_COSTVAL_12]
,[CU_COSTVAL_13]
,[CU_SALEVAL_1]
,[CU_SALEVAL_2]
,[CU_SALEVAL_3]
,[CU_SALEVAL_4]
,[CU_SALEVAL_5]
,[CU_SALEVAL_6]
,[CU_SALEVAL_7]
,[CU_SALEVAL_8]
,[CU_SALEVAL_9]
,[CU_SALEVAL_10]
,[CU_COSTVAL_PTD]
,[CU_COSTVAL_YTD]
,[CU_SALEVAL_PTD]
,[CU_SALEVAL_YTD]
,[CU_COSTVALUE]
,[CU_SALEVALUE]
,[CU_SALEVAL_11]
,[CU_SALEVAL_12]
,[CU_SALEVAL_13]
,[CU_USERDATE1]
,[CU_USERDATE2]
,[CU_NOTES]
,[CU_MULTIADD_FLG]
,[CU_INV_ADD_CDE]
,[CU_DEL_ADD_CDE]
,[CU_STAT_ADD_CDE]
,[CU_DATE_INV]
,[CU_DATE_PAY]
,[CU_USER_PUTIN]
,[CU_DATE_PUTIN]
,[CU_DATE_EDITED]
,[CU_USER_EDITED]
,[CU_MU_STATUS]
,[CU_LINE_DISC]
,[CU_TOT_DISC]
,[CU_SETT_DISC_1]
,[CU_SETT_DISC_2]
,[CU_SETT_DAYS_1]
,[CU_SETT_DAYS_2]
,[CU_TERMS_OPTION]
,[CU_ON_STOP]
,[CU_CREDIT_LIMIT]
,[CU_TERMS]
,[CU_DEL_CHARGE]
,[CU_DEL_CHARGE_C]
,[CU_DEL_CHG_PCNT]
,[CU_MIN_ORDR]
,[CU_MIN_ORDR_C]
,[CU_DUE_DAYS]
,[CU_A_P_DAYS]
,[CU_DUEDATE_TYPE]
,[CU_PRICE_KEY]
,[CU_ANALYSIS]
,[CU_TAX_CODE]
,[CU_BANK_ANALYS]
,[CU_PRIMARY]
,[CU_DEALERCODE]
,[CU_ADDRESS_USER1]
,[CU_ADDRESS_USER2]
,[CU_EMAIL]
,[CU_WEB_PASSWORD]
,[CU_CURRENCY_CHANGED]
,[CU_SOURCE]
,[CU_ACCOUNT_TYPE]
,[CU_DOC_DESTINATION]
,[CU_COUNTRY]
,[CU_DO_NOT_USE]
,[CU_CREDIT_CONTROLLER]
,[CU_TERMS_LINK]
,[CU_CURR_CREDIT_LIMIT]
,[CU_ANT_DAYS_FROM_DATES_OPT]
,[CU_PAYMENT_PROMISED]
,[CU_PAYMENT_PROMISED_DATE]
,[CU_CUSTOM_TERMS_TEMPLATE]
,[CU_PAYMENT_PROMISED_CURRENCY]
,[CU_COMPANY_REG_NUMBER] from DLA..SL_ACCOUNTS INNER JOIN
DLA..SL_ACCOUNTS2 ON DLA..SL_ACCOUNTS.CU_PRIMARY = DLA..SL_ACCOUNTS2.CU_PRIMARY_2
where CUSORT <> 'OBSOLETE' and CU_USRCHAR2 = 'SERVICE' and (CU_DATE_INV>='01/11/2011' or CU_DATE_PAY>='01/11/2011')

The CTS version of the database has the additional column called Direc_Debit_Type...how do I get around this on the Insert statement?

Thanks in advance

Steve
 
Sort by date Sort by votes
This one is probably a no-brainer for the sql guru's here, but I am a bit of a novice and do know how to do this....

I need to create a query that will pull fields from another db's table but also take 3 fields and combine them into one.. for example, I need to take 4 fields: "address", "City", "state", and "zip" and combine them into one field and insert a comma between address and city and between city and state. and a space between state and zip.

can anybody tell me how I can go about this?

I need the query to either create a table from this or update a table from this.... thank you in advance for the help..
 
Upvote 0 Downvote
I apologize for that reply... I thought I was submitting a new post to this forum...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
356
BobSuruncle
BobSuruncle
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
227
Chris Miller
Chris Miller
eboughey1008
  • Locked
  • Question
Import columns from one table into another table with dissimilar field layout
Replies
7
Views
166
Chris Miller
Chris Miller
bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
263
bharons
bharons
Billz66
  • Locked
  • Question
need to udate table entry with number containing a *
Replies
11
Views
196
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top