INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to Insert duplicate data?

How to Insert duplicate data?

(OP)
So I am not sure the best way to approach this problem. I want to update a table the following way:

I want to create a duplicate record but put a tag on the old record, for example:


Name: John
Role: Manager
Rate: 22.00

Name: John
Role: Historical______Manager
Rate: 22.00


I was thinking this for the code but I was running into issues:

INSERT INTO Portfolio SELECT * FROM Portfolio
Where [Role] = 'Manager' and [Rate] = 22;

However I am getting errors when trying to run. Plus I am not sure how to append on the Historical______ prefix.

RE: How to Insert duplicate data?

Hi,

Of what purpose is Historical______ particularly the UNDERSCORE string of characters?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How to Insert duplicate data?

(OP)
Sometime the Role name changes or the Role rate changes, so I wanted to put Historical in the record to distinguish the two. That way we will have a track record of old records.

RE: How to Insert duplicate data?

You ought to have a status change date on each row and maybe a change status

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How to Insert duplicate data?

(OP)
That wouldn't be a bad idea, a time stamp on each row. Yeah I will give that a try. But my other question still stands on how I can take the rows that equal "Manager" and duplicate them and insert them back into the table with the time stamp.

RE: How to Insert duplicate data?

What is the error message and the exact SQL you used.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How to Insert duplicate data?

(OP)
Code:
INSERT INTO Portfolio SELECT * FROM Portfolio
Where [Role] = 'Manager' and [Rate] = 22;



Error:

Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 78 record(s) to the table due to key violations. 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

RE: How to Insert duplicate data?

How was your table constructed?
Does it have Keys: primary, foreign?
Referential integrity constraints?
Did any rows get added?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How to Insert duplicate data?

"due to key violations" looks like you are trying to copy Primary Key values again to the new records. You may want to list all fields (except the PK) in your Insert and Select part:

       INSERT ([Name], [Role], {Rate]) INTO Portfolio
VALUES (SELECT [Name], [Role], {Rate]  FROM Portfolio 
 Where [Role] = 'Manager' and [Rate] = 22;)
 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: How to Insert duplicate data?

(OP)
Ahhh you are right I didn't think of the primary key. Ha, I guess I take that auto number for granted. I will give that a try.

I will update the thread with my results

RE: How to Insert duplicate data?

(OP)
I am getting a "Syntax error in INSERT INTO statement"

RE: How to Insert duplicate data?

(OP)
I got it!

INSERT INTO Portfolio ([Mem_Name], [Role], [Rate])

SELECT [Mem_Name], [Role], [Rate] FROM Portfolio

Where [Role] = 'Manager' and [Rate] = 22;


Thanks for the help Skip and Andy!

RE: How to Insert duplicate data?

You are welcome, but that still does not solve your "Historical______" issue.
I would use Skip's suggestion (the way I understand it) and add a field into your table, like "Historical" with Yes/No, Y/N, True/False, -1/0, etc. and mark those "Historical" records that way.

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close