Probs in Insert the Value through Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Probs in Insert the Value through Stored Procedure

Author

Topic

Arunraj
Starting Member

18 Posts

Posted - 2014-10-09 : 14:51:30

I have 200 Columns in Table called Flatfile_Sales
From that parent table some of the colums must get read to another table.
I have attachment what are all the rows need to inserted into Another Table called Sales_Trade.

TradeIn_1_VIN TradeIn_2_VIN TradeIn_3_VIN

1GNDV03L85D262784 JS3TD62V614165060
1G1AL15FX77304980 JS1VS54A042105741
1G3NL52T01C200877 1FTCR10X7VUC23612
KNDJT2A22A7072771
2GCEC19X831183112
1G2WP12K9WF246095

1G2WJ52MXXF352390 1G2JB124127146930
2G1FF1EV2A9200400 KNAFE121665293113
WAUCB28D6XA281622
3GNCA23DX8S721071 7H30H140721 3GNCA23S721071
1FAFP66L2XK107999
3VWSE69M32M069779
2FMZA51433BA85661
KNDJF724787507170 KNDMB233186265676
5NPEC4AC1BH023144
1G3WH15M7RD307319 2P4GP2438VR336059

And I have write the query using Stored Procedure as below.
INSERT INTO SALES_TRADEIN
(
SalesID,
TradeIn_VIN,
TradeIn_Make,
TradeIn_Model,
TradeIn_ExteriorColor,
TradeIn_Year,
TradeIn_Mileage,
TradeIn_Gross,
TradeIn_Payoff,
TradeIn_ACV,
TradeIn_InteriorColor
)
VALUES
(
@SalesID,
case when @TradeIn_1_VIN is null then @TradeIn_2_VIN else @TradeIn_1_VIN end,
case when @TradeIn_1_Make is null then @TradeIn_2_Make else @TradeIn_1_Make end,
case when @TradeIn_1_Model is null then @TradeIn_2_Model else @TradeIn_1_Model end,
case when @TradeIn_1_ExteriorColor is null then @TradeIn_2_ExteriorColor else @TradeIn_1_ExteriorColor end,
case when @TradeIn_1_Year is null then @TradeIn_2_Year else @TradeIn_1_Year end,
case when @TradeIn_1_Mileage is null then @TradeIn_2_Mileage else @TradeIn_1_Mileage end,
case when @TradeIn_1_Gross is null then @TradeIn_2_Gross else @TradeIn_1_Gross end,
case when @TradeIn_1_Payoff is null then @TradeIn_2_Payoff else @TradeIn_1_Payoff end,
case when @TradeIn_1_ACV is null then @TradeIn_2_ACV else @TradeIn_1_ACV end,
case when @TradeIn_1_InteriorColor is null then @TradeIn_2_InteriorColor else @TradeIn_1_InteriorColor end

);

After i run the about script it reads only Trade_1 details Not the Trade_2 and trade_3 details.
If we have details in only Trade_1 Column its ok no probelm but when we have rows in both Trade_1 and Trade_2 column and Trade_3 columns
How to write the query.

Arunraj
Starting Member

18 Posts

Posted - 2014-10-10 : 02:07:52

Can anyone please givs me the solution for this..

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-10 : 10:56:41

The code you posted is very long and hard to read, so it would take someone a considerable amount of time even to understand what the question is. I suspect that is why you have not received any responses.

What I would suggest is:
a) simplify your problem, to be representative of what you want to do without adding a lot of clutter.
b) describe what you are trying to do in words,
c) provide DDL to create the tables and some sample data.

For example (and I am not saying that this is what you want to do, this is just an example of how you might describe it).

"I have a table called Flatfile_Sales which has 5 columns of data. I want to process this table and insert this data into 3 different tables. The first 2 columns should go into TableA. The first column and columns 3-5 should go into TableB or TableC depending on the value in column 3. If the value in column 3 is less than 0, I want the data to be inserted into TableB, and if it is anything else, I want the data to be inserted into Table C.


-- sample tables
CREATE TABLE #Flatfile_Sales
(col1 INT, col2 FLOAT, col3 FLOAT, col4 FLOAT, col5 FLOAT);

CREATE TABLE #TableA (col1 INT, col2 FLOAT);
CREATE TABLE #TableC (Col1 INT, col3 FLOAT, col4 FLOAT, col5 FLOAT);
CREATE TABLE #TableC (Col1 INT, col3 FLOAT, col4 FLOAT, col5 FLOAT);

-- sample data
INSERT INTO #Flatfile_Sales
        ( col1, col2, col3, col4, col5 )
VALUES  
	(1,1.0,2.0,3.0,4.0,5.0),
	(2,1.0,-4.0,-7.0,-8.0);

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-10 : 11:21:53

You don't need a cursor. Structure your query like this:

INSERT INTO Sales (..)
OUTPUT ... into SALES_TRADEIN
SELECT FROM ... -- the select clause from your Declare Cursor

get rid of all those unused variables!

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2014-10-24 : 14:59:23

Arunraj: Did your problem get resolved or do you still need assistance?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources