cursor appropriate?

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  November 22, 2010 at 5:22 am

  #141558

  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?

• tommyh

  SSCertifiable

  Points: 6302

  More actions

  November 22, 2010 at 6:04 am

  #1253681

  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

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  November 22, 2010 at 6:29 am

  #1253704

  Great thanks tommy

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 22, 2010 at 6:33 am

  #1253709

  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

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  November 22, 2010 at 8:03 am

  #1253757

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 22, 2010 at 8:06 am

  #1253763

  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

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  November 22, 2010 at 9:18 am

  #1253821

  Chris.. the links in ur signature u want me to check out?

• WayneS

  SSC Guru

  Points: 95409

  More actions

  November 22, 2010 at 10:10 am

  #1253858

  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
  Author - SQL Server T-SQL Recipes

  ---

  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
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  November 22, 2010 at 11:02 am

  #1253898

  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[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  November 22, 2010 at 12:28 pm

  #1253953

  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.

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• WayneS

  SSC Guru

  Points: 95409

  More actions

  November 22, 2010 at 12:51 pm

  #1253974

  ChrisM@home (11/22/2010)

  ---

  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.

  :satisfied:

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  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
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  November 22, 2010 at 10:27 pm

  #1254173

  Thanks a lot for valuable ideas.. but what i am trying to do is

  just like the following code...

  from the select statement few values wil be inserted in one table and based on its result other values will be inserted in some other

  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, t.col2 into #table3(f_col2,f_col3)

  select col1,col2 from #table1 t;

  quite difficult :sick:

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  November 23, 2010 at 3:49 am

  #1254291

  any idea ?... cursor inevitable?....

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 23, 2010 at 3:59 am

  #1254308

  MonsterRocks (11/23/2010)

  ---

  any idea ?... cursor inevitable?....

  Don't think so. Try this, it's the same as yours with the little errors removed, mostly column types:

  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 VARCHAR(30));

  create table #table3 (f_col1 int identity(1,1), f_col2 INT, f_col3 VARCHAR(30));

  insert into #table2 (t_col2)

  output inserted.t_col1, inserted.t_col2

  into #table3(f_col2,f_col3)

  select col2 from #table1 t;

  SELECT * FROM #table2 -- three rows

  SELECT * FROM #table3 -- three rows. Column1 = identity column of #Table3, Column2 = identity column of #Table2

  ^{“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

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  November 23, 2010 at 4:34 am

  #1254325

  thanks Chris... still i have a doubt.. i explained that in following code. i changed tables added columns in fact...

  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),col3 varchar(30));

  insert into #table1 values (10,'martin','teacher');

  insert into #table1 values (20,'tom','trainer');

  insert into #table1 values (30,'schrof','student');

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

  create table #table3 (f_col1 int identity(1,1), f_col2 INT, f_col3 VARCHAR(30),f_col4 VARCHAR(30));

  --- here in table3.f_col4 i need to store the value of table1.col3

  ---- will output clause work in this scenario?

  insert into #table2 (t_col2)

  output inserted.t_col1, inserted.t_col2

  into #table3(f_col2,f_col3)

  select col2 from #table1 t;

  SELECT * FROM #table2 -- three rows

  SELECT * FROM #table3

  Thanks for ur guidance