InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 13:52:56 GMT20http://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspxok thanks a lot chris....Tue, 23 Nov 2010 05:17:44 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspx[quote][b]MonsterRocks (11/23/2010)[/b][hr]but i have 20 columns in table1...Should i add 20 columns in table2 too?[/quote]Surely just the keys (primary keys) would be sufficient?Tue, 23 Nov 2010 05:12:37 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspxbut i have 20 columns in table1...Should i add 20 columns in table2 too?Tue, 23 Nov 2010 05:07:46 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspx[code="sql"]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 [/code]Tue, 23 Nov 2010 04:51:02 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspxthanks Chris... still i have a doubt.. i explained that in following code. i changed tables added columns in fact...[code="sql"]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 rowsSELECT * FROM #table3 [/code]Thanks for ur guidanceTue, 23 Nov 2010 04:34:53 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspx[quote][b]MonsterRocks (11/23/2010)[/b][hr]any idea ?... cursor inevitable?....[/quote]Don't think so. Try this, it's the same as yours with the little errors removed, mostly column types:[code="sql"]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 rowsSELECT * FROM #table3 -- three rows. Column1 = identity column of #Table3, Column2 = identity column of #Table2[/code]Tue, 23 Nov 2010 03:59:23 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspxany idea ?... cursor inevitable?....Tue, 23 Nov 2010 03:49:09 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspxThanks a lot for valuable ideas.. but what i am trying to do isjust 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[code="sql"]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;[/code]quite difficult :sick:Mon, 22 Nov 2010 22:27:20 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspx[quote][b]ChrisM@home (11/22/2010)[/b][hr][quote][b]MonsterRocks (11/22/2010)[/b][hr]Chris.. the links in ur signature u want me to check out?[/quote]@MonsterRocks - Sorry mate, got tied up, but Wayne jumped in with solution. @Wayne - cheers mate, sorted.[/quote]:satisfied:Mon, 22 Nov 2010 12:51:14 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspx[quote][b]MonsterRocks (11/22/2010)[/b][hr]Chris.. the links in ur signature u want me to check out?[/quote]@MonsterRocks - Sorry mate, got tied up, but Wayne jumped in with solution. @Wayne - cheers mate, sorted.Mon, 22 Nov 2010 12:28:22 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspxRemoved, inaccurate.Mon, 22 Nov 2010 11:02:20 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspxTry this:[code="sql"]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;[/code]Mon, 22 Nov 2010 10:10:51 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspxChris.. the links in ur signature u want me to check out?Mon, 22 Nov 2010 09:18:55 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspx[quote][b]MonsterRocks (11/22/2010)[/b][hr][code="sql"]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,[b][size="4"]t.[/size][/b]col1 into #table3(f_col2,f_col3) select col1 from #table1 t[/code]i am getting folowing errorThe multi-part identifier "t.col1" could not be bound.what could be the problem?...any suggestion pls[/quote]Mon, 22 Nov 2010 08:06:59 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspx[code="sql"]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[/code]i am getting folowing errorThe multi-part identifier "t.col1" could not be bound.what could be the problem?...any suggestion plsMon, 22 Nov 2010 08:03:13 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspx[quote][b]MonsterRocks (11/22/2010)[/b][hr]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_col3cursor is necessary to achieve this?.. any suggestion pls?[/quote]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.Mon, 22 Nov 2010 06:33:41 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspxGreat thanks tommyMon, 22 Nov 2010 06:29:48 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspxNot neededDepending on uniqueness something like [code="sql"]insert into #table2 (t_col2) select col1 from #table1insert into #table3 (f_col3) select t_col1 from #table2 t2 join #table1 t1 on t1.col1 = t2.t_col2[/code]could work.If one would actually need to insert the values one at a time i would use a while instead. Like[code="sql"]declare @i intset @i = 0while 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[/code]/TMon, 22 Nov 2010 06:04:50 GMTtommyhhttp://www.sqlservercentral.com/Forums/Topic1024247-149-1.aspxis 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_col3cursor is necessary to achieve this?.. any suggestion pls?Mon, 22 Nov 2010 05:22:53 GMTBeginnerBug