cursor appropriate?

Question

Post reply

cursor appropriate?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 2

but i have 20 columns in table1...Should i add 20 columns in table2 too?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 4

BeginnerBug

Hall of Fame

Points: 3539

November 23, 2010 at 5:17 am

#1254353

ok thanks a lot chris....