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)
 Append new Cust. based on some values from existin
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Patyk
Yak Posting Veteran

66 Posts

Posted - 08/08/2014 :  19:03:36  Show Profile  Reply with Quote
Hello, I need to write a store procedure to append records to our customer table. Here is a sample of my table below>
Customer char(7)
Name char(30)
Salesperson char(3)
PriceCode char(2)
CustomerClass char(2)
Branch char(2)
TermsCode char(2)
Area char(2)
Telephone char(20)
Contact char(40)
DateCustAdded datetime
CustomerOnHold char(1)

I will have the following parameters provided
NewCustomer
ParentCustomer (will be listed in the existing CustomerTable under Customer field)
Name
Telephone
CustomerOnHold = Y
DateCustomerAdded = CurrentDate
TermsCode = P

In order to insert a new record I would need to query the current CustomerTable where ParentCustomer = Customer
From the existing customer I would need to copy the following fields that would be needed in order to append a newCustomer
Salesperson
PriceCode
CustomerClass
Area
Branch.

Please let me know if you could be any help

thanks.

gbritton
Flowing Fount of Yak Knowledge

1752 Posts

Posted - 08/10/2014 :  08:02:15  Show Profile  Reply with Quote
What have you tried so far?
Go to Top of Page

Patyk
Yak Posting Veteran

66 Posts

Posted - 08/10/2014 :  16:00:54  Show Profile  Reply with Quote
What I need is an example on how to append a record from one table (need to search for this row first) into another table. Most of the columns will be the same, however some fields will have to be substituted with parameters.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1752 Posts

Posted - 08/10/2014 :  18:47:26  Show Profile  Reply with Quote
quote:
Originally posted by Patyk

What I need is an example on how to append a record from one table (need to search for this row first) into another table. Most of the columns will be the same, however some fields will have to be substituted with parameters.




insert into target (col1, col2, col3)
select col1, col2, col3 from source


What do you mean by "first row"? Since SQL returns sets, there is no guarantee of any order unless you add an ORDER BY clause to the SELECT. If you do, what do you want to ORDER on?
Go to Top of Page

Patyk
Yak Posting Veteran

66 Posts

Posted - 08/11/2014 :  11:52:26  Show Profile  Reply with Quote
yes that would be correct if I would need to copy the whole row. Some columns i need to substitute with parameter. For example column1 and column2 would be a parameter everything else would be copied from the other table.

Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1752 Posts

Posted - 08/11/2014 :  12:45:26  Show Profile  Reply with Quote
quote:
Originally posted by Patyk

yes that would be correct if I would need to copy the whole row. Some columns i need to substitute with parameter. For example column1 and column2 would be a parameter everything else would be copied from the other table.



insert into target (col1, col2, col3)
select col1, @parm1, @parm2 from source
Go to Top of Page

Patyk
Yak Posting Veteran

66 Posts

Posted - 08/11/2014 :  14:47:29  Show Profile  Reply with Quote
I need to append a record from the same table based on provided parameter. The new record will have a new value for Customer field(provided by parameter), rest of the record will need to be copied from the row where customer = parent_customer_code

USE [companyT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE dbo.appendSXcustomer
@parent_customer_code char (7) ,
@sx_customer_code char (7)

AS
BEGIN
SET NOCOUNT ON
set @sx_customer_code = 'NewCust'

INSERT INTO dbo.ArCustomer (customer,Salesperson,CustomerClass,Branch,TermsCode,Area )
SELECT (@sx_customer_code,salesperson,customerclass,branch,termscode,area from dbo.ArCustomer where customer like @parent_customer_code)

END

Edited by - Patyk on 08/11/2014 15:24:32
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1752 Posts

Posted - 08/11/2014 :  18:10:37  Show Profile  Reply with Quote
OK -- does that work? If not, what do you want it to do differently
Go to Top of Page

Patyk
Yak Posting Veteran

66 Posts

Posted - 08/11/2014 :  18:22:23  Show Profile  Reply with Quote
It does not work still I have some errors. What is the other way of doing it?

Edited by - Patyk on 08/11/2014 19:11:17
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37457 Posts

Posted - 08/11/2014 :  20:09:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
INSERT INTO dbo.ArCustomer (customer,Salesperson,CustomerClass,Branch,TermsCode,Area )
SELECT @sx_customer_code,salesperson,customerclass,branch,termscode,area
from dbo.ArCustomer
where customer like '%' + @parent_customer_code + '%'

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

Patyk
Yak Posting Veteran

66 Posts

Posted - 08/12/2014 :  13:33:44  Show Profile  Reply with Quote
Now I have the following sp. Still won't work or compile I am getting an error Msg 102, Level 15, State 1, Procedure insert_sx_customer, Line 25
Incorrect syntax near ','.
the procedure is as follows.

USE [companyT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[insert_sx_customer]

@sx_customer_code char(10),
@parent_customer_code char(10),
@email_address char(50),
@name char(30),
@telephone char(20)

as

BEGIN

SET NOCOUNT ON;

declare @date as datetime
set @date = getdate()
set @parent_customer_code= 'BC0001'
set @sx_customer_code= 'SX0001'
set @email_address = 'test@hotmail.com'
set @telephone = '001-123-4567'
set @name = 'Alex Smith'

INSERT INTO dbo.ArCustomer (Customer,[Name], Salesperson,CustomerClass,Branch,TermsCode,Area,CustomerOnHold,SoDefaultType,Email,Rating,Contact,DateCustAdded)
SELECT (@parent_customer_code,@name, Salesperson,CustomerClass,Branch,"P",Area,"Y","B",@email_address,"HOLD",@name,@date)
from dbo.ArCustomer
where customer like '%' + @parent_customer_code + '%'

END
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37457 Posts

Posted - 08/12/2014 :  13:39:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
Remove the parenthesis for the SELECT. Please see my last reply for how the INSERT/SELECT should look.

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

tkizer
Almighty SQL Goddess

USA
37457 Posts

Posted - 08/12/2014 :  13:40:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
I'm confused why you are setting values in the body of the stored procedure for the input parameters. If they are just test values, then set the test values when you execute the stored procedure, not by changing the code of the stored procedure.

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

Patyk
Yak Posting Veteran

66 Posts

Posted - 08/12/2014 :  14:04:20  Show Profile  Reply with Quote
OK Thanks it executes fine, no errors, however no new records are being appened. It needs to find the existing record where customer like '%' + @parent_customer_code + '% and append a new one that same values with changes. Customer will be substituted with new Customer (@sx_customer_code) etc.... I am not sure why no new records are not appended.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37457 Posts

Posted - 08/12/2014 :  14:09:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
Does this return the row you want to duplicate?

SELECT *
from dbo.ArCustomer
where customer like '%BC0001%'

Don't you need to change @parent_customer_code to @sx_customer_code in the SELECT portion?

SELECT @sx_customer_code,@name, Salesperson,CustomerClass,Branch,"P",Area,"Y","B",@email_address,"HOLD",@name,@date
from dbo.ArCustomer
where customer like '%' + @parent_customer_code + '%'

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

Patyk
Yak Posting Veteran

66 Posts

Posted - 08/12/2014 :  14:34:30  Show Profile  Reply with Quote
Still won't work does not append any records, parentc_customer_code exits in the ARCustomer database

USE [companyT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[insert_sx_customer]

@sx_customer_code char(7),
@parent_customer_code char(7),
@email_address char(50),
@name char(30),
@telephone char(20)

as

BEGIN

SET NOCOUNT ON;

declare @date as datetime
set @date= getdate()
set @parent_customer_code = '2ND01A'
set @sx_customer_code = 'SX0001'
set @email_address = 'test@hotmail.com'
set @telephone = '001-123-4567'
set @name= 'Alex Smith'

INSERT INTO dbo.ArCustomer (Customer,[Name], Salesperson,CustomerClass,Branch,TermsCode,Area,CustomerOnHold,SoDefaultType,Email,UserField1,Contact,DateCustAdded)
SELECT @sx_customer_code,@name, Salesperson,CustomerClass,Branch,'P',Area,'Y','B',@email_address,'HOLD',@name,@date
from dbo.ArCustomer
where Customer like '%' + @parent_customer_code + '%'

END


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37457 Posts

Posted - 08/12/2014 :  14:40:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
Please see my last reply. Need the first question answered.

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

tkizer
Almighty SQL Goddess

USA
37457 Posts

Posted - 08/12/2014 :  14:41:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
By the way, for faster help, you should post your question like this: http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

Patyk
Yak Posting Veteran

66 Posts

Posted - 08/12/2014 :  14:52:08  Show Profile  Reply with Quote
Thank you I will try ... just back to this on here is my error

USE [companyT]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[insert_sx_customer]

SELECT 'Return Value' = @return_value

GO


Msg 201, Level 16, State 4, Procedure insert_sx_customer, Line 0
Procedure or function 'insert_sx_customer' expects parameter '@sx_customer_code', which was not supplied.

(1 row(s) affected)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37457 Posts

Posted - 08/12/2014 :  14:54:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
You need to pass values for the input parameters since they don't have default values. Remove the SET commands from your stored procedure for the input parameters.

EXEC [dbo].[insert_sx_customer] @sx_customer_code = 'somevalue', @parent_customer_code = 'some other value', .......

Remove these from the body of the stored procedure:
set @parent_customer_code = '2ND01A'
set @sx_customer_code = 'SX0001'
set @email_address = 'test@hotmail.com'
set @telephone = '001-123-4567'
set @name= 'Alex Smith'


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
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