Select Into Stored Procedure

[Patrick]

Hello

I have many different tables, that most have the same structure.. they all
have some primary key, some fields and mylinkid ... so what I want to make
is to create a stored procedure, where I can pass the table-name, primary
key, and the new mylinkid, what should happen is, that the procedure copies
the entrie, where i passed the id, to a new row and the mylink-id should get
the new value...

example:
mytable 1
id=1 (primary key autoincrement)
mylinkid=233
field1=asdf
field2=blabla

i call my procedure like: duplicate_entry 'mytable',1,4

the result should then be
id=2 (primary key/autincrement)
mylinkid=4
field1=asdf
field2=blabla

I was thinking to make a Stored Procedure, that copies the entier row with a
select * into mytable (SELECT * from mytable where id=1) and afterwards i
update the mylinkid ..... but there i have problem with the primary key.

The other solution that will work, but I won't like is to get with the
sp_fields all fields from that specific table, build the select into with
the correct fields without the id-field... that should work, but was asking
myself if there is somethign better...

Has someone a good idea for that?

Thanks

[hansje]

I could do this in a DTS package without much difficulty. In an SP, the
main problem is using a variable tablename in an SQL statement is not
possible, as far as I know.
A solution could be using a CASE statement and using your tablename to
go to the right INSERT. Since you claim all tables are structural
identical copying those statements to build the CASE would be quick.

The INSERT would be something like this:

INSERT INTO MYTABLE (mylinkid,field1,field2)
SELECT @Mynewlinkid, field1, field2
FROM mytable
WHERE ID = @Keyfield

One wonders ofcourse why you have split an entity in different tables.

Hth,

Hans Brouwer

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Patrick]

yes, i know that works, but the problem is, that i don't know the sctructure
of the tables, they can have field1, field2 but can also have 20 fields...
so this should be built dynamicly, i can make a stored-procedure that takes
out all fields from that table and makes that. the handling of the different
table is the smallest problem, i just pass the table_name as varchar and
then i build a string and run that with EXEC ... :-)

"hansje" <ha*****@anonymous.com> schrieb im Newsbeitrag
news:3f*********************@news.frii.net...

I could do this in a DTS package without much difficulty. In an SP, the
main problem is using a variable tablename in an SQL statement is not
possible, as far as I know.
A solution could be using a CASE statement and using your tablename to
go to the right INSERT. Since you claim all tables are structural
identical copying those statements to build the CASE would be quick.

The INSERT would be something like this:

INSERT INTO MYTABLE (mylinkid,field1,field2)
SELECT @Mynewlinkid, field1, field2
FROM mytable
WHERE ID = @Keyfield

One wonders ofcourse why you have split an entity in different tables.

Hth,

Hans Brouwer

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[mountain man]

"Patrick" <pa********@bluemail.ch> wrote in message
news:10***************@fuchs.cyberlink.ch...

Hello

I have many different tables, that most have the same structure.. they all
have some primary key, some fields and mylinkid ... so what I want to make
is to create a stored procedure, where I can pass the table-name, primary
key, and the new mylinkid, what should happen is, that the procedure copies the entrie, where i passed the id, to a new row and the mylink-id should get the new value...

example:
mytable 1
id=1 (primary key autoincrement)
mylinkid=233
field1=asdf
field2=blabla

i call my procedure like: duplicate_entry 'mytable',1,4

the result should then be
id=2 (primary key/autincrement)
mylinkid=4
field1=asdf
field2=blabla

I was thinking to make a Stored Procedure, that copies the entier row with a select * into mytable (SELECT * from mytable where id=1) and afterwards i
update the mylinkid ..... but there i have problem with the primary key.

The other solution that will work, but I won't like is to get with the
sp_fields all fields from that specific table, build the select into with
the correct fields without the id-field... that should work, but was asking myself if there is somethign better...

Has someone a good idea for that?

I will work if you break it up into sections beginning

if mytable = 'somespecifictable'
begin
put your specific CODE here
end

You may be able to script this specific code
into the source proc if the number of tables is large.

[hansje]

I am not sure then whether I understand your problem. If I understand
correctly, you can generate the right INSERT statement, for instance by
using SYSOBJECTS and SYSCOLUMNS.
You can add a new record, which is a duplicate of a selected record,
exept for the key. Now you wish to change the field MYLINKID; to do
this you can retrieve the value of the last inserted record with
@@IDENTITY(see BOL) and next update this record with your new MYLINKID.

Have I got this right, or do I still miss something?

Tnx,

Hans Brouwer

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Patrick]

no you're right. That's the option i see .. :-) but was looking for a better
one... instead of generating the insert statement..
"hansje" <ha*****@anonymous.com> schrieb im Newsbeitrag
news:3f*********************@news.frii.net...

I am not sure then whether I understand your problem. If I understand
correctly, you can generate the right INSERT statement, for instance by
using SYSOBJECTS and SYSCOLUMNS.
You can add a new record, which is a duplicate of a selected record,
exept for the key. Now you wish to change the field MYLINKID; to do
this you can retrieve the value of the last inserted record with
@@IDENTITY(see BOL) and next update this record with your new MYLINKID.

Have I got this right, or do I still miss something?

Tnx,

Hans Brouwer

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Patrick]

yes, but the main idea is, that i don't have to know how the tables look
like. because its always like that, they have a primary key, an mylinkid and
i want to copy it always on the same way. so users can create another table
in the same format, and just use that stored procedure to duplicate the
content with another mylinkid
"mountain man" <hobbit@southern_seaweed.com.op> schrieb im Newsbeitrag
news:_1********************@news-server.bigpond.net.au...

"Patrick" <pa********@bluemail.ch> wrote in message
news:10***************@fuchs.cyberlink.ch...

Hello

I have many different tables, that most have the same structure.. they all have some primary key, some fields and mylinkid ... so what I want to make is to create a stored procedure, where I can pass the table-name, primary key, and the new mylinkid, what should happen is, that the procedure copies

the entrie, where i passed the id, to a new row and the mylink-id should

get

the new value...

example:
mytable 1
id=1 (primary key autoincrement)
mylinkid=233
field1=asdf
field2=blabla

i call my procedure like: duplicate_entry 'mytable',1,4

the result should then be
id=2 (primary key/autincrement)
mylinkid=4
field1=asdf
field2=blabla

I was thinking to make a Stored Procedure, that copies the entier row with a

select * into mytable (SELECT * from mytable where id=1) and afterwards

i update the mylinkid ..... but there i have problem with the primary key.

The other solution that will work, but I won't like is to get with the
sp_fields all fields from that specific table, build the select into with the correct fields without the id-field... that should work, but was

asking

myself if there is somethign better...

Has someone a good idea for that?

I will work if you break it up into sections beginning

if mytable = 'somespecifictable'
begin
put your specific CODE here
end

You may be able to script this specific code
into the source proc if the number of tables is large.