Click here to Skip to main content
15,938,848 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I have some data in TableA
Now I want to insert the data of TableA (COL1, COL2, COL3) into another table TableB (COL1, COL2, COL3)

TableA
COL1 COL1 COL1
1 2 3
4 5 6
7 8 9
. . .
. . .


By using MSSQL 2005 stored procedure

Here is my code below
CREATE PROCEDURE [dbo].[ TestTable] 
	
AS
BEGIN
CREATE TABLE # TableB (COL1, COL2, COL3)

DECLARE @ COL1 varchar(200)
DECLARE @ COL2 varchar(200)
DECLARE @ COL3 varchar(200)

SELECT     @ COL1= COL1 FROM TableA
SELECT     @ COL2= COL2 FROM TableA
SELECT     @ COL3= COL3 FROM TableA



--Insert data into # TestTable table
INSERT INTO # TableB (COL1, COL2, COL3) Values (@ COL1, @COL2, @COL3)
	
SET NOCOUNT ON;

    -- Insert statements for final execution for procedure here
SELECT  *  FROM  # TableB

END

NB I am finding only one data in # TableB table.
Posted
Updated 25-May-11 0:18am
v4

You should not use variables for this.

your query should be very simple

like.

INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1


In you case it will be

INSERT INTO #AllDr(level_name,amount,debit_date)

SELECT     vendor.vendor_name, purchase_payment.total_amount, purchase_payment.received_date
FROM         vendor INNER JOIN
                      purchase ON vendor.vendor_id = purchase.vendor_id INNER JOIN
                      purchase_payment ON purchase.invoice_no = purchase_payment.invoice_no
WHERE     (vendor.deleted = 0) AND (purchase.deleted = 0) AND (purchase_payment.record_deleted = 0)AND (purchase_payment.received_date BETWEEN @StardDate AND DATEADD(day, 1,@EndDate))
 
Share this answer
 
in the code snippet you mentioned, SELECT INTO will be more appopriate than INSERT INTO.
This will help you to avoid explicitly creating new Table
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900