Hi,
I need to call sp_OACreate inside an INSERT trigger to create COM component
that's developed in VB6 (STA), my question is if there are many records
inserted at very short period of time, is it multi-threaded (sp_OACreate -
MTA) from trigger point of view? If this is true, then I have to use the MTA
component in trigger.
Thanks!
John
John Lee wrote:
> Hi,
> I need to call sp_OACreate inside an INSERT trigger to create COM component
> that's developed in VB6 (STA), my question is if there are many records
> inserted at very short period of time, is it multi-threaded (sp_OACreate -
> MTA) from trigger point of view? If this is true, then I have to use the MTA
> component in trigger.
> Thanks!
> John
Creating a COM component in a trigger is almost certainly an extremely
bad idea. There is no obvious way that your external code can be made
part of a SQL Server transaction. How will you roll-back the effect of
your VB code if a nested transaction is rolled back? If the external
code is non-transactional in nature then there is little sense in
putting it in a trigger and doing so can only hurt performance and
reliability. Also, in my limited experience the sp_OA procs will not
scale and COM will often leak memory.
Invoke your code from outside SQL Server is my suggestion - from a job
or some other server-side process for example. If you must do it in the
database then use a stored procedure rather than a trigger. In SQL
Server 2005 you also have the option of using a CLR proc.
To answer your original question. Triggers are "multi-threaded" in the
sense that the same trigger can fire simultaneously for several
different connections. Each of those connections will attempt to create
another instance of your COM object.
If multiple rows are updated in a single statement then the trigger
will only fire ONCE. It will NOT fire once for each row. This is
another reason why not to use a trigger to invoke external code. In
order to call your COM component for each row you will probably have to
use a cursor to cycle through each row in the INSERTED virtual table
(INSERTED is available only in triggers and contains the rows that were
inserted or updated). Cursors are best avoided in most circumstances
and especially so in triggers.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1159123193.138245.268530@.b28g2000cwb.googlegr oups.com...
> John Lee wrote:
> Creating a COM component in a trigger is almost certainly an extremely
> bad idea. There is no obvious way that your external code can be made
> part of a SQL Server transaction. How will you roll-back the effect of
> your VB code if a nested transaction is rolled back? If the external
> code is non-transactional in nature then there is little sense in
> putting it in a trigger and doing so can only hurt performance and
> reliability. Also, in my limited experience the sp_OA procs will not
> scale and COM will often leak memory.
> Invoke your code from outside SQL Server is my suggestion - from a job
> or some other server-side process for example. If you must do it in the
> database then use a stored procedure rather than a trigger. In SQL
> Server 2005 you also have the option of using a CLR proc.
> To answer your original question. Triggers are "multi-threaded" in the
> sense that the same trigger can fire simultaneously for several
> different connections. Each of those connections will attempt to create
> another instance of your COM object.
> If multiple rows are updated in a single statement then the trigger
> will only fire ONCE. It will NOT fire once for each row. This is
> another reason why not to use a trigger to invoke external code. In
> order to call your COM component for each row you will probably have to
> use a cursor to cycle through each row in the INSERTED virtual table
> (INSERTED is available only in triggers and contains the rows that were
> inserted or updated). Cursors are best avoided in most circumstances
> and especially so in triggers.
>
Ditto all of that. Just adding in COM programming an STA component can be
use by a multi-threaded client. The restriction is that each object
instance can be accessed only by the thread that created it. In a trigger
or procedure the COM component is created (SP_OACreate), used (SP_OAMethod)
and destroyed (SP_OADestroy) all in the same thread. So a plain VB6 STA COM
Component should work fine.
In addition to David's reasons why this is a poor idea, you should generally
avoid doing anything expensive in a trigger. That includes any kind of
network communication or file IO, regardless of whether the code is VB6 COM
or TSQL or CLR code in 2005.
David
|||Hi David
STAs can certainly be launched from MTAs but the bigger problem with VB6 COM
components is that they use TLS which isn't designed to work with SQL
Server's UMS. Under load, this can lead to various possible undesirable
conditions such as inconsistent results, crashes & hung UMS schedulers (at
least in SQL2K).
If the original poster really HAS to create a component under a trigger, my
advice is to use sp_OACreate only if the trigger activity is low (as
developing in VB does have its advantages in terms of productivity). If the
trigger activity is high (meaning it has a high probability of being heavily
pre-empted & re-scheduled), consider either a different architecture
(asynch, MQ etc) or write an extended stored proc, perhaps with delphi or c.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1159123193.138245.268530@.b28g2000cwb.googlegr oups.com...
> Ditto all of that. Just adding in COM programming an STA component can be
> use by a multi-threaded client. The restriction is that each object
> instance can be accessed only by the thread that created it. In a trigger
> or procedure the COM component is created (SP_OACreate), used
> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
> plain VB6 STA COM Component should work fine.
> In addition to David's reasons why this is a poor idea, you should
> generally avoid doing anything expensive in a trigger. That includes any
> kind of network communication or file IO, regardless of whether the code
> is VB6 COM or TSQL or CLR code in 2005.
> David
|||Thanks very much for your reply - David P, David B and Greg!!!
I totally agree with you all regarding the bad things using triggers doing
heavy lifting job ... BUT here is the business problem I have to solve:
We need to sync some data from SQL server 2000 to CRM in near realtime (< 5
minutes)
so we added triggers to around 10 tables, inside those triggers, we only
write 4 pieces of info to ChangeLog table
1. option 1 - write a service to pull ChangeLog table every 2 minutes
2. option 2 - create a trigger on this table to write out a text file to
notify the service to do the sync work
I also developed an extended stored proc to write file out and this seems
twice as fast as the sp_OACreate.
sp_OACreate has two issues to me:
1. late binding - because we are using progID to create the object
2. possible MTA calling into STA issue - if triggers side are multiple
threaded, then calling into STA will make it as serialized operation.
Thanks!
John
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi David
> STAs can certainly be launched from MTAs but the bigger problem with VB6
> COM components is that they use TLS which isn't designed to work with SQL
> Server's UMS. Under load, this can lead to various possible undesirable
> conditions such as inconsistent results, crashes & hung UMS schedulers (at
> least in SQL2K).
> If the original poster really HAS to create a component under a trigger,
> my advice is to use sp_OACreate only if the trigger activity is low (as
> developing in VB does have its advantages in terms of productivity). If
> the trigger activity is high (meaning it has a high probability of being
> heavily pre-empted & re-scheduled), consider either a different
> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
> with delphi or c.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>
|||A much safer and more efficient way to do this would be to have your trigger
write to staging tables and have an external process read those tables once
a minute or so. In SQL Server 2005 you could use the Service Broker to do
this in a scaleable and asynchronous way.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Lee" <pursca@.newsgroups.nospam> wrote in message
news:O$27%237M4GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Thanks very much for your reply - David P, David B and Greg!!!
> I totally agree with you all regarding the bad things using triggers doing
> heavy lifting job ... BUT here is the business problem I have to solve:
> We need to sync some data from SQL server 2000 to CRM in near realtime (<
> 5 minutes)
> so we added triggers to around 10 tables, inside those triggers, we only
> write 4 pieces of info to ChangeLog table
> 1. option 1 - write a service to pull ChangeLog table every 2 minutes
> 2. option 2 - create a trigger on this table to write out a text file to
> notify the service to do the sync work
> I also developed an extended stored proc to write file out and this seems
> twice as fast as the sp_OACreate.
> sp_OACreate has two issues to me:
> 1. late binding - because we are using progID to create the object
> 2. possible MTA calling into STA issue - if triggers side are multiple
> threaded, then calling into STA will make it as serialized operation.
> Thanks!
> John
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
>
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Friday, March 30, 2012
Is triggers multi-threaded?
Hi,
I need to call sp_OACreate inside an INSERT trigger to create COM component
that's developed in VB6 (STA), my question is if there are many records
inserted at very short period of time, is it multi-threaded (sp_OACreate -
MTA) from trigger point of view? If this is true, then I have to use the MTA
component in trigger.
Thanks!
JohnJohn Lee wrote:
> Hi,
> I need to call sp_OACreate inside an INSERT trigger to create COM component
> that's developed in VB6 (STA), my question is if there are many records
> inserted at very short period of time, is it multi-threaded (sp_OACreate -
> MTA) from trigger point of view? If this is true, then I have to use the MTA
> component in trigger.
> Thanks!
> John
Creating a COM component in a trigger is almost certainly an extremely
bad idea. There is no obvious way that your external code can be made
part of a SQL Server transaction. How will you roll-back the effect of
your VB code if a nested transaction is rolled back? If the external
code is non-transactional in nature then there is little sense in
putting it in a trigger and doing so can only hurt performance and
reliability. Also, in my limited experience the sp_OA procs will not
scale and COM will often leak memory.
Invoke your code from outside SQL Server is my suggestion - from a job
or some other server-side process for example. If you must do it in the
database then use a stored procedure rather than a trigger. In SQL
Server 2005 you also have the option of using a CLR proc.
To answer your original question. Triggers are "multi-threaded" in the
sense that the same trigger can fire simultaneously for several
different connections. Each of those connections will attempt to create
another instance of your COM object.
If multiple rows are updated in a single statement then the trigger
will only fire ONCE. It will NOT fire once for each row. This is
another reason why not to use a trigger to invoke external code. In
order to call your COM component for each row you will probably have to
use a cursor to cycle through each row in the INSERTED virtual table
(INSERTED is available only in triggers and contains the rows that were
inserted or updated). Cursors are best avoided in most circumstances
and especially so in triggers.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use the
>> MTA
>> component in trigger.
>> Thanks!
>> John
> Creating a COM component in a trigger is almost certainly an extremely
> bad idea. There is no obvious way that your external code can be made
> part of a SQL Server transaction. How will you roll-back the effect of
> your VB code if a nested transaction is rolled back? If the external
> code is non-transactional in nature then there is little sense in
> putting it in a trigger and doing so can only hurt performance and
> reliability. Also, in my limited experience the sp_OA procs will not
> scale and COM will often leak memory.
> Invoke your code from outside SQL Server is my suggestion - from a job
> or some other server-side process for example. If you must do it in the
> database then use a stored procedure rather than a trigger. In SQL
> Server 2005 you also have the option of using a CLR proc.
> To answer your original question. Triggers are "multi-threaded" in the
> sense that the same trigger can fire simultaneously for several
> different connections. Each of those connections will attempt to create
> another instance of your COM object.
> If multiple rows are updated in a single statement then the trigger
> will only fire ONCE. It will NOT fire once for each row. This is
> another reason why not to use a trigger to invoke external code. In
> order to call your COM component for each row you will probably have to
> use a cursor to cycle through each row in the INSERTED virtual table
> (INSERTED is available only in triggers and contains the rows that were
> inserted or updated). Cursors are best avoided in most circumstances
> and especially so in triggers.
>
Ditto all of that. Just adding in COM programming an STA component can be
use by a multi-threaded client. The restriction is that each object
instance can be accessed only by the thread that created it. In a trigger
or procedure the COM component is created (SP_OACreate), used (SP_OAMethod)
and destroyed (SP_OADestroy) all in the same thread. So a plain VB6 STA COM
Component should work fine.
In addition to David's reasons why this is a poor idea, you should generally
avoid doing anything expensive in a trigger. That includes any kind of
network communication or file IO, regardless of whether the code is VB6 COM
or TSQL or CLR code in 2005.
David|||Hi David
STAs can certainly be launched from MTAs but the bigger problem with VB6 COM
components is that they use TLS which isn't designed to work with SQL
Server's UMS. Under load, this can lead to various possible undesirable
conditions such as inconsistent results, crashes & hung UMS schedulers (at
least in SQL2K).
If the original poster really HAS to create a component under a trigger, my
advice is to use sp_OACreate only if the trigger activity is low (as
developing in VB does have its advantages in terms of productivity). If the
trigger activity is high (meaning it has a high probability of being heavily
pre-empted & re-scheduled), consider either a different architecture
(asynch, MQ etc) or write an extended stored proc, perhaps with delphi or c.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use the
>> MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
> Ditto all of that. Just adding in COM programming an STA component can be
> use by a multi-threaded client. The restriction is that each object
> instance can be accessed only by the thread that created it. In a trigger
> or procedure the COM component is created (SP_OACreate), used
> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
> plain VB6 STA COM Component should work fine.
> In addition to David's reasons why this is a poor idea, you should
> generally avoid doing anything expensive in a trigger. That includes any
> kind of network communication or file IO, regardless of whether the code
> is VB6 COM or TSQL or CLR code in 2005.
> David|||Thanks very much for your reply - David P, David B and Greg!!!
I totally agree with you all regarding the bad things using triggers doing
heavy lifting job ... BUT here is the business problem I have to solve:
We need to sync some data from SQL server 2000 to CRM in near realtime (< 5
minutes)
so we added triggers to around 10 tables, inside those triggers, we only
write 4 pieces of info to ChangeLog table
1. option 1 - write a service to pull ChangeLog table every 2 minutes
2. option 2 - create a trigger on this table to write out a text file to
notify the service to do the sync work
I also developed an extended stored proc to write file out and this seems
twice as fast as the sp_OACreate.
sp_OACreate has two issues to me:
1. late binding - because we are using progID to create the object
2. possible MTA calling into STA issue - if triggers side are multiple
threaded, then calling into STA will make it as serialized operation.
Thanks!
John
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi David
> STAs can certainly be launched from MTAs but the bigger problem with VB6
> COM components is that they use TLS which isn't designed to work with SQL
> Server's UMS. Under load, this can lead to various possible undesirable
> conditions such as inconsistent results, crashes & hung UMS schedulers (at
> least in SQL2K).
> If the original poster really HAS to create a component under a trigger,
> my advice is to use sp_OACreate only if the trigger activity is low (as
> developing in VB does have its advantages in terms of productivity). If
> the trigger activity is high (meaning it has a high probability of being
> heavily pre-empted & re-scheduled), consider either a different
> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
> with delphi or c.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>>
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use
>> the MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
>>
>> Ditto all of that. Just adding in COM programming an STA component can
>> be use by a multi-threaded client. The restriction is that each object
>> instance can be accessed only by the thread that created it. In a
>> trigger or procedure the COM component is created (SP_OACreate), used
>> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
>> plain VB6 STA COM Component should work fine.
>> In addition to David's reasons why this is a poor idea, you should
>> generally avoid doing anything expensive in a trigger. That includes any
>> kind of network communication or file IO, regardless of whether the code
>> is VB6 COM or TSQL or CLR code in 2005.
>> David
>|||A much safer and more efficient way to do this would be to have your trigger
write to staging tables and have an external process read those tables once
a minute or so. In SQL Server 2005 you could use the Service Broker to do
this in a scaleable and asynchronous way.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Lee" <pursca@.newsgroups.nospam> wrote in message
news:O$27%237M4GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Thanks very much for your reply - David P, David B and Greg!!!
> I totally agree with you all regarding the bad things using triggers doing
> heavy lifting job ... BUT here is the business problem I have to solve:
> We need to sync some data from SQL server 2000 to CRM in near realtime (<
> 5 minutes)
> so we added triggers to around 10 tables, inside those triggers, we only
> write 4 pieces of info to ChangeLog table
> 1. option 1 - write a service to pull ChangeLog table every 2 minutes
> 2. option 2 - create a trigger on this table to write out a text file to
> notify the service to do the sync work
> I also developed an extended stored proc to write file out and this seems
> twice as fast as the sp_OACreate.
> sp_OACreate has two issues to me:
> 1. late binding - because we are using progID to create the object
> 2. possible MTA calling into STA issue - if triggers side are multiple
> threaded, then calling into STA will make it as serialized operation.
> Thanks!
> John
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Hi David
>> STAs can certainly be launched from MTAs but the bigger problem with VB6
>> COM components is that they use TLS which isn't designed to work with SQL
>> Server's UMS. Under load, this can lead to various possible undesirable
>> conditions such as inconsistent results, crashes & hung UMS schedulers
>> (at least in SQL2K).
>> If the original poster really HAS to create a component under a trigger,
>> my advice is to use sp_OACreate only if the trigger activity is low (as
>> developing in VB does have its advantages in terms of productivity). If
>> the trigger activity is high (meaning it has a high probability of being
>> heavily pre-empted & re-scheduled), consider either a different
>> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
>> with delphi or c.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>>
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many
>> records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use
>> the MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
>>
>> Ditto all of that. Just adding in COM programming an STA component can
>> be use by a multi-threaded client. The restriction is that each object
>> instance can be accessed only by the thread that created it. In a
>> trigger or procedure the COM component is created (SP_OACreate), used
>> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
>> plain VB6 STA COM Component should work fine.
>> In addition to David's reasons why this is a poor idea, you should
>> generally avoid doing anything expensive in a trigger. That includes
>> any kind of network communication or file IO, regardless of whether the
>> code is VB6 COM or TSQL or CLR code in 2005.
>> David
>>
>sql
I need to call sp_OACreate inside an INSERT trigger to create COM component
that's developed in VB6 (STA), my question is if there are many records
inserted at very short period of time, is it multi-threaded (sp_OACreate -
MTA) from trigger point of view? If this is true, then I have to use the MTA
component in trigger.
Thanks!
JohnJohn Lee wrote:
> Hi,
> I need to call sp_OACreate inside an INSERT trigger to create COM component
> that's developed in VB6 (STA), my question is if there are many records
> inserted at very short period of time, is it multi-threaded (sp_OACreate -
> MTA) from trigger point of view? If this is true, then I have to use the MTA
> component in trigger.
> Thanks!
> John
Creating a COM component in a trigger is almost certainly an extremely
bad idea. There is no obvious way that your external code can be made
part of a SQL Server transaction. How will you roll-back the effect of
your VB code if a nested transaction is rolled back? If the external
code is non-transactional in nature then there is little sense in
putting it in a trigger and doing so can only hurt performance and
reliability. Also, in my limited experience the sp_OA procs will not
scale and COM will often leak memory.
Invoke your code from outside SQL Server is my suggestion - from a job
or some other server-side process for example. If you must do it in the
database then use a stored procedure rather than a trigger. In SQL
Server 2005 you also have the option of using a CLR proc.
To answer your original question. Triggers are "multi-threaded" in the
sense that the same trigger can fire simultaneously for several
different connections. Each of those connections will attempt to create
another instance of your COM object.
If multiple rows are updated in a single statement then the trigger
will only fire ONCE. It will NOT fire once for each row. This is
another reason why not to use a trigger to invoke external code. In
order to call your COM component for each row you will probably have to
use a cursor to cycle through each row in the INSERTED virtual table
(INSERTED is available only in triggers and contains the rows that were
inserted or updated). Cursors are best avoided in most circumstances
and especially so in triggers.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use the
>> MTA
>> component in trigger.
>> Thanks!
>> John
> Creating a COM component in a trigger is almost certainly an extremely
> bad idea. There is no obvious way that your external code can be made
> part of a SQL Server transaction. How will you roll-back the effect of
> your VB code if a nested transaction is rolled back? If the external
> code is non-transactional in nature then there is little sense in
> putting it in a trigger and doing so can only hurt performance and
> reliability. Also, in my limited experience the sp_OA procs will not
> scale and COM will often leak memory.
> Invoke your code from outside SQL Server is my suggestion - from a job
> or some other server-side process for example. If you must do it in the
> database then use a stored procedure rather than a trigger. In SQL
> Server 2005 you also have the option of using a CLR proc.
> To answer your original question. Triggers are "multi-threaded" in the
> sense that the same trigger can fire simultaneously for several
> different connections. Each of those connections will attempt to create
> another instance of your COM object.
> If multiple rows are updated in a single statement then the trigger
> will only fire ONCE. It will NOT fire once for each row. This is
> another reason why not to use a trigger to invoke external code. In
> order to call your COM component for each row you will probably have to
> use a cursor to cycle through each row in the INSERTED virtual table
> (INSERTED is available only in triggers and contains the rows that were
> inserted or updated). Cursors are best avoided in most circumstances
> and especially so in triggers.
>
Ditto all of that. Just adding in COM programming an STA component can be
use by a multi-threaded client. The restriction is that each object
instance can be accessed only by the thread that created it. In a trigger
or procedure the COM component is created (SP_OACreate), used (SP_OAMethod)
and destroyed (SP_OADestroy) all in the same thread. So a plain VB6 STA COM
Component should work fine.
In addition to David's reasons why this is a poor idea, you should generally
avoid doing anything expensive in a trigger. That includes any kind of
network communication or file IO, regardless of whether the code is VB6 COM
or TSQL or CLR code in 2005.
David|||Hi David
STAs can certainly be launched from MTAs but the bigger problem with VB6 COM
components is that they use TLS which isn't designed to work with SQL
Server's UMS. Under load, this can lead to various possible undesirable
conditions such as inconsistent results, crashes & hung UMS schedulers (at
least in SQL2K).
If the original poster really HAS to create a component under a trigger, my
advice is to use sp_OACreate only if the trigger activity is low (as
developing in VB does have its advantages in terms of productivity). If the
trigger activity is high (meaning it has a high probability of being heavily
pre-empted & re-scheduled), consider either a different architecture
(asynch, MQ etc) or write an extended stored proc, perhaps with delphi or c.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use the
>> MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
> Ditto all of that. Just adding in COM programming an STA component can be
> use by a multi-threaded client. The restriction is that each object
> instance can be accessed only by the thread that created it. In a trigger
> or procedure the COM component is created (SP_OACreate), used
> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
> plain VB6 STA COM Component should work fine.
> In addition to David's reasons why this is a poor idea, you should
> generally avoid doing anything expensive in a trigger. That includes any
> kind of network communication or file IO, regardless of whether the code
> is VB6 COM or TSQL or CLR code in 2005.
> David|||Thanks very much for your reply - David P, David B and Greg!!!
I totally agree with you all regarding the bad things using triggers doing
heavy lifting job ... BUT here is the business problem I have to solve:
We need to sync some data from SQL server 2000 to CRM in near realtime (< 5
minutes)
so we added triggers to around 10 tables, inside those triggers, we only
write 4 pieces of info to ChangeLog table
1. option 1 - write a service to pull ChangeLog table every 2 minutes
2. option 2 - create a trigger on this table to write out a text file to
notify the service to do the sync work
I also developed an extended stored proc to write file out and this seems
twice as fast as the sp_OACreate.
sp_OACreate has two issues to me:
1. late binding - because we are using progID to create the object
2. possible MTA calling into STA issue - if triggers side are multiple
threaded, then calling into STA will make it as serialized operation.
Thanks!
John
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi David
> STAs can certainly be launched from MTAs but the bigger problem with VB6
> COM components is that they use TLS which isn't designed to work with SQL
> Server's UMS. Under load, this can lead to various possible undesirable
> conditions such as inconsistent results, crashes & hung UMS schedulers (at
> least in SQL2K).
> If the original poster really HAS to create a component under a trigger,
> my advice is to use sp_OACreate only if the trigger activity is low (as
> developing in VB does have its advantages in terms of productivity). If
> the trigger activity is high (meaning it has a high probability of being
> heavily pre-empted & re-scheduled), consider either a different
> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
> with delphi or c.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>>
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use
>> the MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
>>
>> Ditto all of that. Just adding in COM programming an STA component can
>> be use by a multi-threaded client. The restriction is that each object
>> instance can be accessed only by the thread that created it. In a
>> trigger or procedure the COM component is created (SP_OACreate), used
>> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
>> plain VB6 STA COM Component should work fine.
>> In addition to David's reasons why this is a poor idea, you should
>> generally avoid doing anything expensive in a trigger. That includes any
>> kind of network communication or file IO, regardless of whether the code
>> is VB6 COM or TSQL or CLR code in 2005.
>> David
>|||A much safer and more efficient way to do this would be to have your trigger
write to staging tables and have an external process read those tables once
a minute or so. In SQL Server 2005 you could use the Service Broker to do
this in a scaleable and asynchronous way.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Lee" <pursca@.newsgroups.nospam> wrote in message
news:O$27%237M4GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Thanks very much for your reply - David P, David B and Greg!!!
> I totally agree with you all regarding the bad things using triggers doing
> heavy lifting job ... BUT here is the business problem I have to solve:
> We need to sync some data from SQL server 2000 to CRM in near realtime (<
> 5 minutes)
> so we added triggers to around 10 tables, inside those triggers, we only
> write 4 pieces of info to ChangeLog table
> 1. option 1 - write a service to pull ChangeLog table every 2 minutes
> 2. option 2 - create a trigger on this table to write out a text file to
> notify the service to do the sync work
> I also developed an extended stored proc to write file out and this seems
> twice as fast as the sp_OACreate.
> sp_OACreate has two issues to me:
> 1. late binding - because we are using progID to create the object
> 2. possible MTA calling into STA issue - if triggers side are multiple
> threaded, then calling into STA will make it as serialized operation.
> Thanks!
> John
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Hi David
>> STAs can certainly be launched from MTAs but the bigger problem with VB6
>> COM components is that they use TLS which isn't designed to work with SQL
>> Server's UMS. Under load, this can lead to various possible undesirable
>> conditions such as inconsistent results, crashes & hung UMS schedulers
>> (at least in SQL2K).
>> If the original poster really HAS to create a component under a trigger,
>> my advice is to use sp_OACreate only if the trigger activity is low (as
>> developing in VB does have its advantages in terms of productivity). If
>> the trigger activity is high (meaning it has a high probability of being
>> heavily pre-empted & re-scheduled), consider either a different
>> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
>> with delphi or c.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>>
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many
>> records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use
>> the MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
>>
>> Ditto all of that. Just adding in COM programming an STA component can
>> be use by a multi-threaded client. The restriction is that each object
>> instance can be accessed only by the thread that created it. In a
>> trigger or procedure the COM component is created (SP_OACreate), used
>> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
>> plain VB6 STA COM Component should work fine.
>> In addition to David's reasons why this is a poor idea, you should
>> generally avoid doing anything expensive in a trigger. That includes
>> any kind of network communication or file IO, regardless of whether the
>> code is VB6 COM or TSQL or CLR code in 2005.
>> David
>>
>sql
Is triggers multi-threaded?
Hi,
I need to call sp_OACreate inside an INSERT trigger to create COM component
that's developed in VB6 (STA), my question is if there are many records
inserted at very short period of time, is it multi-threaded (sp_OACreate -
MTA) from trigger point of view? If this is true, then I have to use the MTA
component in trigger.
Thanks!
JohnJohn Lee wrote:
> Hi,
> I need to call sp_OACreate inside an INSERT trigger to create COM componen
t
> that's developed in VB6 (STA), my question is if there are many records
> inserted at very short period of time, is it multi-threaded (sp_OACreate -
> MTA) from trigger point of view? If this is true, then I have to use the M
TA
> component in trigger.
> Thanks!
> John
Creating a COM component in a trigger is almost certainly an extremely
bad idea. There is no obvious way that your external code can be made
part of a SQL Server transaction. How will you roll-back the effect of
your VB code if a nested transaction is rolled back? If the external
code is non-transactional in nature then there is little sense in
putting it in a trigger and doing so can only hurt performance and
reliability. Also, in my limited experience the sp_OA procs will not
scale and COM will often leak memory.
Invoke your code from outside SQL Server is my suggestion - from a job
or some other server-side process for example. If you must do it in the
database then use a stored procedure rather than a trigger. In SQL
Server 2005 you also have the option of using a CLR proc.
To answer your original question. Triggers are "multi-threaded" in the
sense that the same trigger can fire simultaneously for several
different connections. Each of those connections will attempt to create
another instance of your COM object.
If multiple rows are updated in a single statement then the trigger
will only fire ONCE. It will NOT fire once for each row. This is
another reason why not to use a trigger to invoke external code. In
order to call your COM component for each row you will probably have to
use a cursor to cycle through each row in the INSERTED virtual table
(INSERTED is available only in triggers and contains the rows that were
inserted or updated). Cursors are best avoided in most circumstances
and especially so in triggers.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
> John Lee wrote:
> Creating a COM component in a trigger is almost certainly an extremely
> bad idea. There is no obvious way that your external code can be made
> part of a SQL Server transaction. How will you roll-back the effect of
> your VB code if a nested transaction is rolled back? If the external
> code is non-transactional in nature then there is little sense in
> putting it in a trigger and doing so can only hurt performance and
> reliability. Also, in my limited experience the sp_OA procs will not
> scale and COM will often leak memory.
> Invoke your code from outside SQL Server is my suggestion - from a job
> or some other server-side process for example. If you must do it in the
> database then use a stored procedure rather than a trigger. In SQL
> Server 2005 you also have the option of using a CLR proc.
> To answer your original question. Triggers are "multi-threaded" in the
> sense that the same trigger can fire simultaneously for several
> different connections. Each of those connections will attempt to create
> another instance of your COM object.
> If multiple rows are updated in a single statement then the trigger
> will only fire ONCE. It will NOT fire once for each row. This is
> another reason why not to use a trigger to invoke external code. In
> order to call your COM component for each row you will probably have to
> use a cursor to cycle through each row in the INSERTED virtual table
> (INSERTED is available only in triggers and contains the rows that were
> inserted or updated). Cursors are best avoided in most circumstances
> and especially so in triggers.
>
Ditto all of that. Just adding in COM programming an STA component can be
use by a multi-threaded client. The restriction is that each object
instance can be accessed only by the thread that created it. In a trigger
or procedure the COM component is created (SP_OACreate), used (SP_OAMethod)
and destroyed (SP_OADestroy) all in the same thread. So a plain VB6 STA COM
Component should work fine.
In addition to David's reasons why this is a poor idea, you should generally
avoid doing anything expensive in a trigger. That includes any kind of
network communication or file IO, regardless of whether the code is VB6 COM
or TSQL or CLR code in 2005.
David|||Hi David
STAs can certainly be launched from MTAs but the bigger problem with VB6 COM
components is that they use TLS which isn't designed to work with SQL
Server's UMS. Under load, this can lead to various possible undesirable
conditions such as inconsistent results, crashes & hung UMS schedulers (at
least in SQL2K).
If the original poster really HAS to create a component under a trigger, my
advice is to use sp_OACreate only if the trigger activity is low (as
developing in VB does have its advantages in terms of productivity). If the
trigger activity is high (meaning it has a high probability of being heavily
pre-empted & re-scheduled), consider either a different architecture
(asynch, MQ etc) or write an extended stored proc, perhaps with delphi or c.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
> Ditto all of that. Just adding in COM programming an STA component can be
> use by a multi-threaded client. The restriction is that each object
> instance can be accessed only by the thread that created it. In a trigger
> or procedure the COM component is created (SP_OACreate), used
> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
> plain VB6 STA COM Component should work fine.
> In addition to David's reasons why this is a poor idea, you should
> generally avoid doing anything expensive in a trigger. That includes any
> kind of network communication or file IO, regardless of whether the code
> is VB6 COM or TSQL or CLR code in 2005.
> David|||Thanks very much for your reply - David P, David B and Greg!!!
I totally agree with you all regarding the bad things using triggers doing
heavy lifting job ... BUT here is the business problem I have to solve:
We need to sync some data from SQL server 2000 to CRM in near realtime (< 5
minutes)
so we added triggers to around 10 tables, inside those triggers, we only
write 4 pieces of info to ChangeLog table
1. option 1 - write a service to pull ChangeLog table every 2 minutes
2. option 2 - create a trigger on this table to write out a text file to
notify the service to do the sync work
I also developed an extended stored proc to write file out and this seems
twice as fast as the sp_OACreate.
sp_OACreate has two issues to me:
1. late binding - because we are using progID to create the object
2. possible MTA calling into STA issue - if triggers side are multiple
threaded, then calling into STA will make it as serialized operation.
Thanks!
John
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi David
> STAs can certainly be launched from MTAs but the bigger problem with VB6
> COM components is that they use TLS which isn't designed to work with SQL
> Server's UMS. Under load, this can lead to various possible undesirable
> conditions such as inconsistent results, crashes & hung UMS schedulers (at
> least in SQL2K).
> If the original poster really HAS to create a component under a trigger,
> my advice is to use sp_OACreate only if the trigger activity is low (as
> developing in VB does have its advantages in terms of productivity). If
> the trigger activity is high (meaning it has a high probability of being
> heavily pre-empted & re-scheduled), consider either a different
> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
> with delphi or c.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>|||A much safer and more efficient way to do this would be to have your trigger
write to staging tables and have an external process read those tables once
a minute or so. In SQL Server 2005 you could use the Service Broker to do
this in a scaleable and asynchronous way.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Lee" <pursca@.newsgroups.nospam> wrote in message
news:O$27%237M4GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Thanks very much for your reply - David P, David B and Greg!!!
> I totally agree with you all regarding the bad things using triggers doing
> heavy lifting job ... BUT here is the business problem I have to solve:
> We need to sync some data from SQL server 2000 to CRM in near realtime (<
> 5 minutes)
> so we added triggers to around 10 tables, inside those triggers, we only
> write 4 pieces of info to ChangeLog table
> 1. option 1 - write a service to pull ChangeLog table every 2 minutes
> 2. option 2 - create a trigger on this table to write out a text file to
> notify the service to do the sync work
> I also developed an extended stored proc to write file out and this seems
> twice as fast as the sp_OACreate.
> sp_OACreate has two issues to me:
> 1. late binding - because we are using progID to create the object
> 2. possible MTA calling into STA issue - if triggers side are multiple
> threaded, then calling into STA will make it as serialized operation.
> Thanks!
> John
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
>
I need to call sp_OACreate inside an INSERT trigger to create COM component
that's developed in VB6 (STA), my question is if there are many records
inserted at very short period of time, is it multi-threaded (sp_OACreate -
MTA) from trigger point of view? If this is true, then I have to use the MTA
component in trigger.
Thanks!
JohnJohn Lee wrote:
> Hi,
> I need to call sp_OACreate inside an INSERT trigger to create COM componen
t
> that's developed in VB6 (STA), my question is if there are many records
> inserted at very short period of time, is it multi-threaded (sp_OACreate -
> MTA) from trigger point of view? If this is true, then I have to use the M
TA
> component in trigger.
> Thanks!
> John
Creating a COM component in a trigger is almost certainly an extremely
bad idea. There is no obvious way that your external code can be made
part of a SQL Server transaction. How will you roll-back the effect of
your VB code if a nested transaction is rolled back? If the external
code is non-transactional in nature then there is little sense in
putting it in a trigger and doing so can only hurt performance and
reliability. Also, in my limited experience the sp_OA procs will not
scale and COM will often leak memory.
Invoke your code from outside SQL Server is my suggestion - from a job
or some other server-side process for example. If you must do it in the
database then use a stored procedure rather than a trigger. In SQL
Server 2005 you also have the option of using a CLR proc.
To answer your original question. Triggers are "multi-threaded" in the
sense that the same trigger can fire simultaneously for several
different connections. Each of those connections will attempt to create
another instance of your COM object.
If multiple rows are updated in a single statement then the trigger
will only fire ONCE. It will NOT fire once for each row. This is
another reason why not to use a trigger to invoke external code. In
order to call your COM component for each row you will probably have to
use a cursor to cycle through each row in the INSERTED virtual table
(INSERTED is available only in triggers and contains the rows that were
inserted or updated). Cursors are best avoided in most circumstances
and especially so in triggers.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
> John Lee wrote:
> Creating a COM component in a trigger is almost certainly an extremely
> bad idea. There is no obvious way that your external code can be made
> part of a SQL Server transaction. How will you roll-back the effect of
> your VB code if a nested transaction is rolled back? If the external
> code is non-transactional in nature then there is little sense in
> putting it in a trigger and doing so can only hurt performance and
> reliability. Also, in my limited experience the sp_OA procs will not
> scale and COM will often leak memory.
> Invoke your code from outside SQL Server is my suggestion - from a job
> or some other server-side process for example. If you must do it in the
> database then use a stored procedure rather than a trigger. In SQL
> Server 2005 you also have the option of using a CLR proc.
> To answer your original question. Triggers are "multi-threaded" in the
> sense that the same trigger can fire simultaneously for several
> different connections. Each of those connections will attempt to create
> another instance of your COM object.
> If multiple rows are updated in a single statement then the trigger
> will only fire ONCE. It will NOT fire once for each row. This is
> another reason why not to use a trigger to invoke external code. In
> order to call your COM component for each row you will probably have to
> use a cursor to cycle through each row in the INSERTED virtual table
> (INSERTED is available only in triggers and contains the rows that were
> inserted or updated). Cursors are best avoided in most circumstances
> and especially so in triggers.
>
Ditto all of that. Just adding in COM programming an STA component can be
use by a multi-threaded client. The restriction is that each object
instance can be accessed only by the thread that created it. In a trigger
or procedure the COM component is created (SP_OACreate), used (SP_OAMethod)
and destroyed (SP_OADestroy) all in the same thread. So a plain VB6 STA COM
Component should work fine.
In addition to David's reasons why this is a poor idea, you should generally
avoid doing anything expensive in a trigger. That includes any kind of
network communication or file IO, regardless of whether the code is VB6 COM
or TSQL or CLR code in 2005.
David|||Hi David
STAs can certainly be launched from MTAs but the bigger problem with VB6 COM
components is that they use TLS which isn't designed to work with SQL
Server's UMS. Under load, this can lead to various possible undesirable
conditions such as inconsistent results, crashes & hung UMS schedulers (at
least in SQL2K).
If the original poster really HAS to create a component under a trigger, my
advice is to use sp_OACreate only if the trigger activity is low (as
developing in VB does have its advantages in terms of productivity). If the
trigger activity is high (meaning it has a high probability of being heavily
pre-empted & re-scheduled), consider either a different architecture
(asynch, MQ etc) or write an extended stored proc, perhaps with delphi or c.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
> Ditto all of that. Just adding in COM programming an STA component can be
> use by a multi-threaded client. The restriction is that each object
> instance can be accessed only by the thread that created it. In a trigger
> or procedure the COM component is created (SP_OACreate), used
> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
> plain VB6 STA COM Component should work fine.
> In addition to David's reasons why this is a poor idea, you should
> generally avoid doing anything expensive in a trigger. That includes any
> kind of network communication or file IO, regardless of whether the code
> is VB6 COM or TSQL or CLR code in 2005.
> David|||Thanks very much for your reply - David P, David B and Greg!!!
I totally agree with you all regarding the bad things using triggers doing
heavy lifting job ... BUT here is the business problem I have to solve:
We need to sync some data from SQL server 2000 to CRM in near realtime (< 5
minutes)
so we added triggers to around 10 tables, inside those triggers, we only
write 4 pieces of info to ChangeLog table
1. option 1 - write a service to pull ChangeLog table every 2 minutes
2. option 2 - create a trigger on this table to write out a text file to
notify the service to do the sync work
I also developed an extended stored proc to write file out and this seems
twice as fast as the sp_OACreate.
sp_OACreate has two issues to me:
1. late binding - because we are using progID to create the object
2. possible MTA calling into STA issue - if triggers side are multiple
threaded, then calling into STA will make it as serialized operation.
Thanks!
John
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi David
> STAs can certainly be launched from MTAs but the bigger problem with VB6
> COM components is that they use TLS which isn't designed to work with SQL
> Server's UMS. Under load, this can lead to various possible undesirable
> conditions such as inconsistent results, crashes & hung UMS schedulers (at
> least in SQL2K).
> If the original poster really HAS to create a component under a trigger,
> my advice is to use sp_OACreate only if the trigger activity is low (as
> developing in VB does have its advantages in terms of productivity). If
> the trigger activity is high (meaning it has a high probability of being
> heavily pre-empted & re-scheduled), consider either a different
> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
> with delphi or c.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>|||A much safer and more efficient way to do this would be to have your trigger
write to staging tables and have an external process read those tables once
a minute or so. In SQL Server 2005 you could use the Service Broker to do
this in a scaleable and asynchronous way.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Lee" <pursca@.newsgroups.nospam> wrote in message
news:O$27%237M4GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Thanks very much for your reply - David P, David B and Greg!!!
> I totally agree with you all regarding the bad things using triggers doing
> heavy lifting job ... BUT here is the business problem I have to solve:
> We need to sync some data from SQL server 2000 to CRM in near realtime (<
> 5 minutes)
> so we added triggers to around 10 tables, inside those triggers, we only
> write 4 pieces of info to ChangeLog table
> 1. option 1 - write a service to pull ChangeLog table every 2 minutes
> 2. option 2 - create a trigger on this table to write out a text file to
> notify the service to do the sync work
> I also developed an extended stored proc to write file out and this seems
> twice as fast as the sp_OACreate.
> sp_OACreate has two issues to me:
> 1. late binding - because we are using progID to create the object
> 2. possible MTA calling into STA issue - if triggers side are multiple
> threaded, then calling into STA will make it as serialized operation.
> Thanks!
> John
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
>
Monday, March 26, 2012
Is this possible without a cursor?
I have something like
update table
set field = ...
where field = ...
and for each entry that was effected by this query I want to insert an entry into another table.
I have always done this with cursors is there a more effecient way? For some reason cursors run a lot slower on my sql2005 server than the sql2000 server...
im not sure if this is what you want but maybe something like this... well first of all you cant do two updates on two tables in the same query so you would have to do it in a sproc (am i right?)
so it would be like
updated table2
set field = "my child"
where field in (select field1
from table1
where field = "something")
update table 1
set field ="my parent"
where field = "something"
is that right? sorry i dont know the answer off the top of my head just trying to help
I believe you are looking for the output clause of the update statement:
USE AdventureWorks;GODECLARE @.MyTableVar table( EmpID int NOT NULL, OldVacationHours int, NewVacationHours int, ModifiedDate datetime);UPDATE TOP (10) HumanResources.EmployeeSET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.EmployeeID, DELETED.VacationHours, INSERTED.VacationHours, INSERTED.ModifiedDateINTO @.MyTableVar;--Display the result set of the table variable.SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDateFROM @.MyTableVar;GO--Display the result set of the table.--Note that ModifiedDate reflects the value generated by an--AFTER UPDATE trigger.SELECT TOP (10) EmployeeID, VacationHours, ModifiedDateFROM HumanResources.Employee;GO
Wednesday, March 21, 2012
Is this an SQL bug (SQL 2000)?
Hi....
Can anyone explain the following results:
CREATE TABLE A
(
A varchar(256) NOT NULL
)
go
INSERT INTO A VALUES('test')
go
SELECT * FROM A WHERE A LIKE 'test'
go
=> Returns 1 row
DECLARE @.mytest varchar
SET @.mytest = 'test'
SELECT * FROM A WHERE A LIKE @.mytest
go
=> Returns nothing! Why?
thanks,
Neil"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172825344.594113.234760@.j27g2000cwj.googlegroups.com...
> Hi....
> Can anyone explain the following results:
> CREATE TABLE A
> (
> A varchar(256) NOT NULL
> )
> go
> INSERT INTO A VALUES('test')
> go
> SELECT * FROM A WHERE A LIKE 'test'
> go
> => Returns 1 row
> DECLARE @.mytest varchar
> SET @.mytest = 'test'
> SELECT * FROM A WHERE A LIKE @.mytest
> go
> => Returns nothing! Why?
>
Not a bug.
"DECLARE @.mytest varchar" is equivalent to "DECLARE @.mytest varchar(1)".
So your second SELECT statement is equivalent to "SELECT * FROM A WHERE A
LIKE 't'".
Always specify the size for VARCHAR/NVARCHAR.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Can anyone explain the following results:
CREATE TABLE A
(
A varchar(256) NOT NULL
)
go
INSERT INTO A VALUES('test')
go
SELECT * FROM A WHERE A LIKE 'test'
go
=> Returns 1 row
DECLARE @.mytest varchar
SET @.mytest = 'test'
SELECT * FROM A WHERE A LIKE @.mytest
go
=> Returns nothing! Why?
thanks,
Neil"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172825344.594113.234760@.j27g2000cwj.googlegroups.com...
> Hi....
> Can anyone explain the following results:
> CREATE TABLE A
> (
> A varchar(256) NOT NULL
> )
> go
> INSERT INTO A VALUES('test')
> go
> SELECT * FROM A WHERE A LIKE 'test'
> go
> => Returns 1 row
> DECLARE @.mytest varchar
> SET @.mytest = 'test'
> SELECT * FROM A WHERE A LIKE @.mytest
> go
> => Returns nothing! Why?
>
Not a bug.
"DECLARE @.mytest varchar" is equivalent to "DECLARE @.mytest varchar(1)".
So your second SELECT statement is equivalent to "SELECT * FROM A WHERE A
LIKE 't'".
Always specify the size for VARCHAR/NVARCHAR.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Friday, March 9, 2012
Is there anyway to issue a http POST from SQL Server?
Just wondering if there is a way to issue a http post from SQL? Basically I
want to write an insert trigger that will create a SOAP msg and then send it
to a webservice, anyone know if there is an internal mechanism in
SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
thinking an answer might be to write it in C and the create an Extended
Stored Procedure but was ondering if there was another way.It's not SQL based, but there is a way in SQL Server 2005, using .NET
procedure or trigger. It's conceivable you might do the same in SQL Server
2000 using SP_OACreate and friends, but IIRC, the COM component that
implements this (from Wininet?) may not be thread safe. Your own XP is
another way.
Bob Beauchemin
http://www.sqlskills.com/blogs/bobb
"James Morton" <james_morton@.hotmail.com> wrote in message
news:OUoPHrroFHA.420@.TK2MSFTNGP09.phx.gbl...
> Just wondering if there is a way to issue a http post from SQL? Basically
> I
> want to write an insert trigger that will create a SOAP msg and then send
> it
> to a webservice, anyone know if there is an internal mechanism in
> SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
> thinking an answer might be to write it in C and the create an Extended
> Stored Procedure but was ondering if there was another way.
>
want to write an insert trigger that will create a SOAP msg and then send it
to a webservice, anyone know if there is an internal mechanism in
SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
thinking an answer might be to write it in C and the create an Extended
Stored Procedure but was ondering if there was another way.It's not SQL based, but there is a way in SQL Server 2005, using .NET
procedure or trigger. It's conceivable you might do the same in SQL Server
2000 using SP_OACreate and friends, but IIRC, the COM component that
implements this (from Wininet?) may not be thread safe. Your own XP is
another way.
Bob Beauchemin
http://www.sqlskills.com/blogs/bobb
"James Morton" <james_morton@.hotmail.com> wrote in message
news:OUoPHrroFHA.420@.TK2MSFTNGP09.phx.gbl...
> Just wondering if there is a way to issue a http post from SQL? Basically
> I
> want to write an insert trigger that will create a SOAP msg and then send
> it
> to a webservice, anyone know if there is an internal mechanism in
> SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
> thinking an answer might be to write it in C and the create an Extended
> Stored Procedure but was ondering if there was another way.
>
Is there anyway to issue a http POST from SQL Server?
Just wondering if there is a way to issue a http post from SQL? Basically I
want to write an insert trigger that will create a SOAP msg and then send it
to a webservice, anyone know if there is an internal mechanism in
SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
thinking an answer might be to write it in C and the create an Extended
Stored Procedure but was ondering if there was another way.
It's not SQL based, but there is a way in SQL Server 2005, using .NET
procedure or trigger. It's conceivable you might do the same in SQL Server
2000 using SP_OACreate and friends, but IIRC, the COM component that
implements this (from Wininet?) may not be thread safe. Your own XP is
another way.
Bob Beauchemin
http://www.sqlskills.com/blogs/bobb
"James Morton" <james_morton@.hotmail.com> wrote in message
news:OUoPHrroFHA.420@.TK2MSFTNGP09.phx.gbl...
> Just wondering if there is a way to issue a http post from SQL? Basically
> I
> want to write an insert trigger that will create a SOAP msg and then send
> it
> to a webservice, anyone know if there is an internal mechanism in
> SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
> thinking an answer might be to write it in C and the create an Extended
> Stored Procedure but was ondering if there was another way.
>
want to write an insert trigger that will create a SOAP msg and then send it
to a webservice, anyone know if there is an internal mechanism in
SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
thinking an answer might be to write it in C and the create an Extended
Stored Procedure but was ondering if there was another way.
It's not SQL based, but there is a way in SQL Server 2005, using .NET
procedure or trigger. It's conceivable you might do the same in SQL Server
2000 using SP_OACreate and friends, but IIRC, the COM component that
implements this (from Wininet?) may not be thread safe. Your own XP is
another way.
Bob Beauchemin
http://www.sqlskills.com/blogs/bobb
"James Morton" <james_morton@.hotmail.com> wrote in message
news:OUoPHrroFHA.420@.TK2MSFTNGP09.phx.gbl...
> Just wondering if there is a way to issue a http post from SQL? Basically
> I
> want to write an insert trigger that will create a SOAP msg and then send
> it
> to a webservice, anyone know if there is an internal mechanism in
> SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
> thinking an answer might be to write it in C and the create an Extended
> Stored Procedure but was ondering if there was another way.
>
Is there any way to insert picture to image datatype in sql server 2000 without using fron
Sir,
Is there any way to insert picture to image datatype in sql server 2000 without using front end. If so please let me know.
Thanks in Advance,
Arun.
Hi,
Is there any way to insert picture to image datatype in sql server 2000 without using front end
From your description, I'm not sure what does the front end mean in your question? But I guess that you want to save your images into SqlServer without "choosing the filepath, clicking the save button", right?
If so, I think there's no difference between the way you save the image with front end. Just assign the exact file path to a local variable, read the file into a byte array, and then write your insert statement, save the array object into the image filed in your database.
For reference, see:
http://www.codeproject.com/useritems/Store_images_in_SQL_Serve.asp
Thanks.
Hi,
What i need is, I need to insert picture in sql query analyzer itself,
just like
normal sql query insertion for int datatype is insert tbl(col) values(1);
so, how can i insert for image datatype?
Please let me know how to do.
Thanks in Advance,
Arun.
|||
Hi,
The image actually existed in the File System of your operating system. Before you want to add it to the image field of your database, you shouldconvert the image file to a byte array, that's something must be done.
As you say, you want to execute your insert command in your query analyzer directly, that is to say you have to write the byte array explicitly in your text command, obviously, you can't achieve that. The other way is just passing a byte array to stored procedure, and execute your insert command with the array object. But the work of "locating files and converting types" can't be achieved while just using query analyzer.
Thanks.
Is there any way to insert image into DB without coding ?
I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.
See my response to your identical question posted in the [SQL Server Database Engine] forum.
Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).
Is there any way to insert image into DB without coding ?
I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005 without coding. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.
Microsoft Access is a client (front-end) application that happens to also work with data storage.
SQL Server is a server (back-end) application that requires a client to move data in and out.
So the short answer is No. However, there are some client applications that do not appear to require 'coding' since it is done 'beneath the covers' for you.
You can connect Access to a SQL Server database, and then use your skills with Access to move data into and out of SQL Server.
|||It is typically a better practice to store the images elsewhere and store a reference to the image in a database; give a look here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1010368&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362808&SiteID=1
(In a short while I will delete the other post that duplicates this one.)
As always, Arnie, thank you for your help with all of this.
Friday, February 24, 2012
Is there any other faster method to compare and update table?
TABLE1 has 5,000,000 records, TABLE2 has 1,000,000 records.
I must compare these two tables and insert to TABLE3 and update TABLE1.
Is there any other faster method can replace the following method?
Thanks.
---
CREATE PROCEDURE RMSTEST1 AS
DECLARE tb1_cursor CURSOR
FOR
SELECT A5,A11,A28,A30 FROM TABLE1
OPEN tb1_cursor
DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF @.V11 IN (SELECT B2 FROM TABLE2)
BEGIN
INSERT INTO TABLE3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
INSERT INTO TABLE3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
END
ELSE
BEGIN
INSERT INTO TABLE3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
INSERT INTO TABLE3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
END
UPDATE TABLE1 SET A39='Y' WHERE CURRENT OF tb1_cursor
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
END
CLOSE tb1_cursor
deallocate tb1_cursorEllen
At first glance a I'd use NOT EXISTS clause to eliminate the rows
SELECT <columns list> FROM Table1
WHERE NOT EXISTS
(SELECT * FROM Table2 WHERE Table1.PK=Table2.PK)
You can insert an output into a temporary table and then to manipulate with
UPDATE statement as you need.
"Ellen" <Ellen@.discussions.microsoft.com> wrote in message
news:EF2570C6-2A36-416E-AF70-BFBB53A20475@.microsoft.com...
> TABLE1 has 5,000,000 records, TABLE2 has 1,000,000 records.
> I must compare these two tables and insert to TABLE3 and update TABLE1.
> Is there any other faster method can replace the following method?
> Thanks.
> ---
> CREATE PROCEDURE RMSTEST1 AS
> DECLARE tb1_cursor CURSOR
> FOR
> SELECT A5,A11,A28,A30 FROM TABLE1
> OPEN tb1_cursor
> DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF @.V11 IN (SELECT B2 FROM TABLE2)
> BEGIN
> INSERT INTO TABLE3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
> INSERT INTO TABLE3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
> END
> ELSE
> BEGIN
> INSERT INTO TABLE3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
> INSERT INTO TABLE3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
> END
> UPDATE TABLE1 SET A39='Y' WHERE CURRENT OF tb1_cursor
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> END
> CLOSE tb1_cursor
> deallocate tb1_cursor|||Please include DDL with your posts otherwise we can only guess at your
table structure and exact requirements. Here's an example, assuming B2
is unique in Table2:
INSERT INTO Table3 (/* ... columns list? */)
SELECT T1.a5, ...
CASE WHEN T2.b2 IS NOT NULL THEN '11110000' ELSE '11120000' END,
CASE WHEN T2.b2 IS NOT NULL THEN '22220000' ELSE '22230000' END,
CASE WHEN T2.b2 IS NOT NULL THEN 'D' ELSE 'C' END
FROM Table1 AS T1
LEFT JOIN Table2 AS AS T2
ON T1.a11 = T2.b2 /* B2 is unique? */
--
David Portas
SQL Server MVP
--|||The full script is:
-- Create Tables
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES3]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES4]
GO
CREATE TABLE [dbo].[SUSTES1] (
[A1] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A2] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A3] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A4] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A5] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A6] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A7] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A8] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A9] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A10] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A11] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A12] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A13] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A14] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A15] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A16] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A17] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A18] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A19] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A20] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A21] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A22] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A23] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A24] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A25] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A26] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A27] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A28] [int] NULL ,
[A29] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A30] [int] NULL ,
[A31] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A32] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A33] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A34] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A35] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A36] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A37] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A38] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A39] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES2] (
[B1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B3] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B4] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B5] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B6] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B7] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B8] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B9] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B11] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B12] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B13] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B14] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B15] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B16] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B17] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B18] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B19] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B20] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B21] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES3] (
[C1] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[C2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[C3] [int] NULL ,
[C4] [char] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES4] (
[D1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[D2] [int] NULL ,
[D3] [int] NULL
) ON [PRIMARY]
GO
--Import Datat
BULK INSERT SUSTES1 FROM 'D:\Table1.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
BULK INSERT SUSTES2 FROM 'D:\Table2.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
--Create INDEX
CREATE INDEX PK_B2
ON SUSTES2(B2)
GO
--Main Process
DECLARE tb1_cursor CURSOR
FOR
SELECT A5,A11,A28,A30 FROM SUSTES1
OPEN tb1_cursor
DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF @.V11 IN (SELECT B2 FROM SUSTES2)
BEGIN
INSERT INTO SUSTES3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
INSERT INTO SUSTES3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
END
ELSE
BEGIN
INSERT INTO SUSTES3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
INSERT INTO SUSTES3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
END
UPDATE SUSTES1 SET A39='Y' WHERE CURRENT OF tb1_cursor
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
END
CLOSE tb1_cursor
deallocate tb1_cursor
--Update Table4
INSERT INTO SUSTES4
SELECT C2,SUM(C3),COUNT(*)
FROM SUSTES3
GROUP BY C2
Ellen
"David Portas" wrote:
> Please include DDL with your posts otherwise we can only guess at your
> table structure and exact requirements. Here's an example, assuming B2
> is unique in Table2:
> INSERT INTO Table3 (/* ... columns list? */)
> SELECT T1.a5, ...
> CASE WHEN T2.b2 IS NOT NULL THEN '11110000' ELSE '11120000' END,
> CASE WHEN T2.b2 IS NOT NULL THEN '22220000' ELSE '22230000' END,
> CASE WHEN T2.b2 IS NOT NULL THEN 'D' ELSE 'C' END
> FROM Table1 AS T1
> LEFT JOIN Table2 AS AS T2
> ON T1.a11 = T2.b2 /* B2 is unique? */
> --
> David Portas
> SQL Server MVP
> --
>|||This should take care of the cursor altogether.
INSERT SUITES3
SELECT A5,CASE WHEN B2 IS NULL THEN 10000 ELSE 0 END +I,A30-A28,J
FROM SUITES1 LEFT JOIN SUITES2 ON SUITES1.A5=SUITES2.B2
CROSS JOIN (SELECT 11110000,'D' UNION SELECT 22220000,'C')X(I,J)
-- WHERE A39='N'
-- UPDATE SUSTES1 SET A39='Y'
-oj
"Ellen Huang" <Ellen Huang@.discussions.microsoft.com> wrote in message
news:BCC149AC-6728-4EF1-B42A-E34C21FF5B8C@.microsoft.com...
> The full script is:
> -- Create Tables
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES3]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES4]
> GO
> CREATE TABLE [dbo].[SUSTES1] (
> [A1] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A2] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A3] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A4] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A5] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A6] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A7] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A8] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A9] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A10] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A11] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A12] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A13] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A14] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A15] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A16] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A17] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A18] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A19] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A20] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A21] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A22] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A23] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A24] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A25] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A26] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A27] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A28] [int] NULL ,
> [A29] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A30] [int] NULL ,
> [A31] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A32] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A33] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A34] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A35] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A36] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A37] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A38] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A39] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES2] (
> [B1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B3] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B4] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B5] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B6] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B7] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B8] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B9] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B10] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B11] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B12] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B13] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B14] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B15] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B16] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B17] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B18] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B19] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B20] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B21] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES3] (
> [C1] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [C2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [C3] [int] NULL ,
> [C4] [char] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES4] (
> [D1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [D2] [int] NULL ,
> [D3] [int] NULL
> ) ON [PRIMARY]
> GO
> --Import Datat
> BULK INSERT SUSTES1 FROM 'D:\Table1.csv'
> WITH (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
> BULK INSERT SUSTES2 FROM 'D:\Table2.csv'
> WITH (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
>
> --Create INDEX
> CREATE INDEX PK_B2
> ON SUSTES2(B2)
> GO
>
> --Main Process
> DECLARE tb1_cursor CURSOR
> FOR
> SELECT A5,A11,A28,A30 FROM SUSTES1
> OPEN tb1_cursor
> DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF @.V11 IN (SELECT B2 FROM SUSTES2)
> BEGIN
> INSERT INTO SUSTES3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
> INSERT INTO SUSTES3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
> END
> ELSE
> BEGIN
> INSERT INTO SUSTES3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
> INSERT INTO SUSTES3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
> END
> UPDATE SUSTES1 SET A39='Y' WHERE CURRENT OF tb1_cursor
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> END
> CLOSE tb1_cursor
> deallocate tb1_cursor
> --Update Table4
> INSERT INTO SUSTES4
> SELECT C2,SUM(C3),COUNT(*)
> FROM SUSTES3
> GROUP BY C2
>
> Ellen
> "David Portas" wrote:
>
I must compare these two tables and insert to TABLE3 and update TABLE1.
Is there any other faster method can replace the following method?
Thanks.
---
CREATE PROCEDURE RMSTEST1 AS
DECLARE tb1_cursor CURSOR
FOR
SELECT A5,A11,A28,A30 FROM TABLE1
OPEN tb1_cursor
DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF @.V11 IN (SELECT B2 FROM TABLE2)
BEGIN
INSERT INTO TABLE3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
INSERT INTO TABLE3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
END
ELSE
BEGIN
INSERT INTO TABLE3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
INSERT INTO TABLE3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
END
UPDATE TABLE1 SET A39='Y' WHERE CURRENT OF tb1_cursor
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
END
CLOSE tb1_cursor
deallocate tb1_cursorEllen
At first glance a I'd use NOT EXISTS clause to eliminate the rows
SELECT <columns list> FROM Table1
WHERE NOT EXISTS
(SELECT * FROM Table2 WHERE Table1.PK=Table2.PK)
You can insert an output into a temporary table and then to manipulate with
UPDATE statement as you need.
"Ellen" <Ellen@.discussions.microsoft.com> wrote in message
news:EF2570C6-2A36-416E-AF70-BFBB53A20475@.microsoft.com...
> TABLE1 has 5,000,000 records, TABLE2 has 1,000,000 records.
> I must compare these two tables and insert to TABLE3 and update TABLE1.
> Is there any other faster method can replace the following method?
> Thanks.
> ---
> CREATE PROCEDURE RMSTEST1 AS
> DECLARE tb1_cursor CURSOR
> FOR
> SELECT A5,A11,A28,A30 FROM TABLE1
> OPEN tb1_cursor
> DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF @.V11 IN (SELECT B2 FROM TABLE2)
> BEGIN
> INSERT INTO TABLE3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
> INSERT INTO TABLE3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
> END
> ELSE
> BEGIN
> INSERT INTO TABLE3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
> INSERT INTO TABLE3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
> END
> UPDATE TABLE1 SET A39='Y' WHERE CURRENT OF tb1_cursor
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> END
> CLOSE tb1_cursor
> deallocate tb1_cursor|||Please include DDL with your posts otherwise we can only guess at your
table structure and exact requirements. Here's an example, assuming B2
is unique in Table2:
INSERT INTO Table3 (/* ... columns list? */)
SELECT T1.a5, ...
CASE WHEN T2.b2 IS NOT NULL THEN '11110000' ELSE '11120000' END,
CASE WHEN T2.b2 IS NOT NULL THEN '22220000' ELSE '22230000' END,
CASE WHEN T2.b2 IS NOT NULL THEN 'D' ELSE 'C' END
FROM Table1 AS T1
LEFT JOIN Table2 AS AS T2
ON T1.a11 = T2.b2 /* B2 is unique? */
--
David Portas
SQL Server MVP
--|||The full script is:
-- Create Tables
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES3]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES4]
GO
CREATE TABLE [dbo].[SUSTES1] (
[A1] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A2] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A3] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A4] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A5] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A6] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A7] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A8] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A9] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A10] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A11] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A12] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A13] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A14] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A15] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A16] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A17] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A18] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A19] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A20] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A21] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A22] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A23] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A24] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A25] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A26] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A27] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A28] [int] NULL ,
[A29] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A30] [int] NULL ,
[A31] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A32] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A33] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A34] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A35] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A36] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A37] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A38] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A39] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES2] (
[B1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B3] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B4] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B5] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B6] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B7] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B8] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B9] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B11] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B12] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B13] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B14] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B15] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B16] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B17] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B18] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B19] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B20] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B21] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES3] (
[C1] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[C2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[C3] [int] NULL ,
[C4] [char] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES4] (
[D1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[D2] [int] NULL ,
[D3] [int] NULL
) ON [PRIMARY]
GO
--Import Datat
BULK INSERT SUSTES1 FROM 'D:\Table1.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
BULK INSERT SUSTES2 FROM 'D:\Table2.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
--Create INDEX
CREATE INDEX PK_B2
ON SUSTES2(B2)
GO
--Main Process
DECLARE tb1_cursor CURSOR
FOR
SELECT A5,A11,A28,A30 FROM SUSTES1
OPEN tb1_cursor
DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF @.V11 IN (SELECT B2 FROM SUSTES2)
BEGIN
INSERT INTO SUSTES3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
INSERT INTO SUSTES3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
END
ELSE
BEGIN
INSERT INTO SUSTES3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
INSERT INTO SUSTES3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
END
UPDATE SUSTES1 SET A39='Y' WHERE CURRENT OF tb1_cursor
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
END
CLOSE tb1_cursor
deallocate tb1_cursor
--Update Table4
INSERT INTO SUSTES4
SELECT C2,SUM(C3),COUNT(*)
FROM SUSTES3
GROUP BY C2
Ellen
"David Portas" wrote:
> Please include DDL with your posts otherwise we can only guess at your
> table structure and exact requirements. Here's an example, assuming B2
> is unique in Table2:
> INSERT INTO Table3 (/* ... columns list? */)
> SELECT T1.a5, ...
> CASE WHEN T2.b2 IS NOT NULL THEN '11110000' ELSE '11120000' END,
> CASE WHEN T2.b2 IS NOT NULL THEN '22220000' ELSE '22230000' END,
> CASE WHEN T2.b2 IS NOT NULL THEN 'D' ELSE 'C' END
> FROM Table1 AS T1
> LEFT JOIN Table2 AS AS T2
> ON T1.a11 = T2.b2 /* B2 is unique? */
> --
> David Portas
> SQL Server MVP
> --
>|||This should take care of the cursor altogether.
INSERT SUITES3
SELECT A5,CASE WHEN B2 IS NULL THEN 10000 ELSE 0 END +I,A30-A28,J
FROM SUITES1 LEFT JOIN SUITES2 ON SUITES1.A5=SUITES2.B2
CROSS JOIN (SELECT 11110000,'D' UNION SELECT 22220000,'C')X(I,J)
-- WHERE A39='N'
-- UPDATE SUSTES1 SET A39='Y'
-oj
"Ellen Huang" <Ellen Huang@.discussions.microsoft.com> wrote in message
news:BCC149AC-6728-4EF1-B42A-E34C21FF5B8C@.microsoft.com...
> The full script is:
> -- Create Tables
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES3]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES4]
> GO
> CREATE TABLE [dbo].[SUSTES1] (
> [A1] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A2] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A3] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A4] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A5] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A6] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A7] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A8] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A9] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A10] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A11] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A12] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A13] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A14] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A15] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A16] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A17] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A18] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A19] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A20] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A21] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A22] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A23] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A24] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A25] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A26] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A27] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A28] [int] NULL ,
> [A29] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A30] [int] NULL ,
> [A31] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A32] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A33] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A34] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A35] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A36] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A37] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A38] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A39] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES2] (
> [B1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B3] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B4] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B5] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B6] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B7] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B8] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B9] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B10] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B11] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B12] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B13] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B14] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B15] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B16] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B17] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B18] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B19] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B20] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B21] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES3] (
> [C1] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [C2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [C3] [int] NULL ,
> [C4] [char] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES4] (
> [D1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [D2] [int] NULL ,
> [D3] [int] NULL
> ) ON [PRIMARY]
> GO
> --Import Datat
> BULK INSERT SUSTES1 FROM 'D:\Table1.csv'
> WITH (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
> BULK INSERT SUSTES2 FROM 'D:\Table2.csv'
> WITH (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
>
> --Create INDEX
> CREATE INDEX PK_B2
> ON SUSTES2(B2)
> GO
>
> --Main Process
> DECLARE tb1_cursor CURSOR
> FOR
> SELECT A5,A11,A28,A30 FROM SUSTES1
> OPEN tb1_cursor
> DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF @.V11 IN (SELECT B2 FROM SUSTES2)
> BEGIN
> INSERT INTO SUSTES3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
> INSERT INTO SUSTES3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
> END
> ELSE
> BEGIN
> INSERT INTO SUSTES3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
> INSERT INTO SUSTES3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
> END
> UPDATE SUSTES1 SET A39='Y' WHERE CURRENT OF tb1_cursor
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> END
> CLOSE tb1_cursor
> deallocate tb1_cursor
> --Update Table4
> INSERT INTO SUSTES4
> SELECT C2,SUM(C3),COUNT(*)
> FROM SUSTES3
> GROUP BY C2
>
> Ellen
> "David Portas" wrote:
>
Subscribe to:
Posts (Atom)