April 11, 2012 at 6:50 am
create proc whil1234
as
--Declare @plant_code varchar(20)
--set @plant_code= (select plant from plant_code where plant = (select plant_code from march_2012))
DECLARE @first_in numeric(20)
DECLARE @last_in numeric(20)
DECLARE q cursor for select first_in,last_in from march_2012
--SET @first_in =(select first_in from march_2012 where plant_code= @plant_code)
--SET @last_in =(select last_in from march_2012 where plant_code= @plant_code )
OPEN q
fetch next from q into @first_in,@last_in
WHILE (@@FETCH_STATUS=0) and (@First_in <=@last_in)
BEGIN
Insert into num_ber Values(@first_in)
--exec sp_executesql @Q
PRINT @first_in
SET @first_in = @first_in+1
END
CLOSE Q
DEALLOCATE Q
April 11, 2012 at 8:17 am
Is there a question here? And why do you need a loop for this?
_______________________________________________________________
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/
April 11, 2012 at 8:22 am
Actually, I would be amazed if the coded posted actually did anything.
April 11, 2012 at 8:39 am
Lynn Pettis (4/11/2012)
Actually, I would be amazed if the coded posted actually did anything.
It looked like it would do something...nothing good...but something....
create table march_2012
(
first_in numeric(20),
last_in numeric(20)
)
--create some dummy data
insert march_2012
select N, N + 1 from Tally where N < 11
create table num_ber
(
wtf numeric(20)
)
go
create proc whil1234
as
begin
--Declare @plant_code varchar(20)
--set @plant_code= (select plant from plant_code where plant = (select plant_code from march_2012))
DECLARE @first_in numeric(20)
DECLARE @last_in numeric(20)
DECLARE q cursor for select first_in,last_in from march_2012
--SET @first_in =(select first_in from march_2012 where plant_code= @plant_code)
--SET @last_in =(select last_in from march_2012 where plant_code= @plant_code )
OPEN q
fetch next from q into @first_in,@last_in
WHILE (@@FETCH_STATUS=0) and (@First_in <=@last_in)
BEGIN
Insert into num_ber Values(@first_in)
--exec sp_executesql @Q
PRINT @first_in
SET @first_in = @first_in+1
END
CLOSE Q
DEALLOCATE Q
end
go
exec whil1234
go
select * from num_ber
This will create 2 rows in the num_ber table.
There are just so many things wrong with this it is hard to begin.
numeric(20)? Do you really have integer numbers that long? (12345678901234567890)
Table name of march_2012 makes me think the data is probably not normalized, or at least should be partitioned.
Why loop?
Setting the variable inside the loop, I guess this was done to avoid the endless loop because there is no fetch next inside the cursor.
_______________________________________________________________
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/
April 11, 2012 at 10:15 pm
thx alot cheers!
April 11, 2012 at 10:21 pm
i want to insert values in number tables from march_2012 from first in to last in in series
1
2
3
4
in march tables can have many first in and last in
help me if you can
thx
April 12, 2012 at 7:10 am
paramjit4 (4/11/2012)
i want to insert values in number tables from march_2012 from first in to last in in series1
2
3
4
in march tables can have many first in and last in
help me if you can
thx
Sure I can help. However first you have to help me to help you. I can't possibly help without something to run code against. That means you need to post ddl, sample data and desired output based on the sample data. Take a look at the first link in my signature for more details. Once you post that you will find lots of people willing and able to help.
_______________________________________________________________
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/
April 12, 2012 at 7:14 am
And just for the record there is absolutely no reason at all you need a cursor to do this. 😉
_______________________________________________________________
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/
April 17, 2012 at 11:43 pm
--pls help me now !!!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[march_20121]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[march_2012]
GO
CREATE TABLE [dbo].[march_20121] (
[first_in] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[last_in] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
insert into march_20121 values(12671,12679)
insert into march_20121 values(12571,12579)
go
create table num_ber
(
num numeric(20)
)
go
create proc whil1234
as
begin
--Declare @plant_code varchar(20)
--set @plant_code= (select plant from plant_code where plant = (select plant_code from march_2012))
DECLARE @first_in numeric(20)
DECLARE @last_in numeric(20)
DECLARE q cursor for select first_in,last_in from march_2012
--SET @first_in =(select first_in from march_2012 where plant_code= @plant_code)
--SET @last_in =(select last_in from march_2012 where plant_code= @plant_code )
OPEN q
fetch next from q into @first_in,@last_in
WHILE (@@FETCH_STATUS=0) and (@First_in <=@last_in)
BEGIN
Insert into num_ber Values(@first_in)
--exec sp_executesql @Q
PRINT @first_in
SET @first_in = @first_in+1
END
CLOSE Q
DEALLOCATE Q
end
go
exec whil1234
go
select * from num_ber
April 18, 2012 at 7:46 am
Thanks for the ddl and sample data. Can you explain what you are trying to do? What does the commented stuff about @plant_code do? What you have posted will just insert those values from march_20121 into num_bers.
If you can explain what you want we can help.
_______________________________________________________________
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/
April 18, 2012 at 10:58 pm
i want to insert number into num_ber table from march_20121
linto series
like this
12671
12672
12673
12674
12675
12676
12677
12678
12679
12571
12572
12573
12574
12575
12576
12577
12578
12579
April 19, 2012 at 7:47 am
So you want to insert all values between first_in and last_in? You absolutely do not need a loop for this. You need what is called a numbers, or tally, table. Jeff Moden has a great article about that here. http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/url%5D
Once you have read that article and understand how to build your tally and understand it's use you can do something like the following. No loops, no cursors. You have the benefit that it is super duper fast, and it is really easy to understand.
--generate march_20121 table
;with march_20121 (first_in, last_in)
as ( select 12579, 12671)
Insert into num_ber
select N
from march_20121
cross apply Tally
where N >= first_in and N <= last_in
order by N
_______________________________________________________________
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/
May 11, 2012 at 4:01 am
Sean Lange (4/19/2012)
No loops, no cursors.
Hey, wait a minute! That's part of my mantra! :w00t:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 11, 2012 at 8:04 am
dwain.c (5/11/2012)
Sean Lange (4/19/2012)
No loops, no cursors.
Hey, wait a minute! That's part of my mantra! :w00t:
LOL
_______________________________________________________________
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/
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply