Clone a record

Hello --
I want to create a new record in the "sales_quotes" table so the salesperson
can explore alternate quote(s) to present to a customer.
The new record will have these characteristics:
- identical values for all fields with the exception of the primary key
field
- the primary key value of the new record will have a user-entered value,
validated to assure it is unique
Can someone suggest how to do this ...
- updatable query
- create record in a temp table with identical structure, modify the primary
key field, then append to quotes table
- other
Thanks for any help.
Larry Mehl
L Mehl wrote:

>Hello --
>I want to create a new record in the "sales_quotes" table so the salesperso
n
>can explore alternate quote(s) to present to a customer.
>The new record will have these characteristics:
>- identical values for all fields with the exception of the primary key
>field
>
Identical for all fields of what row? I don't see the point of
inserting a new sales quote that is identical to an existing quote
except for the primary key (which appears to be entirely artificial).
But if you have some real need to do this, it would look something like
insert into sales_quotes
select
@.new_primary_key_value,
column_A,
column_B,
column_C,
.
from sales_quotes
where primary_key_column = @.old_primary_key_value
It makes more sense to me that you would want to add a new
row to a quotes table when there is a new, different quote, not
when there is no new quote.
Steve Kass
Drew University

>- the primary key value of the new record will have a user-entered value,
>validated to assure it is unique
>Can someone suggest how to do this ...
>- updatable query
>- create record in a temp table with identical structure, modify the primar
y
>key field, then append to quotes table
>- other
>Thanks for any help.
>Larry Mehl
>
>|||No reason to copy a row in your table. I guess what you mean is that you
want to present that data to the salesperson so that she can review it,
modify some data and then save a *modified* version of the row - not just
modify the key, which would serve no obvious purpose. Do this the same way
the enter any new row - the "cloned" values just become the defaults in the
user's entry form. This is client-side functionality.
David Portas
SQL Server MVP
--|||If [sales_quotes] is a table seperate from sales, then just do a select into
[sales_quotes] from [sales] and then let the salesperson can edit the new
record using a form. I don't see why the primary key should be user entered;
instead let them enter a descriptive text or key from cutomer and date
entered.
"L Mehl" <mehl_nospam@.cyvest.com> wrote in message
news:LGWMd.556$mG6.504@.newsread1.news.pas.earthlink.net...
> Hello --
> I want to create a new record in the "sales_quotes" table so the
> salesperson
> can explore alternate quote(s) to present to a customer.
> The new record will have these characteristics:
> - identical values for all fields with the exception of the primary key
> field
> - the primary key value of the new record will have a user-entered value,
> validated to assure it is unique
> Can someone suggest how to do this ...
> - updatable query
> - create record in a temp table with identical structure, modify the
> primary
> key field, then append to quotes table
> - other
> Thanks for any help.
> Larry Mehl
>|||Steve --
Thanks. Sorry for the brief description of the need for this process.
The reason for the cloned record is so that the sales person's manager can
perform sensitivity analysis on the quote, with the possible result that it
becomes the quote that is submitted to the customer, and the "original"
quote is discarded.
I was aware of the method you show. I was looking for a way to avoid having
to enter all the non-primary-key values.
Larry
"Steve Kass" <skass@.drew.edu> wrote in message
news:#cCT9q0CFHA.3728@.TK2MSFTNGP14.phx.gbl...
>
> L Mehl wrote:
>
salesperson
> Identical for all fields of what row? I don't see the point of
> inserting a new sales quote that is identical to an existing quote
> except for the primary key (which appears to be entirely artificial).
> But if you have some real need to do this, it would look something like
> insert into sales_quotes
> select
> @.new_primary_key_value,
> column_A,
> column_B,
> column_C,
> ..
> from sales_quotes
> where primary_key_column = @.old_primary_key_value
> It makes more sense to me that you would want to add a new
> row to a quotes table when there is a new, different quote, not
> when there is no new quote.
> Steve Kass
> Drew University
>
primary|||All --
Thank you for the suggestions.
Larry Mehl
"L Mehl" <mehl_nospam@.cyvest.com> wrote in message
news:LGWMd.556$mG6.504@.newsread1.news.pas.earthlink.net...
> Hello --
> I want to create a new record in the "sales_quotes" table so the
salesperson
> can explore alternate quote(s) to present to a customer.
> The new record will have these characteristics:
> - identical values for all fields with the exception of the primary key
> field
> - the primary key value of the new record will have a user-entered value,
> validated to assure it is unique
> Can someone suggest how to do this ...
> - updatable query
> - create record in a temp table with identical structure, modify the
primary
> key field, then append to quotes table
> - other
> Thanks for any help.
> Larry Mehl
>|||Steve --
I am a newbie concerning SQL queries in VB connected to a SQL Server
database.
In an ACCESS/VBA application I used SQL as in your example "insert into
sales_quotes ..." below, in a command
CurrentDb.Execute "insert into sales_quotes ...", dbFailOnError
Can you tell me how to do the same thing in VB connected to a SQL Server
database, or suggest some articles or other resoulced from which I can learn
how?
I can connect to the db and populate a datagrid using ADO. Is your example
"compatible" with ADO, or is it another technique entirely?
I will eventually convert my app to dotNET, so I would appreciate knowing if
your method will work well on that platform.
Thank you for any more help.
Larry
"Steve Kass" <skass@.drew.edu> wrote in message
news:#cCT9q0CFHA.3728@.TK2MSFTNGP14.phx.gbl...
>
> L Mehl wrote:
>
salesperson
> Identical for all fields of what row? I don't see the point of
> inserting a new sales quote that is identical to an existing quote
> except for the primary key (which appears to be entirely artificial).
> But if you have some real need to do this, it would look something like
> insert into sales_quotes
> select
> @.new_primary_key_value,
> column_A,
> column_B,
> column_C,
> ..
> from sales_quotes
> where primary_key_column = @.old_primary_key_value
> It makes more sense to me that you would want to add a new
> row to a quotes table when there is a new, different quote, not
> when there is no new quote.
> Steve Kass
> Drew University
>
primary|||The answer appears to be
connection.Execute
After I understand the third parameter 'Options' I think it will work.
Does anyone have examples or resources for using connection.Execute?
Larry Mehl
"L Mehl" <mehl_nospam@.cyvest.com> wrote in message
news:94CNd.2141$UX3.1163@.newsread3.news.pas.earthlink.net...
> Steve --
> I am a newbie concerning SQL queries in VB connected to a SQL Server
> database.
> In an ACCESS/VBA application I used SQL as in your example "insert into
> sales_quotes ..." below, in a command
> CurrentDb.Execute "insert into sales_quotes ...", dbFailOnError
> Can you tell me how to do the same thing in VB connected to a SQL Server
> database, or suggest some articles or other resoulced from which I can
learn
> how?
> I can connect to the db and populate a datagrid using ADO. Is your
example
> "compatible" with ADO, or is it another technique entirely?
> I will eventually convert my app to dotNET, so I would appreciate knowing
if
> your method will work well on that platform.
> Thank you for any more help.
> Larry
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:#cCT9q0CFHA.3728@.TK2MSFTNGP14.phx.gbl...
> salesperson
value,
> primary
>

Clearer version of my earlier question re sp_grantlogin and sp_grantdbaccess

I have amended this post from the one I posted a few hours ago - to make it
clearer (and friendlier!).
I have developed an app for my customer and it assigns different privilege
levels to users based on which domain network group they belong to.
However the only way I have found to be able to grant them access to the sql
server and
database is to connect with a trusted connection having logged in as Domain
Admin. Is that correct?
(I have to go to another site in order to be allowed to do this which is
somewhat inconvenient).
What I would like to able to do but cannot is log into MSDE as sa and run
the following:
use AppDB
exec sp_grantlogin 'TheirDomain\AppManagers'
exec sp_grantdbaccess 'TheirDomain\AppManagers'
exec sp_grantlogin 'TheirDomain\AppReadWrite'
exec sp_grantdbaccess 'TheirDomain\AppReadWrite'
etc.
Can someone let me know if I could achieve the above with a trusted
connection to MSDE as
something less than Domain Admin? If so what network rights would I need?
Many thanks in advance, for your time and expertise!
Clive Elsworth (London UK)
www.EndorphinSoftware.co.uk
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.631 / Virus Database: 404 - Release Date: 17/03/2004
First question is whether the SQL Server install is a default install or if you can add some login first.
If it is a default install, the only logins which exists are the Windows Administrators group and "sa" (which
requires mixed mode - not recommended). However, in order to install SQL Server, you need to be Administrator
anyhow, so why not just run the script which creates the users after install? Am I missing something?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in message
news:OrihgJpEEHA.3864@.TK2MSFTNGP12.phx.gbl...
> I have amended this post from the one I posted a few hours ago - to make it
> clearer (and friendlier!).
> I have developed an app for my customer and it assigns different privilege
> levels to users based on which domain network group they belong to.
> However the only way I have found to be able to grant them access to the sql
> server and
> database is to connect with a trusted connection having logged in as Domain
> Admin. Is that correct?
> (I have to go to another site in order to be allowed to do this which is
> somewhat inconvenient).
> What I would like to able to do but cannot is log into MSDE as sa and run
> the following:
> use AppDB
> exec sp_grantlogin 'TheirDomain\AppManagers'
> exec sp_grantdbaccess 'TheirDomain\AppManagers'
> exec sp_grantlogin 'TheirDomain\AppReadWrite'
> exec sp_grantdbaccess 'TheirDomain\AppReadWrite'
> etc.
> Can someone let me know if I could achieve the above with a trusted
> connection to MSDE as
> something less than Domain Admin? If so what network rights would I need?
> Many thanks in advance, for your time and expertise!
> Clive Elsworth (London UK)
>
> --
> www.EndorphinSoftware.co.uk
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.631 / Virus Database: 404 - Release Date: 17/03/2004
>
|||Tibor
Hello again - you and I were in contact about 4 years ago or so - I don't
think I've been to Sweden since then unfortunately. It may have been my SQL
Beautifier that we were discussing then, which after giving away a fair bit
I have not developed since. In any case you were a great help with a trick
to restart SQL Agent when SQL Server restarts (using sp_procoption) which I
have since given away to countless students in classes I have taught.
Anyhow..
I did the install about a month ago as Domain Admin (default install - mixed
mode - not recommended I know, but I really need it) and ran the
sp_grantlogin and sp_grantdbaccess fine, because I was then at the 'Admin'
site where they let me login as Domain Admin. Since then, they wanted a
minor enhancement which meant altering a number of Tables, SPs and Views.
What I usually like to do in this situation is:
1. - Backup the customer's DB and restore it to my laptop
2. - Transfer the customer's data to the newly enhanced DB on my laptop,
which I have tested with the app back in my office
3. - Transfer and Restore that DB to the customer server PC
4. - Grant rights to network groups to the DB now on the customer server PC
In the end I had to do it the other way which was to:
1. - Backup the customer's DB and restore it to my laptop
2. - Apply all the changes to their DB and hope I hadn't missed anything.
3. - Restore it back to their server - the domain groups still existed as DB
users and so no further action was necessary.
Maybe I'll just have to invest in one of those utilities that tells you all
the differences between two DBs - so I can be sure I never miss anything -
but I'd rather not if possible.
It doesn't seem right that sa - that should have total control over a SQL
Server, doesn't have the right to grant DB access to Domain Groups. What do
you think?
Best Regards
Clive
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exVYrQpEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> First question is whether the SQL Server install is a default install or
if you can add some login first.
> If it is a default install, the only logins which exists are the Windows
Administrators group and "sa" (which
> requires mixed mode - not recommended). However, in order to install SQL
Server, you need to be Administrator
> anyhow, so why not just run the script which creates the users after
install? Am I missing something?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in
message
> news:OrihgJpEEHA.3864@.TK2MSFTNGP12.phx.gbl...
it
privilege
sql
Domain
run
need?
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.631 / Virus Database: 404 - Release Date: 18/03/2004
|||> Hello again - you and I were in contact about 4 years ago or so <snip>
Ahh, yes. Thanks for reminding me, Clive. :-)

> Maybe I'll just have to invest in one of those utilities that tells you all
> the differences between two DBs - so I can be sure I never miss anything -
> but I'd rather not if possible.
I take it that you have already determined that it is too labor intensive to continuously add onto a script
file while you do changes, so the script file in the end contains the necessary ALTER TABLE commands etc? This
is perfectly doable, but only you can determine whether you consider this hinders your development too much to
be worth it.

> It doesn't seem right that sa - that should have total control over a SQL
> Server, doesn't have the right to grant DB access to Domain Groups. What do
> you think?
Seems I missed this in the beginning of the thread. I thought that your problem is that you don't are
connected as sysadmin. I realize now that you are connected as sa. I didn't know that an SQL Server login as
sa doesn't give you the ability to add Windows Logins from a domain as database users to a database, and if
you read the documentation for sp_grantdbaccess, it only say that you need to be sysadmin (etc). If this is
what you are seeing, then you might want to post a bug report (or rather open a case which might end up in a
bug report being filed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in message
news:%23Zd5q5vEEHA.1376@.TK2MSFTNGP10.phx.gbl...
> Tibor
> Hello again - you and I were in contact about 4 years ago or so - I don't
> think I've been to Sweden since then unfortunately. It may have been my SQL
> Beautifier that we were discussing then, which after giving away a fair bit
> I have not developed since. In any case you were a great help with a trick
> to restart SQL Agent when SQL Server restarts (using sp_procoption) which I
> have since given away to countless students in classes I have taught.
> Anyhow..
> I did the install about a month ago as Domain Admin (default install - mixed
> mode - not recommended I know, but I really need it) and ran the
> sp_grantlogin and sp_grantdbaccess fine, because I was then at the 'Admin'
> site where they let me login as Domain Admin. Since then, they wanted a
> minor enhancement which meant altering a number of Tables, SPs and Views.
> What I usually like to do in this situation is:
> 1. - Backup the customer's DB and restore it to my laptop
> 2. - Transfer the customer's data to the newly enhanced DB on my laptop,
> which I have tested with the app back in my office
> 3. - Transfer and Restore that DB to the customer server PC
> 4. - Grant rights to network groups to the DB now on the customer server PC
> In the end I had to do it the other way which was to:
> 1. - Backup the customer's DB and restore it to my laptop
> 2. - Apply all the changes to their DB and hope I hadn't missed anything.
> 3. - Restore it back to their server - the domain groups still existed as DB
> users and so no further action was necessary.
> Maybe I'll just have to invest in one of those utilities that tells you all
> the differences between two DBs - so I can be sure I never miss anything -
> but I'd rather not if possible.
> It doesn't seem right that sa - that should have total control over a SQL
> Server, doesn't have the right to grant DB access to Domain Groups. What do
> you think?
> Best Regards
> Clive
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:exVYrQpEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> if you can add some login first.
> Administrators group and "sa" (which
> Server, you need to be Administrator
> install? Am I missing something?
> message
> it
> privilege
> sql
> Domain
> run
> need?
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.631 / Virus Database: 404 - Release Date: 18/03/2004
>
|||Tibor
Please see my reply to Greg Low about the sp_grantlogin problem. It's
basically still unresolved - I have more tests to do.

> I take it that you have already determined that it is too labor intensive
to continuously add onto a script
> file while you do changes, so the script file in the end contains the
necessary ALTER TABLE commands etc? This
> is perfectly doable, but only you can determine whether you consider this
hinders your development too much to
> be worth it.
Up to now I have got away without needing to do that because I've been
transferring the client's data into my own 'upgraded and fully tested' db,
before restoring it to their computers.
I was shocked the other day to learn that that was no longer going to be
possible in all cases, although luckily in that case I had kept a note of
all the changes I had made - and so made them all again manually to their
version of the db (briefly restored onto my laptop), which had security all
set up, and so could be restored back to their network without the need for
any additional sp_grantlogins.
Thanks for your help. Hope business is good for you.
Regards
Clive
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eFRGgzZFEHA.2408@.TK2MSFTNGP10.phx.gbl...
> Ahh, yes. Thanks for reminding me, Clive. :-)
>
all
anything -
> I take it that you have already determined that it is too labor intensive
to continuously add onto a script
> file while you do changes, so the script file in the end contains the
necessary ALTER TABLE commands etc? This
> is perfectly doable, but only you can determine whether you consider this
hinders your development too much to
> be worth it.
>
SQL
What do
> Seems I missed this in the beginning of the thread. I thought that your
problem is that you don't are
> connected as sysadmin. I realize now that you are connected as sa. I
didn't know that an SQL Server login as
> sa doesn't give you the ability to add Windows Logins from a domain as
database users to a database, and if
> you read the documentation for sp_grantdbaccess, it only say that you need
to be sysadmin (etc). If this is
> what you are seeing, then you might want to post a bug report (or rather
open a case which might end up in a
> bug report being filed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in
message
> news:%23Zd5q5vEEHA.1376@.TK2MSFTNGP10.phx.gbl...
don't
SQL
bit
trick
which I
mixed
'Admin'
Views.
PC
anything.
as DB
all
anything -
SQL
What do
in
or
Windows
SQL
make
the
which is
and
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.648 / Virus Database: 415 - Release Date: 31/03/2004