December 13, 2011 at 11:00 am
help, i am trying to select a row of data and insert this data into a table while incrementing a specific field.every field stays the same except for the on being incremented....see code that i have below...it inserts first letter of each column and loop doesnt break..
declare @sum as varchar
set @sum = '50'
DECLARE @QUANTITY varchar
DECLARE @SERIAL_NUMBER varchar
DECLARE @TRAN_DATE varchar
DECLARE @TRAN_TYPE varchar
DECLARE @AVERAGE_COST varchar
DECLARE @PRICE varchar
DECLARE @ITEM_CODE varchar
DECLARE @ITEM_DESC varchar
DECLARE @ITEM_DIVISION varchar
DECLARE @ITEM_CLASS varchar
DECLARE @UPC_CODE varchar
DECLARE @tac varchar
DECLARE @fac varchar
DECLARE @CUST_NUMBER varchar
DECLARE @CUSTOMER_NAME varchar
DECLARE @INVOICE_NUMBER varchar
DECLARE @PO_NUMBER varchar
DECLARE db_cursor CURSOR FOR
SELECT TRAN_DATE,TRAN_TYPE, AVERAGE_COST,PRICE,ITEM_CODE,ITEM_DESC,
ITEM_DIVISION,ITEM_CLASS,UPC_CODE,SERIAL_NUMBER,TAC,FAC,
CUST_NUMBER,CUSTOMER_NAME,INVOICE_NUMBER,PO_NUMBER, QUANTITY
FROM [S2K].[dbo].[Test_Sales]
order by 1
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @TRAN_DATE,@TRAN_TYPE,@AVERAGE_COST,@PRICE,@ITEM_CODE,@ITEM_DESC,
@ITEM_DIVISION,@ITEM_CLASS,@UPC_CODE,@SERIAL_NUMBER,@tac,@FAC,
@CUST_NUMBER,@CUSTOMER_NAME,@INVOICE_NUMBER,@PO_NUMBER,@QUANTITY
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SERIAL_NUMBER = @SERIAL_NUMBER
WHILE (@SERIAL_NUMBER<=@QUANTITY + @SERIAL_NUMBER)
BEGIN
INSERT INTO [S2K].[dbo].[Test_Sales]
(
[TRAN_DATE],[TRAN_TYPE],[AVERAGE_COST],[PRICE],[ITEM_CODE],[ITEM_DESC],
[ITEM_DIVISION],[ITEM_CLASS],[UPC_CODE],[SERIAL_NUMBER],[TAC],
[FAC],[CUST_NUMBER],[CUSTOMER_NAME],[INVOICE_NUMBER],[PO_NUMBER],
[QUANTITY]
)
VALUES
(
@TRAN_DATE,
@TRAN_TYPE,@AVERAGE_COST,@PRICE,@ITEM_CODE,@ITEM_DESC,
@ITEM_DIVISION,@ITEM_CLASS,@UPC_CODE,@SERIAL_NUMBER,
@tac,@FAC,@CUST_NUMBER,@CUSTOMER_NAME,@INVOICE_NUMBER,
@PO_NUMBER,@QUANTITY
)
SET @SERIAL_NUMBER = @SERIAL_NUMBER + @sum
IF(@SERIAL_NUMBER >= @SERIAL_NUMBER + @sum)
BREAK
ELSE
CONTINUE
END
FETCH NEXT FROM db_cursor
END
CLOSE db_cursor
DEALLOCATE db_cursor
December 13, 2011 at 11:25 am
Welcome to SSC. It is really difficult to figure out what you are trying to do. It seems that you want to insert some data and have a sequential number added to the data as a new column. This can be done without a cursor. In fact, almost everything in sql can be done without a cursor.
There are lots of volunteers around here willing and able to help but we need some details. ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. If you need help or want details about how to post this information see the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 12:48 pm
Thank you for ur advise
this is a row of data from the table which contains multiple record query must move to each row and analyze
TRAN. DATE ITEM CODE SERIAL NUMBER CUST. NUMBER CUSTOMER NAME QUANTITY
20111212819832170009 300521102351 SD-360 TREVOR PAUL BAKSH 350
below is what i would like to be inserted into second table
quantity- which is 350 divided by 50 = 7
so i need seven rows
serialnumber field (300521102351) is incremented by 50 (hence customer bought 7 packs)
every other column stays the same only serialnumber is incremented by50
TRAN. DATE ITEM CODE SERIALNUMBER CUSTNUMBER CUSTOMER NAME QUANTITY
20111212819832170009300521102351 SD-360 TREVOR PAUL BAKSH 350
20111212819832170009300521102401 SD-360 TREVOR PAUL BAKSH 350
20111212819832170009300521102451 SD-360 TREVOR PAUL BAKSH 350
20111212819832170009300521102501 SD-360 TREVOR PAUL BAKSH 350
20111212819832170009300521102551 SD-360 TREVOR PAUL BAKSH 350
20111212819832170009300521102601 SD-360 TREVOR PAUL BAKSH 350
20111212819832170009300521102651 SD-360 TREVOR PAUL BAKSH 350
i hope this explanation helps
December 13, 2011 at 12:54 pm
omalie24 (12/13/2011)
...every field stays the same except for the on being incremented....see code that i have below...it inserts first letter of each column and loop doesnt break..
That's because all the variables are are declared as varchar with no length, so default length 1.
So when you grab the values from the table and putting them into variables, it's only grabbing the first one.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 13, 2011 at 12:58 pm
toddasd (12/13/2011)
omalie24 (12/13/2011)
...every field stays the same except for the on being incremented....see code that i have below...it inserts first letter of each column and loop doesnt break..That's because all the variables are are declared as varchar with no length, so default length 1.
So when you grab the values from the table and putting them into variables, it's only grabbing the first one.
I just skimmed over that detail because of that huge ugly cursor staring me in the face. I saw big ole mean nasty cursor and wanted to kill it. I went into death trance and totally missed reading the rest of the post. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 1:07 pm
Sean Lange (12/13/2011)
toddasd (12/13/2011)
omalie24 (12/13/2011)
...every field stays the same except for the on being incremented....see code that i have below...it inserts first letter of each column and loop doesnt break..That's because all the variables are are declared as varchar with no length, so default length 1.
So when you grab the values from the table and putting them into variables, it's only grabbing the first one.
I just skimmed over that detail because of that huge ugly cursor staring me in the face. I saw big ole mean nasty cursor and wanted to kill it. I went into death trance and totally missed reading the rest of the post. :hehe:
I don't blame you at all. That is a big ole hunk of mess right there, cher. 😛
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 13, 2011 at 1:11 pm
omalie24, this is the type of info we need to help you. I typed this up to get you started.
create table [dbo].[Test_Sales] (
[TRAN_DATE] varchar(10),[ITEM_CODE] varchar(20), [SERIAL_NUMBER] bigint,
[CUSTOMER_NUMBER] varchar(20), [CUSTOMER_NAME] varchar(20),[QUANTITY] int);
INSERT INTO [dbo].[Test_Sales]
([TRAN_DATE],[ITEM_CODE],[SERIAL_NUMBER],[CUSTOMER_NUMBER],[CUSTOMER_NAME],[QUANTITY])
VALUES
('20111212','819832170009',300521102351,'SD-360','TREVOR PAUL BAKSH',350);
Notice that I've taken the liberty of changing some of the data types to be more appropriate. Consider this.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 13, 2011 at 1:34 pm
the data type in the first table is like that as a result of me importing the data from a excel sheet
CREATE TABLE [dbo].[Test_Sales](
[TRAN_DATE] [varchar](50) NULL,
[TRAN_TYPE] [varchar](50) NULL,
[AVERAGE_COST] [varchar](50) NULL,
[PRICE] [varchar](50) NULL,
[ITEM_CODE] [varchar](50) NULL,
[ITEM_DESC] [varchar](50) NULL,
[ITEM_DIVISION] [varchar](50) NULL,
[ITEM_CLASS] [varchar](50) NULL,
[UPC_CODE] [varchar](50) NULL,
[SERIAL_NUMBER] [varchar](50) NULL,
[TAC] [varchar](50) NULL,
[FAC] [varchar](50) NULL,
[CUST_NUMBER] [varchar](50) NULL,
[CUSTOMER_NAME] [varchar](50) NULL,
[INVOICE_NUMBER] [varchar](50) NULL,
[PO_NUMBER] [varchar](50) NULL,
[QUANTITY] [varchar](50) NULL
)
INSERT INTO [S2K].[dbo].[Test_Sales]
([TRAN_DATE]
,[TRAN_TYPE]
,[AVERAGE_COST]
,[PRICE]
,[ITEM_CODE]
,[ITEM_DESC]
,[ITEM_DIVISION]
,[ITEM_CLASS]
,[UPC_CODE]
,[SERIAL_NUMBER]
,[TAC]
,[FAC]
,[CUST_NUMBER]
,[CUSTOMER_NAME]
,[INVOICE_NUMBER]
,[PO_NUMBER]
,[QUANTITY])
VALUES
(‘20111212’,‘S’,431.03’,’431.03’,‘819832170009’,’ACTIVE FLEX VOUCHER $500’,‘970’,‘1’,‘ ’,‘300521102351’,‘0’,‘0’,‘SD-360’,’TREVOR PAUL’,
‘3720’,‘0’,‘350’)
December 13, 2011 at 2:47 pm
omalie24 (12/13/2011)
the data type in the first table is like that as a result of me importing the data from a excel sheet
Ok, but understand that if you want to make this into a database, you will be better off having the column type tightly bound to the type of data. Storing everything as a string is asking for a lot of problems down the road.
That being said, why are you bringing this into a database if it is workable in a spreadsheet?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 13, 2011 at 4:20 pm
Ok, here is a way to do this with using a loop. I'm showing this way because I think you will understand this the most. You should in general avoid cursors and loops, but your level is way too low and this gets the job done.
First, create the scenario;
--the original table
create table [dbo].[Test_Sales] (
[TRAN_DATE] varchar(10),[ITEM_CODE] varchar(20), [SERIAL_NUMBER] bigint,
[CUSTOMER_NUMBER] varchar(20), [CUSTOMER_NAME] varchar(20),[QUANTITY] int);
--the destination copy table
create table [dbo].[Test_Sales2] (
[TRAN_DATE] varchar(10),[ITEM_CODE] varchar(20), [SERIAL_NUMBER] bigint,
[CUSTOMER_NUMBER] varchar(20), [CUSTOMER_NAME] varchar(20),[QUANTITY] int);
--initial row
INSERT INTO [dbo].[Test_Sales]
([TRAN_DATE],[ITEM_CODE],[SERIAL_NUMBER],[CUSTOMER_NUMBER],[CUSTOMER_NAME],[QUANTITY])
VALUES
('20111212','819832170009',300521102351,'SD-360','TREVOR PAUL BAKSH',350);
And here is the codez (god, i hate writing it like that, but it seems apropo)
declare @SN bigint
Set @SN = 300521102351
declare @SN_New bigint
Set @SN_New = @SN
declare @Inc int
Set @Inc = 50
declare @QTY int
select @QTY = Quantity from [dbo].[Test_Sales] where Serial_Number = @SN;
declare @Pack_Count int
Set @Pack_Count = ceiling(@QTY / @Inc);
while @Pack_Count > 0
begin
Set @SN_New = @SN_New + @Inc;
INSERT INTO [dbo].[Test_Sales2]
([TRAN_DATE],[ITEM_CODE],[SERIAL_NUMBER],[CUSTOMER_NUMBER],[CUSTOMER_NAME],[QUANTITY])
select [TRAN_DATE],[ITEM_CODE],@SN_New,[CUSTOMER_NUMBER],[CUSTOMER_NAME],[QUANTITY]
from [dbo].[Test_Sales] where Serial_Number = @SN;
Set @Pack_Count = @Pack_Count - 1;
end
/*
select * from Test_Sales2;
drop table Test_Sales;
drop table Test_Sales2;
*/
And finally, here is a way of doing it without using a naughty loop:
declare @SN bigint
Set @SN = 300521102351
declare @SN_New bigint
Set @SN_New = @SN
declare @Inc int
Set @Inc = 50
declare @QTY int
select @QTY = Quantity from [dbo].[Test_Sales] where Serial_Number = @SN;
declare @Pack_Count int
Set @Pack_Count = ceiling(@QTY / @Inc);
INSERT INTO [dbo].[Test_Sales2]
([TRAN_DATE],[ITEM_CODE],[SERIAL_NUMBER],[CUSTOMER_NUMBER],[CUSTOMER_NAME],[QUANTITY])
select [TRAN_DATE],[ITEM_CODE],NewSN,[CUSTOMER_NUMBER],[CUSTOMER_NAME],[QUANTITY]
from Test_Sales t1
inner join Tally t on t.N <= @Pack_Count
cross apply (select t1.SERIAL_NUMBER + (@Inc * t.N) NewSN from Test_Sales) a
where Serial_Number = @SN;
To find out what a Tally table is and how to create one, do a search on this site.
Edit: forgot a "where" clause. Good thing this ain't production!
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 13, 2011 at 6:03 pm
thanks alot for taking the time to assist me i appreciate it toddasd...i now see that declaring everything as varchar has came back to bite me..it was easy at the time since sql server was screaming at me for declare some variables as integer etc....i normally receive this and similar data via spreadsheet....i then import into my report server from where my reports are generated....all of these processes are automated..the data giving me hell are some new ones....am trying to maintain my automation....will try when i go to work tomorrow thanks again for ur help.
December 14, 2011 at 8:01 am
HEY....IT WORKS...BUT I NEEDED IT TO CYCLE THROUGH MULTIPLE ROWS OF DATA WHICH HAS DIFFERENT SERIAL NUMBERS.....I KINDA FIXED IT.....I KNOW U WOULDNT LIKE THIS BUT I WRAPPED UR CODE IN A CURSOR AND IT WORKS...
December 14, 2011 at 8:06 am
omalie24 (12/14/2011)
HEY....IT WORKS...BUT I NEEDED IT TO CYCLE THROUGH MULTIPLE ROWS OF DATA WHICH HAS DIFFERENT SERIAL NUMBERS.....I KINDA FIXED IT.....I KNOW U WOULDNT LIKE THIS BUT I WRAPPED UR CODE IN A CURSOR AND IT WORKS...
🙂 Glad you got it to work. Exciting isn't it?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 14, 2011 at 8:08 am
omalie24 (12/14/2011)
HEY....IT WORKS...BUT I NEEDED IT TO CYCLE THROUGH MULTIPLE ROWS OF DATA WHICH HAS DIFFERENT SERIAL NUMBERS.....I KINDA FIXED IT.....I KNOW U WOULDNT LIKE THIS BUT I WRAPPED UR CODE IN A CURSOR AND IT WORKS...
BLAH BLAH BLAH I can't hear you over the cries of pain watching you continuing to use cursors. You are correct that you "kinda fixed it". Honestly, I am glad you were able to resolve your issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 14, 2011 at 8:25 am
Yes toddasd it is….
Sean Lange how would u have gotten it to move to the next row without a cursor
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply