how to insert data from one table to another

john kanyora

8y968

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

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anurag Sharma8y ago
Accepted 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 kanyora8y ago
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 kanyora8y ago
Thanks alot..i got it now
Piyush Pansuriya8y ago
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.
john kanyora8y ago
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
Piyush Pansuriya8y ago
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.
john kanyora8y ago
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 kanyora8y ago
Thanks piyush, i want to insert in a table called sms_outbox
john kanyora8y ago
Thanks alot.i appreciate
Piyush Pansuriya8y ago
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 Bastin8y ago
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.