SQL Statement to insert record in the same table

Last Post 07 Apr 2006 04:00 AM by jag8699. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
jag8699
New Member
New Member

--
03 Apr 2006 05:21 AM
I have a table with 3 fields and 5 records, I want to write 1 statement will do the following
open cursor
select * from table1 where key = 'key'.
fetch cursor
move table1-field1 to table1-field1.
move table1-field2 to table1-field2.
move 'key' to table1-field3.
insert into table1.
close cursor.

Is their a possiblity that their can be statement

insert table1-field1,table1-field2,key into (select * from table1 where key = 'key')

thanks for help.
nosepicker
New Member
New Member

--
03 Apr 2006 06:43 AM
If I understand you correctly, yes, you absolutely can. Here is a simplified example:

create table #tmp_insert (
col1 int null,
col2 varchar(5) null)

insert into #tmp_insert values(1, 'abc')
insert into #tmp_insert values(2, 'def')

insert into #tmp_insert
select * from #tmp_insert
where col1 = 1

Results:
col1 col2
1 abc
2 def
1 abc
jag8699
New Member
New Member

--
07 Apr 2006 04:00 AM
I found another command which is more simple

insert into <table> select * from <table> where <condition>.

nosepicker
New Member
New Member

--
08 Apr 2006 03:17 PM
Hello ... that's what I did:

insert into #tmp_insert
select * from #tmp_insert
where col1 = 1

The rest of what I wrote was just to create the table and populate it with some sample data.
You are not authorized to post a reply.

Acceptable Use Policy