Inserting a new row into a table by copying an existing one and changing the key(s)
-
How would I select a row in a table, change its key value and insert it into the same table as a new row?
-
you mean
Insert...Select?
Examples below
--TestTable
Create table #Mytable (pk int identity,
Foo varchar(25),
Bar varchar(25))
--Insert TestData
insert into #Mytable (Foo, Bar)
Values('One', 'Mississippi')
insert into #Mytable (Foo, Bar)
Values('Two', 'Mississippi')
insert into #Mytable (Foo, Bar)
Values('Three', 'Mississippi')
-- Can Modify several ways.
insert into #Mytable (Foo, Bar)
select Foo, Bar
From #Mytable
where pk = 2
select *
from #Mytable
Results
1 One Mississippi
2 Two Mississippi
3 Three Mississippi
4 Two Mississippi
insert into #mytable (Foo, Bar)
select 'Four', 'Mississippi'
From #Mytable
where pk = 3
1 One Mississippi
2 Two Mississippi
3 Three Mississippi
4 Two Mississippi
5 Four Mississippi
BOL
-
insert table1 (col1, col, col3)
select 40000 + col1, col2 / 2.0, col3
from table1
where col4 < 33123
N 56°04'39.16"
E 12°55'05.25" -
Peter's example is *almost* what I need: I have a part master table where the part id is the PK, plus many columns. I created a "Generic Part" row which I would like to be able to copy to a new row when I need to create a new part (to save me the trouble of having to set a lot of default values each time for many of the columns), but
1) Changing the part id, or PK, to a different id, say "Part 123"
2) Not having to specify all the other columns separately, since there are so many of them
-
A few comments:
- if the purpose of this copy is to fill in default values - why not use the default value property of the various fields?
- your primary key is by definition constrained to be unique, so you won't be able to just copy the record exactly, and then modify it. At very least, you'll have to specify a new value for the primary key (or let it be auto-generated by a default value). The minute you do that - you need to specify the other columns. Now - you *might* be able to do that by storing the default values in another table (a.k.a all fields EXCEPT the primary key), so you could do:
insert <table>
select <new primary key here>,* from <default table>
I find this a bit ugly, since the minute anything changes in either table this will fall apart, but here's how you would do it if you want to go that way. in order to not specify field names, you have to make sure that the select returns the same number of fields, in the same order, so just pay some attention to that when building the default table structure.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
A few additional comments:
- That's PDM (Pretty Damn Lazy) even for a programmer. You have to write out the columns - what? - ONCE and then save it. Cut and paste from then on.
- Go ahead and define the defaults in the column definitions. Again, you only have to do it once and, believe me, you're going thru a lot more work now than if you had simply bitten the bullet and done it in the first place.
- Right click on the table and select "Edit". There! All the column definitions already written out for you. Beforehand, go ahead a define a default on one column and use that as a template for the others. CTRL-C and CTRL-V are your friends.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
Hey, PDM is the name of the game,here: I got started with computers in the late seventies when, as a new accountant, I swore to myself that there was no way in hell I was going to do all this repetitive, mind-numbing work manually for the rest of my career...
Anyway, thanks to all for your input, it will be put to good use.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply