Would SQL Prohibt Copy Record - 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

General SQL Server Forums

New to SQL Server Programming

Would SQL Prohibt Copy Record

Author

Topic

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-03-26 : 17:27:36

Good Day,

I have an SQL 2005 Server running with a table called
Active and a linked table called Recall.

In my .net APP I wrote a VB code that will copy an
existing record and create a duplicate with a new PK ID.

Now here is the problem, The copy record works perfect if
I create a new record and then copy that record. I can
press my copy button and it will make as many copies as
I want. When I try to copy a record that is pre-existing
from an import I did about 6 months ago it looks like it
works but the record is never copied.

My question come down to, Is their something within SQL that
would prohibt a record from being copied ?

If the answer is yes, Can anyone advise where to look to
change setting in SQL or what type of correction I should
pursue.

I am new to SQL and coming from a foxpro arena, Which is also
where the imported data came from..

Any advice would be great.

Thank You..

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 21:55:07

Why are you doung this in the app?
Simpler to pass the PK to an SP and let it do the insert.

As to why it doesn't work with old rows - probably because there's something wrong with the data you are trying to insert (notice how easy this would be to check with the SP?).
You are maybe getting an error but not trapping iy.
Try using the profiler to trace the sql executed and then execute it im management studio in a transaction with a rollback to see if it errors.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-04-09 : 13:10:36

As I am coming from Visual Foxpro into SQL
I am not that great with SP's. Any chance
you might have a base example you could
share to get me in the right direction.

Thanx

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-09 : 13:14:48

depends on you structure something like this for an identity PK. It takes the PK to copy and returns the new PK as an output parameter.

create proc s_CopyRec
@PK int ,
@NewPKVal int
as
insert mytbl (col1, col2, col3)
select col1, col2, col3
from mytbl
where PKCol = @PKCol

select @NewPKVal = scope_identity()
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 13:16:52

[code]CREATE PROCEDURE CopyRecord
@PKValue int
AS

INSERT INTO YourTable (Field1,Field2,..)
SELECT Field1,Field2,..
FROM YourTable
WHERE PKCol=@PKValue

GO[/code]

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-04-09 : 16:22:28

Thanks, I will work with the examples givin..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources