1

I have trouble duplicating rows from a MS Access table. This is a table I have. My (PK) in this table are BID, Order_nr, Parameter_ID and TimeStamp.

This is a query I have now, but it doesnt work; says "INSERT INTO syntax error"

INSERT INTO Measurement_test ( BID, Order_nr, Parameter_ID, Value, Machine_Serial_nr, TimeStamp, Passed)
SELECT  BID, '12345', Parameter_ID, Value, Machine_Serial_nr, TimeStamp, Passed
FROM Measurement_test
WHERE BID = 123;

I want to copy all those rows and create new rows while setting the Order_nr to 12345. How can I accomplish this?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Roman Siabro
  • 25
  • 7
  • There is allready a solution for that here: https://stackoverflow.com/questions/2783150/mysql-how-to-copy-rows-but-change-a-few-fields which works for my other table, but not for this one. Please help :) – Roman Siabro Jun 20 '17 at 09:41
  • Also, if `Order_nr` is PK, it should be Unique. If your select statement returns more than one rows, (or you have already a record with Order_nr = 12345) it going to fail. – Derenir Jun 20 '17 at 09:47
  • As you can see my PK consist of 4 fields combination, so when I duplicate - each new row created is unique as I give it new Order_nr. And that new Order_nr assigned doesnt exists in the table before running the query – Roman Siabro Jun 20 '17 at 09:53
  • Is Order_nr a string? – Shadow Jun 20 '17 at 09:56
  • No, it is integer(number) – Roman Siabro Jun 20 '17 at 09:58

1 Answers1

0

You have several Reserved Words in your column names:

Value, TimeStamp

You need to enclose them with square brackets.

And if Order_nr is integer, don't use quotes around the value.

INSERT INTO Measurement_test ( BID, Order_nr, Parameter_ID, [Value], Machine_Serial_nr, [TimeStamp], Passed)
SELECT  BID, 12345, Parameter_ID, [Value], Machine_Serial_nr, [TimeStamp], Passed
FROM Measurement_test
WHERE BID = 123;
Andre
  • 26,751
  • 7
  • 36
  • 80
  • Thanks, but it doesnt work yet. Now, first I get a message that I am about to append (1) number of rows and after clicking Yes, I get another error message which says: MS Access set 0 fields to Null due to type coversion failure , and didnt add 1 record to the table due to keys violation, 0 due to lock violations and 0 records due to validation rules violation – Roman Siabro Jun 20 '17 at 10:42
  • So as I understand, something is wrong with Primary keys – Roman Siabro Jun 20 '17 at 10:45
  • I found a mistake in my primary table, it works perfectly. THANK YOU! I give upvote, but as I am a new user, no one can see it ;/ – Roman Siabro Jun 20 '17 at 11:05