Inserting Rows from a copy with a Primary Key, and a GUID

[Sieldan]

I'm trying to throw some data around so that I can manipulate it more easily. Unfortunately, I don't really know what I'm doing. :) So I'm creeping my way through it step by step.

What I have done so far is find the rows of data I need to work with, copied them into a temporary table, and removed them from the original table. Now I am trying to send the manipulated rows back to the original table. The hitch is the original table has two columns that are unique, a RecID, and a RecGUID. If I try to copy all of the columns from the temp table, I could get duplicate RecIDs and RecGUIDs. However, if I try to copy all of the columns minus the RecID and RecGUID I get an error saying that the number of columns does not match the table definition.

Here is how I created the data in #temptable:

select * into #temptable
from originaltable 
where userID = @userID

I am guessing that I have to tell it to generate a RecID and RecGUID, but I have no idea how to do that. Here is the relevent bit of query code that attempts to copy the rows back into originaltable from #temptable:

insert into originaltable
select column3, column4, column5, .., column48
from #temptable;

Columns 1 and 2 are the RecID and RecGUID. RecID is the PrimaryKey, and RecGUID is a uniqueidentifier. #temptable is a copy of relevent rows from originaltable. Once I figure out the row insert issue, Ill tackle the manipulation that I need to do. But one step at a time.

So, my questions are these:
-How do I code the query to tell the server to generate new IDs and GUIDs for the copied rows?
-And less important, is there a better way to list all the columns except for columns 1 and 2? Thats a lot of typing. :)

Thanks for looking and for your time.
Sieldan

[ck9663]

I'm trying to throw some data around so that I can manipulate it more easily. Unfortunately, I don't really know what I'm doing. :) So I'm creeping my way through it step by step.

What I have done so far is find the rows of data I need to work with, copied them into a temporary table, and removed them from the original table. Now I am trying to send the manipulated rows back to the original table. The hitch is the original table has two columns that are unique, a RecID, and a RecGUID. If I try to copy all of the columns from the temp table, I could get duplicate RecIDs and RecGUIDs. However, if I try to copy all of the columns minus the RecID and RecGUID I get an error saying that the number of columns does not match the table definition.

Here is how I created the data in #temptable:

select * into #temptable
from originaltable 
where userID = @userID

I am guessing that I have to tell it to generate a RecID and RecGUID, but I have no idea how to do that. Here is the relevent bit of query code that attempts to copy the rows back into originaltable from #temptable:

insert into originaltable
select column3, column4, column5, .., column48
from #temptable;

Columns 1 and 2 are the RecID and RecGUID. RecID is the PrimaryKey, and RecGUID is a uniqueidentifier. #temptable is a copy of relevent rows from originaltable. Once I figure out the row insert issue, Ill tackle the manipulation that I need to do. But one step at a time.

So, my questions are these:
-How do I code the query to tell the server to generate new IDs and GUIDs for the copied rows?
-And less important, is there a better way to list all the columns except for columns 1 and 2? Thats a lot of typing. :)

Thanks for looking and for your time.
Sieldan

to answer your question first...

although it's easy to generate a random unique number, you might want to ask your systems analysts the convention and algorithm used to generate those unique identifiers. in some system they have some coding ie. if the first 3 char represents something...

yes there is. if you have a query analyzer, change the output to text. run a select * from yourtable where 1 = 2. it will display all fields on your table. copy and paste it to your window. you just have to insert comma (,).

why did you have to copy it to the temp table? if you're worried that you might mess up the data, backup your entire table first. then manipulate it as it is. just don't forget the WHERE USERID = @USERID in every query...

-- ck

[Sieldan]

to answer your question first...

although it's easy to generate a random unique number, you might want to ask your systems analysts the convention and algorithm used to generate those unique identifiers. in some system they have some coding ie. if the first 3 char represents something...

yes there is. if you have a query analyzer, change the output to text. run a select * from yourtable where 1 = 2. it will display all fields on your table. copy and paste it to your window. you just have to insert comma (,).

why did you have to copy it to the temp table? if you're worried that you might mess up the data, backup your entire table first. then manipulate it as it is. just don't forget the WHERE USERID = @USERID in every query...

-- ck

CK,

Thanks for the reply.

Unfortunatly, there is no systems analyst, well other than me. :)

I can tell you that the RecID is an incremental 4 digit number, and that RecGUID is a uniqueidentifier.

As far as copying the table around, the original table could possibly become quite large and I know the dataset I want to work with will be finite (no more than 50 rows) and I'm just trying to minimize 'actions' on that table.

[ck9663]

CK,

Thanks for the reply.

Unfortunatly, there is no systems analyst, well other than me. :)

I can tell you that the RecID is an incremental 4 digit number, and that RecGUID is a uniqueidentifier.

As far as copying the table around, the original table could possibly become quite large and I know the dataset I want to work with will be finite (no more than 50 rows) and I'm just trying to minimize 'actions' on that table.

1. make sure that no one else is working on the table.
2. you're right in creating a temp table for the recordset that you just need
3. manipulate the temp table to whatever you need. if you did not change the RecID and RecGUID that means you can put it back.
4. backup your original table
5.delete those records that you have on the original table that you already copied on the temp table.
6. append back the temp table on your original table. since you deleted the records with the same RecID and RecGUID on the original table there will be no duplicate. if it becomes too messy, revert back to your backup...

-- ck

[Sieldan]

1. make sure that no one else is working on the table.
2. you're right in creating a temp table for the recordset that you just need
3. manipulate the temp table to whatever you need. if you did not change the RecID and RecGUID that means you can put it back.
4. backup your original table
5.delete those records that you have on the original table that you already copied on the temp table.
6. append back the temp table on your original table. since you deleted the records with the same RecID and RecGUID on the original table there will be no duplicate. if it becomes too messy, revert back to your backup...

-- ck

Well, at least my plan of attack is valid. :)
1. Gonna lock the original table when I pull the records, and when I put them back. Inbetween, it needs to be open. Its part of a back end for a e-commerce website.
2. Cool, got that part right.
3. Not sure how Im going to do it yet, but I might be adding a record or two, and deleting a few. Im figuring on RecID and RecGUID changing.
4. Cant believe I didn't think about that. Good call. Thanks.
5. Yup, got that.
6. This is where Im currently having problems. Right now, Im just trying to copy back (with a new RecID and RecGUID) the records that I have deleted from the original table.

[ck9663]

Well, at least my plan of attack is valid. :)
1. Gonna lock the original table when I pull the records, and when I put them back. Inbetween, it needs to be open. Its part of a back end for a e-commerce website.
2. Cool, got that part right.
3. Not sure how Im going to do it yet, but I might be adding a record or two, and deleting a few. Im figuring on RecID and RecGUID changing.
4. Cant believe I didn't think about that. Good call. Thanks.
5. Yup, got that.
6. This is where Im currently having problems. Right now, Im just trying to copy back (with a new RecID and RecGUID) the records that I have deleted from the original table.

on #6, if you already delete the same record that you copied to your temp, then it's no longer in your original table. when you append it back, there will be no duplicate...(coz you already deleted it)

-- ck