Click here to Skip to main content
14,774,519 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have this new empty table that have eight field called Table-X and an existing table called Table_Y with different fields .
In the store procedure I am only interested two fields of Table_Y that I have to insert into the table_X.
My question is how do I insert these fields into the new empty table that are also different name fields?

What I have tried:

@ID_CUSTOMER int,
@COMPANY_NAME varchar(50),
@EXP_DATE date,

AS

INSERT INTO TABLE_X(ID_CLI,COMPANY_NAME,EXP_DATE,DTINIT) //DTINT TAKE A DEFAULT VALUE ON DB

SELECT ANCDCF,ANRGSO
FROM TABLE_Y
WHERE TABLE_Y.ANCDCF = @ID_CUSTOMER AND TABLE_Y.ANRGSO = @COMPANY_NAME
Posted
Updated 3-Sep-20 4:31am
Comments
Sandeep Mewara 3-Sep-20 9:14am
   
What about EXP_DATE? that too can have default vlaue? basically which fields of X maps to Y and what about the rest of fields of X. Can they be empty as is (not mandatory)
DARK__FOXX 3-Sep-20 9:24am
   
EXP_DATE isn't default value , but a value that will be insert with a Textbox.
Table_X have ID_CUSTOMER and COMPANY_NAME that are ANCDCF and ANRGSO of Table_Y, but as you can see they have different names.

The SELECT statement needs to specify the same number of columns as you are INSERTing. The names do not matter.

The columns you're inserting don't need to come from the table you're selecting from. You can use parameters, local variables, or functions instead.
INSERT INTO table_x (ID_CLI, COMPANY_NAME, EXP_DATE, DTINIT)
SELECT ANCDCF, ANRGSO, @EXP_DATE, SYSDATETIME()
FROM table_y
WHERE table_y.ANCDCF = @ID_CUSTOMER
AND table_y.ANRGSO = @COMPANY_NAME;
   
Comments
DARK__FOXX 3-Sep-20 9:30am
   
Can I ask how do I run it to see if it works or not? Thanks
Richard Deeming 3-Sep-20 9:33am
   
In the same way as you'd run any other stored procedure or SQL code.

If you want to run it manually from within SQL Management Studio, you'll need to declare local variables instead of parameters:
DECLARE @ID_CUSTOMER int = 42, @COMPANY_NAME varchar(50) = 'Fenchurch', @EXP_DATE date = SYSDATETIME();
Based on your comment, it would be something like:
@ID_CUSTOMER int,
@COMPANY_NAME varchar(50),
@EXP_DATE date,

AS

INSERT INTO TABLE_X(ID_CLI,COMPANY_NAME,EXP_DATE,DTINIT) 

SELECT 
    ANCDCF, -- ANOTHER TABLE
    ANRGSO, -- ANOTHER TABLE
    @EXP_DATE, -- PASSED FROM UI
    SYSDATETIME(). -- DEFAULT VALUE
FROM 
    TABLE_Y
WHERE 
    TABLE_Y.ANCDCF = @ID_CUSTOMER AND TABLE_Y.ANRGSO = @COMPANY_NAME


All you need is to make sure that the INSERT INTO values count is same as in SELECT query. Data can come from anywhere.
Refer: SQL INSERT INTO SELECT Statement[^]
   

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