trying to insert unique instances of information from select

microsoft.public.sqlserver.programming

22 Sep 2005 3:42 PM

jason

i'm performing a massive database transformation, so this sample might
seem completely bizarre, but it is only one small part of a 5000 line
operation to perform transformations. so try not to read this in the
context of "regular" database design, because at this stage the design
is mid-transformation. instead just treat it as an isolated sql puzzle.

first, some sample ddl for the tables (Celko is going to hate this,
it's all uniqueidentifiers):

create table things (
thingid uniqueidentifier primary key not null,
temporarythingnumber int identity (1, 1) not null,
thingownerid uniqueidentifier not null
)

create table thingtransactions (
transactionid uniqueidentifier primary key not null,
temporarythingnumber int,
thingownerid uniqueidentifier not null,
transactiondate datetime not null,
)

now some sample data for the transactions table:

insert into thingtransactions
values (newid(), 1, newid(), getdate())
insert into thingtransactions
values (newid(), 1, newid(), getdate())
insert into thingtransactions
values (newid(), 1, newid(), getdate())
insert into thingtransactions
values (newid(), 2, newid(), getdate())
insert into thingtransactions
values (newid(), 2, newid(), getdate())
insert into thingtransactions
values (newid(), 3, newid(), getdate())
vinsert into thingtransactions
values (newid(), 3, newid(), getdate())

now for my goal: keep in mind that the thing table is empty. i'm trying
to insert new things into the things table, based on information in the
thingtransaction table. what i have right now is this:

insert into things
select newid(), adid, userid from thingtransactions

however, as you might guess, this statement violates the primary key
constraint on the things table. how do i just get values for ONE of the
transactions (i don't particularly care which, but MAX(transactiondate)
would be ideal)

thanks for any help,

jason

22 Sep 2005 4:22 PM

jason

nevermind, i was able to get this working by using some other
thingtransaction columns to isolate a single match.

22 Sep 2005 5:03 PM

Jeremy Williams

Actually, he is going to hate it even more, since you do not have any unique
constraints on any combination of columns *other* than the uniqueidentifier
columns - so you have no means of preventing duplicate data from being
entered.

Also, your final INSERT statement has more problems than just the key
violation - where are the "adid" and "userid" columns? They are not in the
schema information you provided.

Finally, I think what you are trying to do is get unique combinations of
"temporarythingnumber" and "thingownerid" into your "things" table, but
since you have the "temporarythingnumber" column set with an Identity
property, you will not be able to insert the corresponding values from the
"thingtransactions" table (unless you use the SET IDENTITY_INSERT statement
first).

So, how about this:
INSERT INTO things
SELECT DISTINCT thingownerid FROM thingtransactions

Now, this statement does not actually correspond to your sample data, since
your sample data always uses unique thingownerid values (you always get a
new one, you never re-use a value already generated). But as a complete
guess, I *think* this is along the lines you were striving for.

Show quote

"jason" <iae***@yahoo.com> wrote in message
news:1127403728.104290.175580@g49g2000cwa.googlegroups.com...
> i'm performing a massive database transformation, so this sample might
> seem completely bizarre, but it is only one small part of a 5000 line
> operation to perform transformations. so try not to read this in the
> context of "regular" database design, because at this stage the design
> is mid-transformation. instead just treat it as an isolated sql puzzle.
>
> first, some sample ddl for the tables (Celko is going to hate this,
> it's all uniqueidentifiers):
>
> create table things (
> thingid uniqueidentifier primary key not null,
> temporarythingnumber int identity (1, 1) not null,
> thingownerid uniqueidentifier not null
> )
>
> create table thingtransactions (
> transactionid uniqueidentifier primary key not null,
> temporarythingnumber int,
> thingownerid uniqueidentifier not null,
> transactiondate datetime not null,
> )
>
> now some sample data for the transactions table:
>
> insert into thingtransactions
> values (newid(), 1, newid(), getdate())
> insert into thingtransactions
> values (newid(), 1, newid(), getdate())
> insert into thingtransactions
> values (newid(), 1, newid(), getdate())
> insert into thingtransactions
> values (newid(), 2, newid(), getdate())
> insert into thingtransactions
> values (newid(), 2, newid(), getdate())
> insert into thingtransactions
> values (newid(), 3, newid(), getdate())
> vinsert into thingtransactions
> values (newid(), 3, newid(), getdate())
>
> now for my goal: keep in mind that the thing table is empty. i'm trying
> to insert new things into the things table, based on information in the
> thingtransaction table. what i have right now is this:
>
> insert into things
> select newid(), adid, userid from thingtransactions
>
> however, as you might guess, this statement violates the primary key
> constraint on the things table. how do i just get values for ONE of the
> transactions (i don't particularly care which, but MAX(transactiondate)
> would be ideal)
>
> thanks for any help,
>
> jason
>

22 Sep 2005 6:38 PM

jason

there are plenty of alternate keys on the actual end-state tables. as
mentioned in the post, this is a tiny part of a transformation script
repairing a highly denormalized schema.

one problem with the insert statement is i want one "thing" entry per
"thingtransaction" not per "thingtransaction" owner. i did eventually
get a working solution. thanks for your input though.

jason