Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

cursor appropriate? Expand / Collapse
Author
Message
Posted Monday, November 22, 2010 5:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, Visits: 350
is cursor needed for following scenario?

create table #table1 (col1 int,col2 varchar(30))
insert into #table1 values (1,'martin')
insert into #table1 values (2,'tom')
insert into #table1 values (3,'schrof')

create table #table2(t_col1 int identity(1,1),t_col2 int)

create table #table3 (f_col1 int identity(1,1),f_col2 varchar(30),f_col3 int)

1.i have to select the values from table1 and insert the value of table1.col1 into table2.t_col2

2. fetch the auto increment value and insert into table3.f_col3 and insert the value of table1.col2 into table3.f_col3


cursor is necessary to achieve this?.. any suggestion pls?
Post #1024247
Posted Monday, November 22, 2010 6:04 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, November 21, 2013 11:33 PM
Points: 1,481, Visits: 1,959
Not needed

Depending on uniqueness something like
insert into #table2 (t_col2) select col1 from #table1
insert into #table3 (f_col3) select t_col1 from #table2 t2 join #table1 t1 on t1.col1 = t2.t_col2

could work.

If one would actually need to insert the values one at a time i would use a while instead. Like
declare @i int
set @i = 0

while exists (select * from #table1 where col1 > @i)
begin
select top 1 @i = col1 from #table1 where col1 > @i
insert into #table2 (t_col2) values (@i)
insert into #table3 (f_col3) values(SCOPE_IDENTITY())
end

/T
Post #1024264
Posted Monday, November 22, 2010 6:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, Visits: 350
Great thanks tommy
Post #1024288
Posted Monday, November 22, 2010 6:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
MonsterRocks (11/22/2010)
is cursor needed for following scenario?

create table #table1 (col1 int,col2 varchar(30))
insert into #table1 values (1,'martin')
insert into #table1 values (2,'tom')
insert into #table1 values (3,'schrof')

create table #table2(t_col1 int identity(1,1),t_col2 int)

create table #table3 (f_col1 int identity(1,1),f_col2 varchar(30),f_col3 int)

1.i have to select the values from table1 and insert the value of table1.col1 into table2.t_col2

2. fetch the auto increment value and insert into table3.f_col3 and insert the value of table1.col2 into table3.f_col3


cursor is necessary to achieve this?.. any suggestion pls?


You don't need a cursor or a WHILE loop, SQL Server has the OUTPUT clause for this purpose. See BOL, the examples are excellent.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1024293
Posted Monday, November 22, 2010 8:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, Visits: 350
create table #table1 (col1 int,col2 varchar(30))
insert into #table1 values (1,'martin')
insert into #table1 values (2,'tom')
insert into #table1 values (3,'schrof')

create table #table2(t_col1 int identity(1,1),t_col2 int)

create table #table3 (f_col1 int identity(1,1),f_col2 varchar(30),f_col3 int)




insert into #table2 (t_col2) output inserted.t_col1,t.col1 into #table3(f_col2,f_col3) select col1 from #table1 t


i am getting folowing error

The multi-part identifier "t.col1" could not be bound.


what could be the problem?...any suggestion pls
Post #1024360
Posted Monday, November 22, 2010 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
MonsterRocks (11/22/2010)
create table #table1 (col1 int,col2 varchar(30))
insert into #table1 values (1,'martin')
insert into #table1 values (2,'tom')
insert into #table1 values (3,'schrof')

create table #table2(t_col1 int identity(1,1),t_col2 int)

create table #table3 (f_col1 int identity(1,1),f_col2 varchar(30),f_col3 int)




insert into #table2 (t_col2) output inserted.t_col1,t.col1 into #table3(f_col2,f_col3) select col1 from #table1 t





i am getting folowing error

The multi-part identifier "t.col1" could not be bound.


what could be the problem?...any suggestion pls





“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1024366
Posted Monday, November 22, 2010 9:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, Visits: 350
Chris.. the links in ur signature u want me to check out?
Post #1024430
Posted Monday, November 22, 2010 10:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:59 AM
Points: 6,544, Visits: 8,761
Try this:
if object_id('tempdb..#table1') IS NOT NULL DROP TABLE #table1;
if object_id('tempdb..#table2') IS NOT NULL DROP TABLE #table2;
if object_id('tempdb..#table3') IS NOT NULL DROP TABLE #table3;

create table #table1 (col1 int,col2 varchar(30));
insert into #table1 values (1,'martin');
insert into #table1 values (2,'tom');
insert into #table1 values (3,'schrof');

create table #table2(t_col1 int identity(1,1),t_col2 int);
create table #table3 (f_col1 int identity(1,1),f_col2 varchar(30),f_col3 int);

insert into #table2 (t_col2) output inserted.t_col1, inserted.t_col2 into #table3(f_col2,f_col3)
select col1 from #table1 t;



Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1024476
Posted Monday, November 22, 2010 11:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:16 PM
Points: 5,986, Visits: 6,931
Removed, inaccurate.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1024521
Posted Monday, November 22, 2010 12:28 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 1,059, Visits: 5,758
MonsterRocks (11/22/2010)
Chris.. the links in ur signature u want me to check out?


@MonsterRocks - Sorry mate, got tied up, but Wayne jumped in with solution.
@Wayne - cheers mate, sorted.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1024586
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse