how to insert data from one table to another

Question

Hi, kindly seeking for help on how i can use a stored procedure to select data from one table and insert into another..i have the below stored procedure..kindly help on how i implement it to achieve inserting data from one table to another. CREATE PROCEDURE [enterprise].[send_sms] ( @CompanyID NVARCHAR(72), @BranchID NVARCHAR(36), @DepartmentID NVARCHAR(36), @Value nvarchar(36)= '' ) AS DECLARE @EmployeeName nvarchar(36) DECLARE @EmployeeMobilePhone nvarchar(36) DECLARE @VendorName nvarchar(36) DECLARE @VendorPhone nvarchar(36) DECLARE @PatientName nvarchar(36) DECLARE @PatientCellPhone nvarchar(36) BEGIN IF @Value= 'Patients' BEGIN select PatientName,PatientCellPhone from CustomerInformation END ELSE IF @Value= 'Suppliers' BEGIN select VendorName,VendorPhone from VendorInformation END ELSE IF @Value= 'Employees' BEGIN select EmployeeName,EmployeeMobilePhone from PayrollEmployees END END

Anurag Sharma · Answer

We can use the particular SQL query to insert data from one sql table to another................... INSERT INTO TBLCAMPDTL (Campaign_ID, Division, Offer_Type, Campaign_Name ) SELECT Campaign_ID, Division, Offer_Type, Campaign_Name FROM TBLCAMPDTL_TEMP where Campaign_ID=@CampaignID AND [STATUS] 'D'

john kanyora · Answer

Thanks alot..i got it now

Piyush Pansuriya · Answer

insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient,Message) select CompanyID,BranchID,DepartmentID,PatientCellPhone, PatientName from CustomerInformation As you have a primary key defined in the perticular column, you must insert a value in that field. In your table 'CustomerInformation' must have data in column 'CompanyID' or other column in which has primary key.

Piyush Pansuriya · Answer

Cannot insert the value NULL into column 'CompanyID', table 'SmartCare_Melchizedek.dbo.sms_outbox'; column does not allow nulls. INSERT fails. May be your column 'CompanyID' has primary key. To insert in CompanyID, you have to provide auto increment value in it. By using 'Identity Specification' of column properties you can achive it.

Piyush Pansuriya · Answer

Hi, In which table you want to insert? IF @Value='Patients' BEGIN Insert into ( , ) select PatientName,PatientCellPhone from CustomerInformation END ELSE IF @Value='Suppliers' BEGIN Insert into ( , ) select VendorName,VendorPhone from VendorInformation END ELSE IF @Value='Employees' BEGIN Insert into ( , ) select EmployeeName,EmployeeMobilePhone from PayrollEmployees END

Francis Bastin · Answer

Hi, Use below query Insert into destination_tablename select EmployeeName,EmployeeMobilePhone from PayrollEmployees for above query no of column should be same for both source and destination table. if you want to insert specified column then use below query. Inser into destination_tablename (columns) select columns from PayrollEmployees.

john kanyora · Answer

in regard to this , kindly help on how i can implement a stored procedure on the code behind that once i use my radiobutton it loads the data to the table sms_outbox. i have created the stored procedure below Create PROCEDURE [enterprise].[send_sms] ( @CompanyID NVARCHAR(72), @BranchID NVARCHAR(36), @DepartmentID NVARCHAR(36), @Value nvarchar(36)='' ) AS DECLARE @EmployeeName nvarchar(36) DECLARE @EmployeeMobilePhone nvarchar(36) DECLARE @VendorName nvarchar(36) DECLARE @VendorPhone nvarchar(36) DECLARE @PatientName nvarchar(36) DECLARE @PatientCellPhone nvarchar(36) BEGIN IF @Value='Patients' BEGIN insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient) select CompanyID,BranchID,DepartmentID,PatientCellPhone from CustomerInformation END ELSE IF @Value='Suppliers' BEGIN insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient) select CompanyID,BranchID,DepartmentID,VendorPhone from VendorInformation END this is my aspx page with the radiobutton defined

john kanyora · Answer

Kindly help on this one, i have used this query insert into sms_outbox(Recipient,Message) select PatientName,PatientCellPhone from CustomerInformation and i was running it on my sql server but am getting the below error Cannot insert the value NULL into column 'CompanyID', table 'SmartCare_Melchizedek.dbo.sms_outbox'; column does not allow nulls. INSERT fails.

john kanyora · Answer

Thanks piyush, i want to insert in a table called sms_outbox

john kanyora · Answer

Thanks alot.i appreciate

john kanyora · Answer

yes it has a primary key identity together with branchID and DepartmentID,..i have used the query like this insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient,Message) select CompanyID,BranchID,DepartmentID,PatientCellPhone, PatientName from CustomerInformation is there a simpler way i can use like the one you are talking about identity specification, since i dont know about it

how to insert data from one table to another

Answers (11)