sql server - I am inserting excel sheet bulk data into a sql database table, now i need to split the table into multiple tables by using stored procedure -
select [myaccount_firstname] , [myaccount_lastname] , [myaccount_email] , [myaccount_mobile_no] , [myaccount_workphone] , [myaccount_street_1] , [myaccount_street_2] , [myaccount_city] , [myaccount_state] , [myaccount_zipcode] , [myaccount_country] , [business_setup_business_name] , [business_setup_fein_reg_id] , [business_setup_duns] , [business_setup_street_1] , [business_setup_street_2] , [business_setup_city] , [business_setup_state] , [business_setup_zipcode] , [business_setup_country] , [business_setup_businessphone] , [business_setup_businessfax] , [business_setup_emailid] , [business_setup_website] , [business_setup_primary_firstname] , [business_setup_primary_lastname] , [business_setup_primary_email] , [business_setup_primary_mobile] , [business_setup_primary_workphone] , [business_setup_secondary_firstname] , [business_setup_secondary_lastname] , [business_setup_secondary_email] , [business_setup_secondary_mobile] , [business_setup_secondary_workphone] , [business_setup_pay_via] , [business_setup_paypal_id] , [business_info_short_description] , [business_info_long_description] , [business_info_hours_sunday] , [business_info_hours_monday] , [business_info_hours_tuesday] , [business_info_hours_wednesday] , [business_info_hours_thursday] , [business_info_hours_friday] , [business_info_hours_saturday] , [business_info_starttime] , [business_info_endtime] , [business_info_webonly] , [business_info_telephoneonly] , [business_info_appointmentonly] , [business_info_speciality_acupuncture] , [business_info_speciality_chiropractor] , [business_info_speciality_conventional] , [business_info_speciality_dentist] , [business_info_speciality_elderlycare] , [business_info_speciality_eyecare] , [business_info_speciality_general] , [business_info_speciality_healthcoach] , [business_info_speciality_homeopathy] , [business_info_speciality_lifecoach] , [business_info_speciality_meditation] , [business_info_speciality_myofacialtherapy] , [business_info_speciality_naturopathy] , [business_info_speciality_nutritionhealthycooking] , [business_info_speciality_pilates] , [business_info_speciality_wellnesscenter] , [business_info_speciality_yoga] , [subscription_type] [bulk_data] this main table this, need split data 3 tables
- myaccount
- business_setup
- business_info
i assume want dumb data bulk_data myaccount table, business_setup table , business_info table.
if so,
assumptions : - column names prefix myaccount_ belongs myaccount table - column names prefix business_setup belongs business_setup table - column names prefix business_info belongs business_info table - complete data [bulk_data] needs dump in above tables limitations: - please don't forget include not-null columns of tables in select list - handle foreign key columns (if exists) -- exec data_migration create procedure data_migration begin /* before execute this, make sure include not null column names in select list */ insert myaccount ([myaccount_firstname] , [myaccount_lastname] , [myaccount_email] , [myaccount_mobile_no] , [myaccount_workphone] , [myaccount_street_1] , [myaccount_street_2] , [myaccount_city] , [myaccount_state] , [myaccount_zipcode] , [myaccount_country]) select [myaccount_firstname] , [myaccount_lastname] , [myaccount_email] , [myaccount_mobile_no] , [myaccount_workphone] , [myaccount_street_1] , [myaccount_street_2] , [myaccount_city] , [myaccount_state] , [myaccount_zipcode] , [myaccount_country] [bulk_data] /* before execute this, make sure include not null column names in select list */ insert business_setup ([business_setup_business_name] , [business_setup_fein_reg_id] , [business_setup_duns] , [business_setup_street_1] , [business_setup_street_2] , [business_setup_city] , [business_setup_state] , [business_setup_zipcode] , [business_setup_country] , [business_setup_businessphone] , [business_setup_businessfax] , [business_setup_emailid] , [business_setup_website] , [business_setup_primary_firstname] , [business_setup_primary_lastname] , [business_setup_primary_email] , [business_setup_primary_mobile] , [business_setup_primary_workphone] , [business_setup_secondary_firstname] , [business_setup_secondary_lastname] , [business_setup_secondary_email] , [business_setup_secondary_mobile] , [business_setup_secondary_workphone] , [business_setup_pay_via] , [business_setup_paypal_id] ,) select [business_setup_business_name] , [business_setup_fein_reg_id] , [business_setup_duns] , [business_setup_street_1] , [business_setup_street_2] , [business_setup_city] , [business_setup_state] , [business_setup_zipcode] , [business_setup_country] , [business_setup_businessphone] , [business_setup_businessfax] , [business_setup_emailid] , [business_setup_website] , [business_setup_primary_firstname] , [business_setup_primary_lastname] , [business_setup_primary_email] , [business_setup_primary_mobile] , [business_setup_primary_workphone] , [business_setup_secondary_firstname] , [business_setup_secondary_lastname] , [business_setup_secondary_email] , [business_setup_secondary_mobile] , [business_setup_secondary_workphone] , [business_setup_pay_via] , [business_setup_paypal_id] , [bulk_data] /* before execute this, make sure include not null column names in select list assume [subscription_type] exists in business_info table */ insert business_info ([business_info_short_description] , [business_info_long_description] , [business_info_hours_sunday] , [business_info_hours_monday] , [business_info_hours_tuesday] , [business_info_hours_wednesday] , [business_info_hours_thursday] , [business_info_hours_friday] , [business_info_hours_saturday] , [business_info_starttime] , [business_info_endtime] , [business_info_webonly] , [business_info_telephoneonly] , [business_info_appointmentonly] , [business_info_speciality_acupuncture] , [business_info_speciality_chiropractor] , [business_info_speciality_conventional] , [business_info_speciality_dentist] , [business_info_speciality_elderlycare] , [business_info_speciality_eyecare] , [business_info_speciality_general] , [business_info_speciality_healthcoach] , [business_info_speciality_homeopathy] , [business_info_speciality_lifecoach] , [business_info_speciality_meditation] , [business_info_speciality_myofacialtherapy] , [business_info_speciality_naturopathy] , [business_info_speciality_nutritionhealthycooking] , [business_info_speciality_pilates] , [business_info_speciality_wellnesscenter] , [business_info_speciality_yoga] , [subscription_type]) select [business_info_short_description] , [business_info_long_description] , [business_info_hours_sunday] , [business_info_hours_monday] , [business_info_hours_tuesday] , [business_info_hours_wednesday] , [business_info_hours_thursday] , [business_info_hours_friday] , [business_info_hours_saturday] , [business_info_starttime] , [business_info_endtime] , [business_info_webonly] , [business_info_telephoneonly] , [business_info_appointmentonly] , [business_info_speciality_acupuncture] , [business_info_speciality_chiropractor] , [business_info_speciality_conventional] , [business_info_speciality_dentist] , [business_info_speciality_elderlycare] , [business_info_speciality_eyecare] , [business_info_speciality_general] , [business_info_speciality_healthcoach] , [business_info_speciality_homeopathy] , [business_info_speciality_lifecoach] , [business_info_speciality_meditation] , [business_info_speciality_myofacialtherapy] , [business_info_speciality_naturopathy] , [business_info_speciality_nutritionhealthycooking] , [business_info_speciality_pilates] , [business_info_speciality_wellnesscenter] , [business_info_speciality_yoga] , [subscription_type] [bulk_data] end 
Comments
Post a Comment