473,721 Members | 2,062 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 2787
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
2425
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 would have taken far too much time (there is a *lot* of code and unfortunately most of the...
23
2230
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") = txtAddress.Tex
4
1843
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
1395
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
1361
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 table or not. Can someone suggest a better way of doing that.Is it a good idea to create a primary...
1
4487
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 when i retreive it and compare to the one stored locally in my program. Isn't the GUID case...
4
5893
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 the GUID and the next value in the csv field until all values have been written to new rows in a new...
5
5709
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 into the participants table. How can I get hold of the row's key or identity column, immediately...
2
3592
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 just canceled it at this point, looked like it was about 10% done) with 200,000 vertices and an...
0
8852
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8736
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9373
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9081
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8020
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6676
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4761
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3206
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2143
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.