create a stored procedure with insert and update using a cursor

Question

create a stored procedure with insert and update using a cursor

Viewing 15 posts - 31 through 45 (of 61 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

A Case Statement should do the trick:

insert into dbo.Customers (CustomerID, Name, ShortName, ReferenceNo, EffectiveDate, Status, CreateDate)

select A.Account_number, A.Customer_name, A.Known_As, A.Legacy_Account_Number, A.Account_Established_Date, CASE WHEN A.Customer_Status = 'I' THEN 0 ELSE 1 END, A.Account_Established_Date

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

paul.starr SSCrazy Points: 2505 More actions · Answer 2

there is a problem converting nvarchar to int

Conversion failed when converting the nvarchar value 'A' to data type int.

paul.starr SSCrazy Points: 2505 More actions · Answer 3

IS this the correct way to write this if i need to change a datatype from nvarchar to int:

select A.Account_number, A.Customer_name, A.Known_As, A.Legacy_Account_Number, A.Account_Established_Date, CASE WHEN A.Customer_Status = 'I' THEN 0 ELSE 1 END, A.Account_Established_Date

from

dbo.AVW_11i_WA_BILLTO_Customers A

left outer join dbo.Customers C

on A.Account_number = C.CustomerID

where

C.CustomerID is null

AND CAST (A.Customer_Status AS int(2))

GO

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

What is this trying to accomplish?

where

C.CustomerID is null

AND CAST (A.Customer_Status AS int(2))

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Also, an observation. BOL will answer many of your syntax questions. BOL should be your first place to look when in doubt about T-SQL syntax. Learn it well, even after more than 10 years working with SQL Server, I still use it a lot. It is my best SQL friend.

paul.starr SSCrazy Points: 2505 More actions · Answer 6

Need to change nvarchar to int

Case when A.Customer_Status = 'I' THEN CAST('0' AS int). Will this work

paul.starr SSCrazy Points: 2505 More actions · Answer 7

I can alter this stored procedure but i get this error:

Conversion failed when converting the nvarchar value 'A' to data type int

Why do i keep getting this error when i run the sp??

select A.Account_number, A.Customer_name, A.Known_As, A.Legacy_Account_Number, A.Account_Established_Date, CASE WHEN A.Customer_Status = 'I' THEN CAST ('0' AS int) ELSE CAST ('1' AS int) END, A.Account_Established_Date

from

dbo.AVW_11i_WA_BILLTO_Customers A

left outer join dbo.Customers C

on A.Account_number = C.CustomerID

where

C.CustomerID is null

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Seeing that the table definition you posted earlier doesn't have the base types used, you are going to have to start comparing the base data types between the view and the target table. Something isn't what you think it is.

paul.starr SSCrazy Points: 2505 More actions · Answer 9

status from Customers table is datatype int

Status from view is NOT NULL VARCHAR2(1)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

We are going to need the DDL for the tables and the view (for SQL Server 2005, not Oracle), sample data (in the form of insert statements) to load into the tables, your current code, and the expected results.

The article I keep mentioning tells you how to generate most of this.

The only way we can help at this point is to do some work on our own systems and see what we can come up with.

paul.starr SSCrazy Points: 2505 More actions · Answer 11

im a little confused on that, all i understand is that you want me to create a table,how many columns do i need?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

We need the DDL for the tables and views you are working with. This means generating the create scripts using SSMS. We then need sample data to work with. This means you need write (or script some how) data in the form of insert statements we can then cut, paste, and run in SSMS to load the tables we create with your DDL statements. You then need to show us what the results of the process will be based on the sample data provided.

We also need the EXACT code you currently have that isn't working, so we have a starting point to work from.

All of this is covered in the article http://www.sqlservercentral.com/articles/Best+Practices/61537/ that we have been asking you to read.

paul.starr SSCrazy Points: 2505 More actions · Answer 13

here is a create table example

CREATE TABLE [dbo].[Customers](

[CustomerID] [dbo].[C_ID] NOT NULL,

[Name] [dbo].[C_Name] NOT NULL,

[ShortName] [dbo].[C_Caption] NULL,

[ReferenceNo] [dbo].[C_Name] NULL,

[EffectiveDate] [dbo].[C_Date] NOT NULL CONSTRAINT [DF__Customers__Effec__220B0B18] DEFAULT (getdate()),

[Status] [dbo].[C_Status] NULL CONSTRAINT [DF__Customers__Statu__26CFC035] DEFAULT ((1))

insert into dbo.customers

(CustomerID, Name, ShortName, ReferenceNo, EffectiveDate, Status)

values('48313', 'ALLIED PROD(PORTLAND)', 'ALLIED', '3046969-086', '7/2/2008 10:31:12 AM', '1')

datatypes

CustomerId= nvarchar(30)

Name=nvarchar(50)

ShortName=nvarchar(20)

ReferenceNo=nvarchar(50)

EffectiveDate=(datetime)

Status=int(null)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Lynn Pettis

SSC Guru

Points: 442467

November 11, 2008 at 4:04 pm

#897210

I'm done.

paul.starr SSCrazy Points: 2505 More actions · Answer 15

what else do you need from me ?

here are the view datatypes from oracle and the output to update from

Account_number NOT NULL VARCHAR2(30)

Customer_name VARCHAR2(150)

Known_As VARCHAR2(150)

Legacy_Account_Number VARCHAR2(150)

Account_Established_Date Date

Customer_Status NOT NULL VARCHAR2(1)

Account_Established_Date Date

VIEW VALUES

Customer_Name

MACPAC

Legacy_ACCOUNT_NUMBER

3054165-086

Account_Number

48315

Name

BGL INDUSTRIES

Status

A

Account_Established_Date

NULL

Known_AS

10