Need a way to clone rows in SQL [modified]
-
I'm literally trying to clone data rows, nothing very complicated, infact, they aren't even going to be changed during this section of the project. I've captured the rows I want to clone using a custom SQL query, but I'm not sure how to insert them back into the database with new ID's(primary key is autonumber)... can't I just run a .update of some sort? I've tried copying the data table from results of my query and merging into the complete table, that doesn't make any change. I dont seem to be able to .insert anything other then independant values for each column. I'm using VS 2005 and C#. Thanks. -- modified at 17:09 Monday 24th July, 2006
-
I'm literally trying to clone data rows, nothing very complicated, infact, they aren't even going to be changed during this section of the project. I've captured the rows I want to clone using a custom SQL query, but I'm not sure how to insert them back into the database with new ID's(primary key is autonumber)... can't I just run a .update of some sort? I've tried copying the data table from results of my query and merging into the complete table, that doesn't make any change. I dont seem to be able to .insert anything other then independant values for each column. I'm using VS 2005 and C#. Thanks. -- modified at 17:09 Monday 24th July, 2006
Just run an insert ignoring the autonumber primary key. It will be filled in for you.
INSERT INTO MyTable ( Field1, Field2, Field3 ) SELECT Field1, Field2, Field3 FROM MyTable WHERE Field2 = 'MyCriteria' -
Just run an insert ignoring the autonumber primary key. It will be filled in for you.
INSERT INTO MyTable ( Field1, Field2, Field3 ) SELECT Field1, Field2, Field3 FROM MyTable WHERE Field2 = 'MyCriteria'I'm trying that, and I dont think I'm doing something right in the query. Is field1 supposed to be the primary key?(as per default)? I'm getting "primary key doesn't allow nulls" error, and my field1 is set to the autonum. By autonum, I mean that SQL is set for INT for primary key, and I've got my dataset flagged to incriment 1, seed 1. I think this is the correct way to set for autonum? I'm used to there being a default "autonum" from access for the field type... -- modified at 18:26 Monday 24th July, 2006
-
I'm trying that, and I dont think I'm doing something right in the query. Is field1 supposed to be the primary key?(as per default)? I'm getting "primary key doesn't allow nulls" error, and my field1 is set to the autonum. By autonum, I mean that SQL is set for INT for primary key, and I've got my dataset flagged to incriment 1, seed 1. I think this is the correct way to set for autonum? I'm used to there being a default "autonum" from access for the field type... -- modified at 18:26 Monday 24th July, 2006
try..... insert into Employees SELECT * from Employees where name = 'Joe' Watch out for Unquie fields and indentity.
Look where you want to go not where you don't want to crash. Bikers Bible
-
I'm trying that, and I dont think I'm doing something right in the query. Is field1 supposed to be the primary key?(as per default)? I'm getting "primary key doesn't allow nulls" error, and my field1 is set to the autonum. By autonum, I mean that SQL is set for INT for primary key, and I've got my dataset flagged to incriment 1, seed 1. I think this is the correct way to set for autonum? I'm used to there being a default "autonum" from access for the field type... -- modified at 18:26 Monday 24th July, 2006
Do not specify the primary key. A new one will be generated for you. Note: If you expect to get a full clone (copy the primary key also) then you will never accomplish this in a single table. Primary keys can not be duplicated.
-
Do not specify the primary key. A new one will be generated for you. Note: If you expect to get a full clone (copy the primary key also) then you will never accomplish this in a single table. Primary keys can not be duplicated.
If I use INSERT INTO tbABC SELECT (all but primary key) FROM tbABC WHERE Name = @Name I get an error back saying that the Column Name or number of supplied values does not match the table definition. If I use INSERT INTO tbABC SELECT * FROM tbABC WHERE Name = @Name I get a name saying that the primary key can't be duplicated (duh)... I've been looking all over the table design and properties in SQL and I can't find where I can actually pick an autonumber for the field type. I thought that inside my dataset I'd set to AutoIncriment, seed 1, step 1.. isn't this correctly setting autonumbering for an SQL database? I'm used to access just having autonum as a field type choice. Thanks.
-
If I use INSERT INTO tbABC SELECT (all but primary key) FROM tbABC WHERE Name = @Name I get an error back saying that the Column Name or number of supplied values does not match the table definition. If I use INSERT INTO tbABC SELECT * FROM tbABC WHERE Name = @Name I get a name saying that the primary key can't be duplicated (duh)... I've been looking all over the table design and properties in SQL and I can't find where I can actually pick an autonumber for the field type. I thought that inside my dataset I'd set to AutoIncriment, seed 1, step 1.. isn't this correctly setting autonumbering for an SQL database? I'm used to access just having autonum as a field type choice. Thanks.
Sweet! I just found out I can flag the primary key as the identity column, now it works!!! Yay! Thanks alot for your help guys. -- modified at 15:04 Tuesday 25th July, 2006