copy record data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

copy record data

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2006-04-11 : 13:24:41

I have a copy record feature on my program. The data is in 1 tables. I need to copy all the fields in one record and insert a new record with that data and return an ID #. What would the SQL look like?

Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-11 : 13:30:20

Let's say that Column1 is the identity, so we need to exclude it.

DECLARE @i int

INSERT INTO SomeTable(Column2, Column3, Column4)
SELECT Column2, Column3, Column4
FROM SomeTable
WHERE SomeColumn = 'SomeValue'

SET @i = SCOPE_IDENTITY()

Tara Kizer
aka tduggan

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-11 : 13:33:27

if u want to add the same record to another table
U can do by a Stored Procedure or a trigger. Which way u want it?

if it is just adding data to a table and return the ID# u may do it with a stored procedure
Also is ID# is an identity column ?

Srinika

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-11 : 13:34:26

I see Tara beat me, but here's what I did anyway (it's essentially the same)...

--data
declare @t table (id int identity(1, 1), a int, b int, c int)
insert @t select 1, 2, 3 union all select 4, 5, 6

--inputs
declare @fromId int
set @fromId = 1

--calculation
insert @t
select a, b, c from @t where id = @fromId

--results
select scope_identity() as id
select * from @t

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

gotafly
Yak Posting Veteran

54 Posts

Posted - 2006-04-11 : 13:48:10

Thanks all

gotafly
Yak Posting Veteran

54 Posts

Posted - 2006-04-13 : 08:06:38

I forgot to add one thing. I am passing a variable in. I need to change the name field. The rest of the fields are coppied.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-13 : 09:40:57

"I am passing a variable in. I need to change the name field. The rest of the fields are coppied."


insert into target_table(col2, col3, col4)
select col2, @para1, col4
from   source_table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources