473,573 Members | 2,719 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

3 New Member
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:
Expand|Select|Wrap|Line Numbers
  1. select * into #temptable
  2. from originaltable 
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. insert into originaltable
  2. select column3, column4, column5, .., column48
  3. from #temptable;
Columns 1 and 2 are the RecID and RecGUID. RecID is the PrimaryKey, and RecGUID is a uniqueidentifie r. #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
Feb 6 '08 #1
5 2762
ck9663
2,878 Recognized Expert Specialist
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:
Expand|Select|Wrap|Line Numbers
  1. select * into #temptable
  2. from originaltable 
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. insert into originaltable
  2. select column3, column4, column5, .., column48
  3. from #temptable;
Columns 1 and 2 are the RecID and RecGUID. RecID is the PrimaryKey, and RecGUID is a uniqueidentifie r. #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
Feb 6 '08 #2
Sieldan
3 New Member
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 uniqueidentifie r.

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.
Feb 6 '08 #3
ck9663
2,878 Recognized Expert Specialist
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 uniqueidentifie r.

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
Feb 7 '08 #4
Sieldan
3 New Member
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.
Feb 7 '08 #5
ck9663
2,878 Recognized Expert Specialist
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...(co z you already deleted it)

-- ck
Feb 8 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
2422
by: Marko Poutiainen | last post by:
Situation: We had to make our SQLServer 2000 database multi-lingual. That is, certain things (such as product names) in the database should be shown in the language the user is using (Finnish, Swedish or English). There are about a dozen tables with columns that need localization. Doing this in the application level was a no-goer. It...
23
2208
by: Eva | last post by:
Hi i am trying to insert a new row into one of my datatabels that i have in my dataset when a button is clicked. here is my code Dim ClientInsRow As DataRow = dtClient.NewRo ClientInsRow("Surname") = txtSurname.Tex ClientInsRow("Forename") = txtForename.Tex ClientInsRow("OrgName") = txtOrganisation.Tex ClientInsRow("Address") =...
4
1820
by: Support | last post by:
Hi, I want to know if I have changed a few records in my database using update / insert / delete methods, how can i later know which rows have been changed or modified ? I know the ExecuteNonQuery method which can give me the "number" of modified rows, but which rows are changed how do I know ? Any ideas ?
4
1378
by: Grey | last post by:
I used .NET to generate a GUID for creating a new record in Access DB as primary key. Is it guaratee to be unique in the DB as the GUID is not generate in DB?? If so, is that mean GUID is generated based on machine instead of application, right?? Million Thanks
4
1358
by: Maxood | last post by:
I want to generate a primary key in ASP.Net through VB.Net code.Can someone give some tips and ideas how to do that.What i am thinking is to: 1.Create an array of values(characters) from 0 to 9 and 'A' to 'Z'. 2.Then use a nested For loop to create various combinations. 3.Then checking whether these combinations exist in the field of the...
1
4481
by: Vish | last post by:
Hi All, I am trying to insert a GUID generated in .NET into my SQL Server tables. I am inserting it into a column of type uniqueidentifier. The GUID generated by ..NET has lower case alphabets in it but after i insert it into the SQL Server tables, all the lower case alphabets get converted into upper case alphabets. This causes problems...
4
5887
by: kageyone | last post by:
I have an access database with a table with two fields. They are 1. a GUID field and 2. a comma delimited set of values. I want to take this table and for each row I want to do the following: copy the GUID to a new rows field 1 parse out the first value of the csv field and copy to a new rows field 2 Continue to write new rows with...
5
5693
by: dos360 | last post by:
Hello, I have two tables, one is a list of activities, the other a list of participants. I want to insert one record in the activities table and then using its identity column as foreign key, I want to insert two or more records into the participants table. My problem is that I have no idea what foreign key to use when inserting names...
2
3582
by: aeblank | last post by:
THE PROBLEM I'm running into performance issues generating and storing a randomly created graph in a SQL Server database. I have a T-SQL script that generates a graph, and then randomly connects the vertices in that graph to each other. I can see that my hard-drive is working very hard throughout the operation. The operation took about 2 hours (I...
0
8206
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7796
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8077
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6426
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5601
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5294
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3734
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3739
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1316
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.