Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


cursor appropriate?


cursor appropriate?

Author
Message
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6243 Visits: 10403
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, 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

BeginnerBug
BeginnerBug
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 350
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
BeginnerBug
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 350
any idea ?... cursor inevitable?....
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9005 Visits: 19028
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
Exploring Recursive CTEs by Example Dwain Camps
BeginnerBug
BeginnerBug
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 350
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9005 Visits: 19028
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), [table1_col3] varchar(30));

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

--- here in table3.f_col4 i need to store the value of table1.col3
--- will output clause work in this scenario?
--- You cannot add random columns from the source table to the OUTPUT column list,
--- but you could add a column for it to #Table2 as follows
--- You could of course remove it later.

insert into #table2 (t_col2, [table1_col3])
output inserted.t_col1, inserted.t_col2, inserted.[table1_col3]
into #table3 (f_col2, f_col3, [table1_col3])
select col2, col3 from #table1 t;

SELECT * FROM #table2
SELECT * FROM #table3



“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
BeginnerBug
BeginnerBug
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 350
but i have 20 columns in table1...Should i add 20 columns in table2 too?
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9005 Visits: 19028
MonsterRocks (11/23/2010)
but i have 20 columns in table1...Should i add 20 columns in table2 too?


Surely just the keys (primary keys) would be sufficient?

“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
BeginnerBug
BeginnerBug
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 350
ok thanks a lot chris....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search