[Solved] 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 [closed]

by


I assume that you want to dumb the data from bulk_data to myaccount table, business_setup table and business_info table.

if so,

Assumptions :
- Column names with prefix myaccount_ belongs to myaccount table
- Column names with prefix business_setup belongs to business_setup table
- Column names with prefix business_info belongs to business_info table
- complete data from  [bulk_data] needs to be dump in to above tables 


Limitations:
- please don't forget to include not-null columns of tables in select list 
- handle foreign key columns (if exists) by yourself

-- EXEC Data_Migration
    CREATE PROCEDURE Data_Migration
AS
BEGIN
    /* before you execute this, make sure to include all not null column names in select list */
    INSERT INTO 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]
    FROM [bulk_data]


    /*  before you execute this, make sure to include all not null column names in select list      */
    INSERT INTO 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] ,
    FROM [bulk_data]

    /*
        before you execute this, make sure to include all not null column names in select list 
        I assume [subscription_type] exists in business_info table
    */
    INSERT INTO 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]
    FROM [bulk_data]
END

solved 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 [closed]