Is it possible to get and use an insert id?

[Angie Winterbottom]

I'm building something where there's a login system, and I've just
added roles. Roles simply have an id and a name, it's many to many so
I made a joining table called RoleUsers to hold that info.

So I want a migration which :

a) Adds the new db tables
b) Adds the new roles
c) sets all pre-existing users to one of the roles

I've managed the first two bits, but I don't know if the third is
possible.

I was thinking I would either do it by:

i) finding out the insert id on one of the role inserts (I am not sure
whether fluentmigrator provides access to this data)

or

ii) some clever sql that updates by a select, but I can't see how to
do that without a join, but I don't know what to join on because I
only just added the table.

[Sean Chambers]

What I usually need to do with something like this is use
execute.script and make a .sql script where I can select the role ids
by name into variables and then insert them in the right combinations.

I can provide a simple example if you need it.

Sean

Sent from my Windows Phone From: Angie Winterbottom
Sent: Wednesday, September 07, 2011 9:55 AM
To: FluentMigrator Google Group
Subject: Is it possible to get and use an insert id?

or

--
You received this message because you are subscribed to the Google
Groups "FluentMigrator Google Group" group.
To post to this group, send email to
fluentmigrato...@googlegroups.com.
To unsubscribe from this group, send email to
fluentmigrator-goog...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/fluentmigrator-google-group?hl=en.

[Andrew Busby]

Hi Angie,

What we tend to do in this situation, is to explicitly set the primary keys
so that we know what they are before the import.

Then for databases that have a issues with you inserting primary keys into
identity fields, we place an (sql server example)

Execute.Sql("SET IDENTITY_INSERT [TableName] ON");

//Insert rows here

Execute.Sql("SET IDENTITY_INSERT [TableName] OFF");

If you combine this with an IFDatabaseType call then you handle all of your
database.

Just in case this might be helpful.

Andy

-----Original Message-----
From: fluentmigrato...@googlegroups.com
[mailto:fluentmigrato...@googlegroups.com] On Behalf Of Angie
Winterbottom
Sent: 07 September 2011 09:49
To: FluentMigrator Google Group
Subject: Is it possible to get and use an insert id?

or

--

[Angie Winterbottom]

Thanks, that worked out just fine!

Now that I think about it, I don't suppose it would be possible to
have those ids when it's all running in a single transaction anyway,
would it?