SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need help with the code
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Patyk
Starting Member

46 Posts

Posted - 03/03/2014 :  13:57:15  Show Profile  Reply with Quote
I have the following code to import .csv file into my table in excel
It's being inserted into a table. dbo.ImportedPromoPricing.
Table and the .csv file have 3 fields price, code and selling price.
Once import is completed I want to use the data in my dbo.ImportedPromoPricing to update another table dbo.MasterPricing. Records need to be compared and updated or appended if needed. in case of update only price will be updated. this is the beginning of my code


USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[ImportPromoPricing] Script Date: 03/03/2014 14:04:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[ImportPromoPricing] as
begin
truncate table dbo.ImportedPromoPricing

Bulk Insert dbo.ImportedPromoPricing
from 'D:\Reporting\DB\Sales\PromoPriceImport\PromoPriceImport.csv'
with
(FIELDTERMINATOR =',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)

DELETE FROM dbo.ImportedPromoPricing
where SellingPrice IS NULL


end

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[UpdatePromoPricing] Script Date: 03/03/2014 13:34:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UpdatePromoPricing]
@StockCode char(30),
@PriceCode char(2),
@SellingPrice decimal(15,5)
as
begin

SET NOCOUNT ON;

begin
IF NOT EXISTS (SELECT * FROM dbo.ImportedPromoPricing WHERE StockCode = @StockCode and PriceCode = @PriceCode)
BEGIN
insert into [dbo].[tmpinvpricetable] ([StockCode],[PriceCode],[SellingPrice])
values (@StockCode, @PriceCode, @SellingPrice);
END
ELSE
BEGIN
update dbo.tmpinvpricetable
set SellingPrice = @SellingPrice
WHERE StockCode = @StockCode and PriceCode = @PriceCode;
END

end
end


Edited by - Patyk on 03/03/2014 18:12:32

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 03/03/2014 :  14:06:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
Show us how you are executing the stored procedure. It is your execute that's the problem, not the stored procedure.

EXEC UpdatePromoPricing 'value111111111111', 'v2', 15.54

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Patyk
Starting Member

46 Posts

Posted - 03/03/2014 :  14:10:34  Show Profile  Reply with Quote
I just right click / Execute Stored Procedure ? I am able to do this with all the other ones....?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 03/03/2014 :  14:11:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
Do it through an EXEC command. Perhaps the GUI has a bug. By the way, in case it is a bug, you should try installing the latest SQL service pack plus cumulative update. MS includes bug fixes for client tools in those updates.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 03/03/2014 :  14:57:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
A nicer and cleaner way to achieve what you are doing is to start using the MERGE command.
ALTER PROCEDURE dbo.UpdatePromoPricing
(
	@StockCode CHAR(30),
	@PriceCode CHAR(2),
	@SellingPrice DECIMAL(15, 5)
)
AS

SET NOCOUNT ON;

MERGE	dbo.tmpInvPriceTable AS tgt
USING	(
		SELECT	@StockCode AS StockCode,
			@PriceCode AS PriceCode,
			@SellingPrice AS SellingPrice
	) AS src ON src.StockCode = tgt.StockCode
		AND src.PriceCode = tgt.PriceCode
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.SellingPrice = src.SellingPrice
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					StockCode,
					PriceCode,
					SellingPrice
				)
			VALUES	(
					src.StockCode,
					src.PriceCode,
					src.SellingPrice
				);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Patyk
Starting Member

46 Posts

Posted - 03/03/2014 :  15:30:03  Show Profile  Reply with Quote
Thanks however I am receiving error while running it.
Msg 102, Level 15, State 1, Procedure UpdatePromoPricing1, Line 16
Incorrect syntax near 'MERGE'.
Msg 156, Level 15, State 1, Procedure UpdatePromoPricing1, Line 21
Incorrect syntax near the keyword 'AS'.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 03/03/2014 :  15:43:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
MERGE is not supported in SQL Server 2005. You'll need to use your previous version and execute it via EXEC like I mentioned and showed an example.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Patyk
Starting Member

46 Posts

Posted - 03/03/2014 :  16:41:46  Show Profile  Reply with Quote
I think I have made a mistake creating this procedure. I am not supplying the parameters the right way.
I need to open ImportedPromoPricingTable and for each record in this table I need to compare with each record in my tmpinvpricetable (which is the destination table) if exist update the selling price if not append the whole line. I need some kind of LOOP code?

Thanks

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 03/03/2014 :  18:01:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
Please update your original post to include the original stored procedure. We don't need to see ImportedPromoPricingTable stored procedure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Patyk
Starting Member

46 Posts

Posted - 03/03/2014 :  18:14:33  Show Profile  Reply with Quote
I did however my second store procedure won't work since I am not providing the parameters the whole import / update or append needs to be reconfigured and redone.

The first sp works fine, this is why i think the other tasks could be just appended to it.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 03/03/2014 :  18:39:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
Try this:

insert into tmpinvpricetable] ([StockCode],[PriceCode],[SellingPrice])
select [StockCode],[PriceCode],[SellingPrice]
from ImportedPromoPricing i
left join tmpinvpricetable t on i.stockcode = t.stockcode
where t.stockcode is null

update t
set SellingPrice = i.SellingPrice
from ImportedPromoPricing i
left join tmpinvpricetable t on i.stockcode = t.stockcode
where t.stockcode is null

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Patyk
Starting Member

46 Posts

Posted - 03/03/2014 :  18:56:41  Show Profile  Reply with Quote
Tara.....


I will only need to insert if the record does not exist in my tmpinvpricetable
link is stockcode = stockcode and pricecode = pricecode

I will need to update (price only) if record found.

What does it mean in your code where t.stockcode is null?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 03/03/2014 :  19:06:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
Just change the joins to include pricecode.

The t.stockcode is due to using a left join. A left join returns all rows from the first table (left table) but only the matches in the second table (right table). For the rows where there isn't a match, the columns will return as null.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 03/03/2014 :  19:07:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
Run just the select to see what I mean regarding the left join/null stuff. Remove the where clause, add it back, change it to IS NOT NULL. Note the difference in each.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Patyk
Starting Member

46 Posts

Posted - 03/03/2014 :  19:50:43  Show Profile  Reply with Quote
I am getting the error while running the store procedure. Stock Code and Price Code are the primary Codes?


Msg 515, Level 16, State 2, Procedure testupdate, Line 3
Cannot insert the value NULL into column 'StockCode', table 'Reporting.dbo.tmpinvpricetable'; column does not allow nulls. INSERT fails.
Null not null ?

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[testupdate] Script Date: 03/03/2014 16:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testupdate] as

insert into dbo.tmpinvpricetable ([tmpinvpricetable].[StockCode],[tmpinvpricetable].[PriceCode],[tmpinvpricetable].[SellingPrice])

select [tmpinvpricetable].[StockCode],[tmpinvpricetable].[PriceCode],[tmpinvpricetable].[SellingPrice]

from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where ImportedPromoPricing.StockCode is not null


update tmpinvpricetable
set SellingPrice = ImportedPromoPricing.SellingPrice
from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where ImportedPromoPricing.StockCode is not null

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 03/04/2014 :  11:02:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[testupdate] Script Date: 03/03/2014 16:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testupdate] as

insert into dbo.tmpinvpricetable ([tmpinvpricetable].[StockCode],[tmpinvpricetable].[PriceCode],[tmpinvpricetable].[SellingPrice])

select [tmpinvpricetable].[StockCode],[tmpinvpricetable].[PriceCode],[tmpinvpricetable].[SellingPrice]

from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where tmpinvpricetable.StockCode is null


update tmpinvpricetable
set SellingPrice = ImportedPromoPricing.SellingPrice
from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where tmpinvpricetable.StockCode is not null

You may need to also add tmpinvpricetable.PriceCode to the WHERE clauses.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Patyk
Starting Member

46 Posts

Posted - 03/06/2014 :  12:10:25  Show Profile  Reply with Quote
Still not working I am receiving an error message:
Msg 515, Level 16, State 2, Procedure testupdate, Line 3
Cannot insert the value NULL into column 'StockCode', table 'Reporting.dbo.tmpinvpricetable'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 03/06/2014 :  12:27:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
Sorry I missed that you had added the tmp table to the select. It should be the import table instead.

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[testupdate] Script Date: 03/03/2014 16:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testupdate] as

insert into dbo.tmpinvpricetable ([tmpinvpricetable].[StockCode],[tmpinvpricetable].[PriceCode],[tmpinvpricetable].[SellingPrice])
select ImportedPromoPricing.[StockCode],ImportedPromoPricing.[PriceCode],ImportedPromoPricing.[SellingPrice]
from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where tmpinvpricetable.StockCode is null

update tmpinvpricetable
set SellingPrice = ImportedPromoPricing.SellingPrice
from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where tmpinvpricetable.StockCode is not null

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 03/06/2014 12:28:11
Go to Top of Page

Patyk
Starting Member

46 Posts

Posted - 03/07/2014 :  12:45:02  Show Profile  Reply with Quote
thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000