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 12:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 6,594, Visits: 8,877
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.



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 #1024607
Posted Monday, November 22, 2010 10:27 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, 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
Post #1024839
Posted Tuesday, November 23, 2010 3:49 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
any idea ?... cursor inevitable?....
Post #1024978
Posted Tuesday, November 23, 2010 3:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Post #1024995
Posted Tuesday, November 23, 2010 4:34 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, 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
Post #1025019
Posted Tuesday, November 23, 2010 4:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Post #1025030
Posted Tuesday, November 23, 2010 5:07 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
but i have 20 columns in table1...Should i add 20 columns in table2 too?
Post #1025044
Posted Tuesday, November 23, 2010 5:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Post #1025047
Posted Tuesday, November 23, 2010 5:17 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
ok thanks a lot chris....
Post #1025052
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse