Create new records based on existing records
JR
have a table that has say 150 columns of data. I have data in the
unique key column of "objects" with say:
123456
234567
345678
456789
567890
678901
Then for each of those of course is tied the other 149 columns of
data. I have a list of values say:
234567second
345678second
So as you can see the first part of the second list matches values in
the first list. I can obviousl strip out the word "second" so they
match the values in the DB, however how would I duplicate the matching
"objects" value, including all the data in it's column, and create a
new record with that data however call the "objects" value
234567second instead of 234567? So when all is said and done, there
will be
234567 ....
234567second ....
Both which have identical data except for the "objects" value being
different as shown above, and the same with 345678.
Any ideas? I would hate to have to put all 150 values in an insert
query and put 150 column labels in as part of the insert statement.
Thanks.
JR
Tom Cooper
data base, open the object browser (F8 opens the object browser, another F8
will close it). Expand the items in the object browser until you get to
your table with the 150 columns. Right click on the table name, choose
script object to clipboard as insert. Then paste into the query window.
You will get two lines, an INSERT line and a VALUES line, delete the VALUES
line. Now, click on the table name, choose script object to clipboard as
insert. Then paste into the query window below the INSERT line. Now you
have all 150 column names.
Now you have an INSERT query which would duplicate every row in your table
(and, of course, give you errors on your unique key column). So change the
SELECT "objects" to SELECT "objects" + 'second'. Now you have a query with
will duplicate every row as a ...second. You are on your own wirting the
WHERE clause that will select only those rows you want to duplicate.
Tom
"JR" <jri...@yahoo.com> wrote in message
news:8bbdc227-32a5-4d08...@p69g2000hsa.googlegroups.com...
sloan
Try this:
declare @baseTable table ( id int , nameof varchar(32) )
insert into @baseTable ( id , nameof ) values ( 1 , 'Apple' )
insert into @baseTable ( id , nameof ) values ( 2 , 'Banana')
insert into @baseTable ( id , nameof ) values ( 3 , 'Cantaloupe' )
declare @suffixTable table ( id int , suffix varchar(32) )
insert into @suffixTable ( id , suffix ) values ( 2 , 'second')
insert into @suffixTable ( id , suffix ) values ( 3 , 'third' )
select
bt.nameof + st.suffix as TheValue
from @baseTable bt c
"JR" <jri...@yahoo.com> wrote in message
news:8bbdc227-32a5-4d08...@p69g2000hsa.googlegroups.com...
JR
wrote:
> No need to type 150 column names, QA will do it for you. In QA go to your
> data base, open the object browser (F8 opens the object browser, another F8
> will close it). Expand the items in the object browser until you get to
> your table with the 150 columns. Right click on the table name, choose
> script object to clipboard as insert. Then paste into the query window.
> You will get two lines, an INSERT line and a VALUES line, delete the VALUES
> line. Now, click on the table name, choose script object to clipboard as
> insert. Then paste into the query window below the INSERT line. Now you
> have all 150 column names.
>
> Now you have an INSERT query which would duplicate every row in your table
> (and, of course, give you errors on your unique key column). So change the
> SELECT "objects" to SELECT "objects" + 'second'. Now you have a query with
> will duplicate every row as a ...second. You are on your own wirting the
> WHERE clause that will select only those rows you want to duplicate.
>
> Tom
>
>
Thanks for the reply. Your second "script object to clipboard as
insert" was that supposed to be a "script object to clipboard as
select"? You had insert twice and guessing you mean't to have a
select as that second part.
Thanks.
JR
JR
> Try this:
>
> declare @baseTable table ( id int , nameof varchar(32) )
>
> insert into @baseTable ( id , nameof ) values ( 1 , 'Apple' )
>
> insert into @baseTable ( id , nameof ) values ( 2 , 'Banana')
>
> insert into @baseTable ( id , nameof ) values ( 3 , 'Cantaloupe' )
>
> declare @suffixTable table ( id int , suffix varchar(32) )
>
> insert into @suffixTable ( id , suffix ) values ( 2 , 'second')
>
> insert into @suffixTable ( id , suffix ) values ( 3 , 'third' )
>
> select
>
> bt.nameof + st.suffix as TheValue
>
> from @baseTable bt c
Thanks for the code example. I think I see where you are going with
this. It errors on the "c" though at the end. Tried doing "from
@baseTable bt @suffixTable st" but didn't like it.
JR
JR
>
> declare @baseTable table ( id int , nameof varchar(32) )
>
> insert into @baseTable ( id , nameof ) values ( 1 , 'Apple' )
>
> insert into @baseTable ( id , nameof ) values ( 2 , 'Banana')
>
> insert into @baseTable ( id , nameof ) values ( 3 , 'Cantaloupe' )
>
> declare @suffixTable table ( id int , suffix varchar(32) )
>
> insert into @suffixTable ( id , suffix ) values ( 2 , 'second')
>
> insert into @suffixTable ( id , suffix ) values ( 3 , 'third' )
>
> select
>
> bt.nameof + st.suffix as TheValue
>
> from @baseTable bt c
Your script seemed to return:
Applesecond
Applethird
Bananasecond
Bananathird
Cantaloupesecond
Cantaloupethird
I would expect from what I'm looking to do to return:
Bananasecond
Cantaloupethird
as those match the id values. Think I got it though if I go:
select basetable.nameof + suffixtable.suffix as TheValue
from baseTable,suffixTable where basetable.id = suffixtable.id
Tom Cooper
select.news:d166d0f3-e753-4004...@e1g2000hsh.googlegroups.com...
sloan
"Cross Join"
and then
"Inner Join"
to see the difference.
"JR" <jri...@yahoo.com> wrote in message
news:d85336d4-8ea2-48d9...@i29g2000prf.googlegroups.com...