I get an error, saying a previous instance of setup did not complete, select
continue or cancel to end.
Have removed the SQL foldersfrom \Program Files\xxx and the registry keys
for MSSQLSERVER, service entries etc., but setup keeps coming back with the
above message. Have not removed any files from \winnt or system32. Server
rest several times.
Is there some registry key in CurrentControlSet I need to delete'
Thought I knew SQL !!!
--
Thanks for any help!
Neil MillingtonCan you post the exact error message?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Belmann" <SQLServer@.belmann.co.uk> wrote in message
news:17D3E6B5-4465-4927-85F1-B73B5ADD70E7@.microsoft.com...
> I get an error, saying a previous instance of setup did not complete, select
> continue or cancel to end.
> Have removed the SQL foldersfrom \Program Files\xxx and the registry keys
> for MSSQLSERVER, service entries etc., but setup keeps coming back with the
> above message. Have not removed any files from \winnt or system32. Server
> rest several times.
> Is there some registry key in CurrentControlSet I need to delete'
> Thought I knew SQL !!!
> --
> Thanks for any help!
> Neil Millington|||Tibor... will post tonight as server is in ISP and I need to get behing
firewall!
Much appreciated.
"Tibor Karaszi" wrote:
> Can you post the exact error message?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Belmann" <SQLServer@.belmann.co.uk> wrote in message
> news:17D3E6B5-4465-4927-85F1-B73B5ADD70E7@.microsoft.com...
> > I get an error, saying a previous instance of setup did not complete, select
> > continue or cancel to end.
> >
> > Have removed the SQL foldersfrom \Program Files\xxx and the registry keys
> > for MSSQLSERVER, service entries etc., but setup keeps coming back with the
> > above message. Have not removed any files from \winnt or system32. Server
> > rest several times.
> >
> > Is there some registry key in CurrentControlSet I need to delete'
> >
> > Thought I knew SQL !!!
> > --
> > Thanks for any help!
> > Neil Millington
>
>|||I suspect the error is that you have another setup (doesn't have to be a SQL Server setup) which is
in "pending" state. If so, search KB for a suitable part of the error message and you will find the
KB which describes the registry modification you need to perform...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Belmann" <SQLServer@.belmann.co.uk> wrote in message
news:CDC4EFFD-BCDA-4C08-9DA5-E938DDA4DB72@.microsoft.com...
> Tibor... will post tonight as server is in ISP and I need to get behing
> firewall!
> Much appreciated.
> "Tibor Karaszi" wrote:
> > Can you post the exact error message?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Belmann" <SQLServer@.belmann.co.uk> wrote in message
> > news:17D3E6B5-4465-4927-85F1-B73B5ADD70E7@.microsoft.com...
> > > I get an error, saying a previous instance of setup did not complete, select
> > > continue or cancel to end.
> > >
> > > Have removed the SQL foldersfrom \Program Files\xxx and the registry keys
> > > for MSSQLSERVER, service entries etc., but setup keeps coming back with the
> > > above message. Have not removed any files from \winnt or system32. Server
> > > rest several times.
> > >
> > > Is there some registry key in CurrentControlSet I need to delete'
> > >
> > > Thought I knew SQL !!!
> > > --
> > > Thanks for any help!
> > > Neil Millington
> >
> >
> >|||This was not the error message I was thinking of, I'm afraid. I suggest you start searching Google,
the newsgroup archives and KB. If you don't find anything there, consider posting this to the .setup
group. And be prepared to include the sqlstp.log file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Belmann" <SQLServer@.belmann.co.uk> wrote in message
news:A8921334-D3E0-4AE1-9225-31E8FCAE562F@.microsoft.com...
> Tibor,
> Thanks for the reply, here is the message:
> <<
> setup has detected that the previous install attempt was not completed.
> Retry to continue installation or cancel to exit setup
> >>
> ...when selecting 'Retry' setup starts to file copy into \program
> files\microsoft sql server but fails when trying to run script 1 (as
> everything has been removed from drive/registry).
> Neil.
> "Tibor Karaszi" wrote:
> > Can you post the exact error message?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Belmann" <SQLServer@.belmann.co.uk> wrote in message
> > news:17D3E6B5-4465-4927-85F1-B73B5ADD70E7@.microsoft.com...
> > > I get an error, saying a previous instance of setup did not complete, select
> > > continue or cancel to end.
> > >
> > > Have removed the SQL foldersfrom \Program Files\xxx and the registry keys
> > > for MSSQLSERVER, service entries etc., but setup keeps coming back with the
> > > above message. Have not removed any files from \winnt or system32. Server
> > > rest several times.
> > >
> > > Is there some registry key in CurrentControlSet I need to delete'
> > >
> > > Thought I knew SQL !!!
> > > --
> > > Thanks for any help!
> > > Neil Millington
> >
> >
> >|||Thanks Tibor...will post tonight, as server is in remote ISP and I need to
get behing the firewall.
Much appreciated,
Neil.
"Tibor Karaszi" wrote:
> Can you post the exact error message?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Belmann" <SQLServer@.belmann.co.uk> wrote in message
> news:17D3E6B5-4465-4927-85F1-B73B5ADD70E7@.microsoft.com...
> > I get an error, saying a previous instance of setup did not complete, select
> > continue or cancel to end.
> >
> > Have removed the SQL foldersfrom \Program Files\xxx and the registry keys
> > for MSSQLSERVER, service entries etc., but setup keeps coming back with the
> > above message. Have not removed any files from \winnt or system32. Server
> > rest several times.
> >
> > Is there some registry key in CurrentControlSet I need to delete'
> >
> > Thought I knew SQL !!!
> > --
> > Thanks for any help!
> > Neil Millington
>
>|||Tibor,
Thanks for the reply, here is the message:
<<
setup has detected that the previous install attempt was not completed.
Retry to continue installation or cancel to exit setup
...when selecting 'Retry' setup starts to file copy into \program
files\microsoft sql server but fails when trying to run script 1 (as
everything has been removed from drive/registry).
Neil.
"Tibor Karaszi" wrote:
> Can you post the exact error message?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Belmann" <SQLServer@.belmann.co.uk> wrote in message
> news:17D3E6B5-4465-4927-85F1-B73B5ADD70E7@.microsoft.com...
> > I get an error, saying a previous instance of setup did not complete, select
> > continue or cancel to end.
> >
> > Have removed the SQL foldersfrom \Program Files\xxx and the registry keys
> > for MSSQLSERVER, service entries etc., but setup keeps coming back with the
> > above message. Have not removed any files from \winnt or system32. Server
> > rest several times.
> >
> > Is there some registry key in CurrentControlSet I need to delete'
> >
> > Thought I knew SQL !!!
> > --
> > Thanks for any help!
> > Neil Millington
>
>
Showing posts with label select. Show all posts
Showing posts with label select. Show all posts
Wednesday, March 21, 2012
Friday, March 9, 2012
inset table using sp
Hi,
I have a select statement from tb1 and tb2:
select field1 from tb1 where field1 not in (select field1 from tb2)
order by field1
I want to have the result inserted into tb3 using a SP.
tb3 has only one filed, same type. How to do this?
ThanksCheck out INSERT INTO in BOL.
INSERT INTO tb3
SELECT field1 FROM tb1 WHERE field1 NOT IN (SELECT field1 FROM tb2)
You can put it in a stored procedure like this:
CREATE PROCEDURE dbo.usp_DoInsert
AS
INSERT INTO tb3
SELECT field1 FROM tb1 WHERE field1 NOT IN (SELECT field1 FROM tb2)
GO
BTW you might want to reconsider your naming conventions - this particular
setup is going to be very hard to understand and maintain.
"whph" <nospam@.nospam.com> wrote in message
news:mqo2719mcnvjerec932v8i49mof003ikk4@.
4ax.com...
> Hi,
> I have a select statement from tb1 and tb2:
> select field1 from tb1 where field1 not in (select field1 from tb2)
> order by field1
> I want to have the result inserted into tb3 using a SP.
> tb3 has only one filed, same type. How to do this?
> Thanks
I have a select statement from tb1 and tb2:
select field1 from tb1 where field1 not in (select field1 from tb2)
order by field1
I want to have the result inserted into tb3 using a SP.
tb3 has only one filed, same type. How to do this?
ThanksCheck out INSERT INTO in BOL.
INSERT INTO tb3
SELECT field1 FROM tb1 WHERE field1 NOT IN (SELECT field1 FROM tb2)
You can put it in a stored procedure like this:
CREATE PROCEDURE dbo.usp_DoInsert
AS
INSERT INTO tb3
SELECT field1 FROM tb1 WHERE field1 NOT IN (SELECT field1 FROM tb2)
GO
BTW you might want to reconsider your naming conventions - this particular
setup is going to be very hard to understand and maintain.
"whph" <nospam@.nospam.com> wrote in message
news:mqo2719mcnvjerec932v8i49mof003ikk4@.
4ax.com...
> Hi,
> I have a select statement from tb1 and tb2:
> select field1 from tb1 where field1 not in (select field1 from tb2)
> order by field1
> I want to have the result inserted into tb3 using a SP.
> tb3 has only one filed, same type. How to do this?
> Thanks
INSERT-SELECT depending on the Select:ed order
I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.
However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?
Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).This sure looks like a "tip of the iceberg" thread...
You can include an ORDER BY statement in your INSERT code, at the end of statement where you would normally place it.
Your trigger is only going to fire ONCE per transaction, no matter how many records are in the transaction. Insert 1000 individual records and the trigger fires 1000 times, but do a single insert of 1000 records and your trigger will only fire once.
Now the big question...what the heck are you doing that requires ordered inserts into a view with an INSTEAD OF trigger? Such complexity is rarely necessary.|||Thanks; I noticed that the records "sent" from the SELECT to the INSERT appeared in reversed order. And, I though the trigger was fired once per record. I've created an SP to take care of it.
The requirement on the order is because this table keeps track of how users logon to an application and then logoff. I'm changing the table from a structure where each such event has its own table row, to a structure where both events are stored in the same row (one datetime column for the logons and one for the logoffs). When transferring data from the old table to the new, I'm assuming that the Logon records are read before the Logoff records, since the Logon records mean an INSERT into the new table while the Logoff records mean an UPDATE of an already existing row (having a Logon date registered).|||You should probably just include a datetime value in your recordset rather than relying on record order.
However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?
Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).This sure looks like a "tip of the iceberg" thread...
You can include an ORDER BY statement in your INSERT code, at the end of statement where you would normally place it.
Your trigger is only going to fire ONCE per transaction, no matter how many records are in the transaction. Insert 1000 individual records and the trigger fires 1000 times, but do a single insert of 1000 records and your trigger will only fire once.
Now the big question...what the heck are you doing that requires ordered inserts into a view with an INSTEAD OF trigger? Such complexity is rarely necessary.|||Thanks; I noticed that the records "sent" from the SELECT to the INSERT appeared in reversed order. And, I though the trigger was fired once per record. I've created an SP to take care of it.
The requirement on the order is because this table keeps track of how users logon to an application and then logoff. I'm changing the table from a structure where each such event has its own table row, to a structure where both events are stored in the same row (one datetime column for the logons and one for the logoffs). When transferring data from the old table to the new, I'm assuming that the Logon records are read before the Logoff records, since the Logon records mean an INSERT into the new table while the Logoff records mean an UPDATE of an already existing row (having a Logon date registered).|||You should probably just include a datetime value in your recordset rather than relying on record order.
Wednesday, March 7, 2012
Inserting/Updating and locking
We are inserting and updating large amounts of rows and find that users are
complaining that their SELECT queries on the same tables are blocking (not
finishing) until our INSERT or UPDATE finishes.
Is there any way to tell an INSERT or UPDATE to not lock the whole table so
that SELECT's can still be run on the table?Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
That's probably not the problem. While it's likey that SQL Server is
escalating row locks to page locks and even possibly a table lock if
enough rows are affected by the insert/update, while that transaction is
running, there are exclusive locks on those rows/pages/table.
When a page has an exclusive lock, no other readers or writers can touch
the page. They are blocked until the update/insert transaction
completes. That is, unless they use the read uncommited or NOLOCK table
hint on the tables in the Select statements. But since you are updating
information, is it ok for your users to read dirty data? I don't know.
That's up to you to determine. Dirty data arises when data is
updated/inserted in a transaction and another user reads the data using
read uncommitted isolation level. if the update transaction then rolls
back the changes the user that selected the data is staring at data that
doesn't exist any longer in the database.
The other option is to keep the insert/update transactions as short as
possible. Use batches if you need to to. That will keep the outstanding
locks to a minimim.
SQL Server 2005 offers a method for readers to see the original data
even if it's being updated by another user, but this option will likley
introduce overhead in the database because the data is temporarily
written to tempdb so it's available for other users to read. And it's a
database-wide setting.
For SQL 2000, it's either dirty reads or blocking. But short
transactions mitigate most of these problems.
David Gugick
Imceda Software
www.imceda.com|||Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
Another thing to consider is where the newly inserted rows are going.
For example, if you have a clustered index on an IDENTITY column, then
inserting new rows will have less of an effect on existing data because
the new most of the rows are inserted on new pages. Updated rows will
still cause problems.
What is your clustered index on? What data are you inserting? Can you
insert and update in different transactions? Can you also update and
insert in small amounts, say 1,000 rows at a time, rather than all at
once?
David Gugick
Imceda Software
www.imceda.com|||Thanks for your responses. I believe the table that is being updated does
have a Clustered index on the Primary key field, but it's not an Identity
field.
The data we are inserting is customer lead information.
I will ask about the last 2 questions since it is not me that is doing the
updates.
Thanks again.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:On30MJv4EHA.1596@.tk2msftngp13.phx.gbl...
> Mike W wrote:
>> We are inserting and updating large amounts of rows and find that
>> users are complaining that their SELECT queries on the same tables
>> are blocking (not finishing) until our INSERT or UPDATE finishes.
>> Is there any way to tell an INSERT or UPDATE to not lock the whole
>> table so that SELECT's can still be run on the table?
> Another thing to consider is where the newly inserted rows are going. For
> example, if you have a clustered index on an IDENTITY column, then
> inserting new rows will have less of an effect on existing data because
> the new most of the rows are inserted on new pages. Updated rows will
> still cause problems.
> What is your clustered index on? What data are you inserting? Can you
> insert and update in different transactions? Can you also update and
> insert in small amounts, say 1,000 rows at a time, rather than all at
> once?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Mike W wrote:
> Thanks for your responses. I believe the table that is being updated
> does have a Clustered index on the Primary key field, but it's not an
> Identity field.
> The data we are inserting is customer lead information.
> I will ask about the last 2 questions since it is not me that is
> doing the updates.
> Thanks again.
>
If the clustered index is on something other than a date or an identity,
you have a few potential issues:
1-The clustered index is probably causing page splits as new rows are
inserted. This is a very expensive operation because it requires a page
is split, a new one created, and rows moved around. While this operation
is going on, both pages are locked by SQL Server, adding to the locking
overhead of this operation.
2- The clustered index is likely requiring the disk heads move all
around the physical disk to locate the page to update. There is nothing
slower than random disk access for a database, further slowing down the
operation.
You can mitigate some of the problems here (not the physical disk heads
moving around) by leaving space in your clustered index using a fill
factor. However, this requires you rebuild the clustered index as needed
to maintain the free space before a bulk update of data. Using a fill
factor will leave a percentage of space available in each row and help
prevent page splitting. However, it will make the table a percentage
larger, but this may not be a problem if the free space is going to be
filled with new data anyway.
And the rebuilding of the clustered index will likely affect concurrency
for that table while the rebuild occurs.
If your clustered index is, in fact, on a column or set of columns that
are causing these problem, you may need to consider changing the index
to something that keeps all new data at the end of the table. The other
option is to temporarily remove the clustered index during the load and
rebuild when complete, but this will cause an automatic rebuild of all
non-clustered indexes as well and may be more expensive than what you
want.
A clustered index on an identity can prevent a host of problems and
speed the load process significantly in this case. It's worth a test to
see if it helps the load and if it affects other queries run on that
table.
David Gugick
Imceda Software
www.imceda.com
complaining that their SELECT queries on the same tables are blocking (not
finishing) until our INSERT or UPDATE finishes.
Is there any way to tell an INSERT or UPDATE to not lock the whole table so
that SELECT's can still be run on the table?Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
That's probably not the problem. While it's likey that SQL Server is
escalating row locks to page locks and even possibly a table lock if
enough rows are affected by the insert/update, while that transaction is
running, there are exclusive locks on those rows/pages/table.
When a page has an exclusive lock, no other readers or writers can touch
the page. They are blocked until the update/insert transaction
completes. That is, unless they use the read uncommited or NOLOCK table
hint on the tables in the Select statements. But since you are updating
information, is it ok for your users to read dirty data? I don't know.
That's up to you to determine. Dirty data arises when data is
updated/inserted in a transaction and another user reads the data using
read uncommitted isolation level. if the update transaction then rolls
back the changes the user that selected the data is staring at data that
doesn't exist any longer in the database.
The other option is to keep the insert/update transactions as short as
possible. Use batches if you need to to. That will keep the outstanding
locks to a minimim.
SQL Server 2005 offers a method for readers to see the original data
even if it's being updated by another user, but this option will likley
introduce overhead in the database because the data is temporarily
written to tempdb so it's available for other users to read. And it's a
database-wide setting.
For SQL 2000, it's either dirty reads or blocking. But short
transactions mitigate most of these problems.
David Gugick
Imceda Software
www.imceda.com|||Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
Another thing to consider is where the newly inserted rows are going.
For example, if you have a clustered index on an IDENTITY column, then
inserting new rows will have less of an effect on existing data because
the new most of the rows are inserted on new pages. Updated rows will
still cause problems.
What is your clustered index on? What data are you inserting? Can you
insert and update in different transactions? Can you also update and
insert in small amounts, say 1,000 rows at a time, rather than all at
once?
David Gugick
Imceda Software
www.imceda.com|||Thanks for your responses. I believe the table that is being updated does
have a Clustered index on the Primary key field, but it's not an Identity
field.
The data we are inserting is customer lead information.
I will ask about the last 2 questions since it is not me that is doing the
updates.
Thanks again.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:On30MJv4EHA.1596@.tk2msftngp13.phx.gbl...
> Mike W wrote:
>> We are inserting and updating large amounts of rows and find that
>> users are complaining that their SELECT queries on the same tables
>> are blocking (not finishing) until our INSERT or UPDATE finishes.
>> Is there any way to tell an INSERT or UPDATE to not lock the whole
>> table so that SELECT's can still be run on the table?
> Another thing to consider is where the newly inserted rows are going. For
> example, if you have a clustered index on an IDENTITY column, then
> inserting new rows will have less of an effect on existing data because
> the new most of the rows are inserted on new pages. Updated rows will
> still cause problems.
> What is your clustered index on? What data are you inserting? Can you
> insert and update in different transactions? Can you also update and
> insert in small amounts, say 1,000 rows at a time, rather than all at
> once?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Mike W wrote:
> Thanks for your responses. I believe the table that is being updated
> does have a Clustered index on the Primary key field, but it's not an
> Identity field.
> The data we are inserting is customer lead information.
> I will ask about the last 2 questions since it is not me that is
> doing the updates.
> Thanks again.
>
If the clustered index is on something other than a date or an identity,
you have a few potential issues:
1-The clustered index is probably causing page splits as new rows are
inserted. This is a very expensive operation because it requires a page
is split, a new one created, and rows moved around. While this operation
is going on, both pages are locked by SQL Server, adding to the locking
overhead of this operation.
2- The clustered index is likely requiring the disk heads move all
around the physical disk to locate the page to update. There is nothing
slower than random disk access for a database, further slowing down the
operation.
You can mitigate some of the problems here (not the physical disk heads
moving around) by leaving space in your clustered index using a fill
factor. However, this requires you rebuild the clustered index as needed
to maintain the free space before a bulk update of data. Using a fill
factor will leave a percentage of space available in each row and help
prevent page splitting. However, it will make the table a percentage
larger, but this may not be a problem if the free space is going to be
filled with new data anyway.
And the rebuilding of the clustered index will likely affect concurrency
for that table while the rebuild occurs.
If your clustered index is, in fact, on a column or set of columns that
are causing these problem, you may need to consider changing the index
to something that keeps all new data at the end of the table. The other
option is to temporarily remove the clustered index during the load and
rebuild when complete, but this will cause an automatic rebuild of all
non-clustered indexes as well and may be more expensive than what you
want.
A clustered index on an identity can prevent a host of problems and
speed the load process significantly in this case. It's worth a test to
see if it helps the load and if it affects other queries run on that
table.
David Gugick
Imceda Software
www.imceda.com
Inserting/Updating and locking
We are inserting and updating large amounts of rows and find that users are
complaining that their SELECT queries on the same tables are blocking (not
finishing) until our INSERT or UPDATE finishes.
Is there any way to tell an INSERT or UPDATE to not lock the whole table so
that SELECT's can still be run on the table?
Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
That's probably not the problem. While it's likey that SQL Server is
escalating row locks to page locks and even possibly a table lock if
enough rows are affected by the insert/update, while that transaction is
running, there are exclusive locks on those rows/pages/table.
When a page has an exclusive lock, no other readers or writers can touch
the page. They are blocked until the update/insert transaction
completes. That is, unless they use the read uncommited or NOLOCK table
hint on the tables in the Select statements. But since you are updating
information, is it ok for your users to read dirty data? I don't know.
That's up to you to determine. Dirty data arises when data is
updated/inserted in a transaction and another user reads the data using
read uncommitted isolation level. if the update transaction then rolls
back the changes the user that selected the data is staring at data that
doesn't exist any longer in the database.
The other option is to keep the insert/update transactions as short as
possible. Use batches if you need to to. That will keep the outstanding
locks to a minimim.
SQL Server 2005 offers a method for readers to see the original data
even if it's being updated by another user, but this option will likley
introduce overhead in the database because the data is temporarily
written to tempdb so it's available for other users to read. And it's a
database-wide setting.
For SQL 2000, it's either dirty reads or blocking. But short
transactions mitigate most of these problems.
David Gugick
Imceda Software
www.imceda.com
|||Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
Another thing to consider is where the newly inserted rows are going.
For example, if you have a clustered index on an IDENTITY column, then
inserting new rows will have less of an effect on existing data because
the new most of the rows are inserted on new pages. Updated rows will
still cause problems.
What is your clustered index on? What data are you inserting? Can you
insert and update in different transactions? Can you also update and
insert in small amounts, say 1,000 rows at a time, rather than all at
once?
David Gugick
Imceda Software
www.imceda.com
|||Thanks for your responses. I believe the table that is being updated does
have a Clustered index on the Primary key field, but it's not an Identity
field.
The data we are inserting is customer lead information.
I will ask about the last 2 questions since it is not me that is doing the
updates.
Thanks again.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:On30MJv4EHA.1596@.tk2msftngp13.phx.gbl...
> Mike W wrote:
> Another thing to consider is where the newly inserted rows are going. For
> example, if you have a clustered index on an IDENTITY column, then
> inserting new rows will have less of an effect on existing data because
> the new most of the rows are inserted on new pages. Updated rows will
> still cause problems.
> What is your clustered index on? What data are you inserting? Can you
> insert and update in different transactions? Can you also update and
> insert in small amounts, say 1,000 rows at a time, rather than all at
> once?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Mike W wrote:
> Thanks for your responses. I believe the table that is being updated
> does have a Clustered index on the Primary key field, but it's not an
> Identity field.
> The data we are inserting is customer lead information.
> I will ask about the last 2 questions since it is not me that is
> doing the updates.
> Thanks again.
>
If the clustered index is on something other than a date or an identity,
you have a few potential issues:
1-The clustered index is probably causing page splits as new rows are
inserted. This is a very expensive operation because it requires a page
is split, a new one created, and rows moved around. While this operation
is going on, both pages are locked by SQL Server, adding to the locking
overhead of this operation.
2- The clustered index is likely requiring the disk heads move all
around the physical disk to locate the page to update. There is nothing
slower than random disk access for a database, further slowing down the
operation.
You can mitigate some of the problems here (not the physical disk heads
moving around) by leaving space in your clustered index using a fill
factor. However, this requires you rebuild the clustered index as needed
to maintain the free space before a bulk update of data. Using a fill
factor will leave a percentage of space available in each row and help
prevent page splitting. However, it will make the table a percentage
larger, but this may not be a problem if the free space is going to be
filled with new data anyway.
And the rebuilding of the clustered index will likely affect concurrency
for that table while the rebuild occurs.
If your clustered index is, in fact, on a column or set of columns that
are causing these problem, you may need to consider changing the index
to something that keeps all new data at the end of the table. The other
option is to temporarily remove the clustered index during the load and
rebuild when complete, but this will cause an automatic rebuild of all
non-clustered indexes as well and may be more expensive than what you
want.
A clustered index on an identity can prevent a host of problems and
speed the load process significantly in this case. It's worth a test to
see if it helps the load and if it affects other queries run on that
table.
David Gugick
Imceda Software
www.imceda.com
complaining that their SELECT queries on the same tables are blocking (not
finishing) until our INSERT or UPDATE finishes.
Is there any way to tell an INSERT or UPDATE to not lock the whole table so
that SELECT's can still be run on the table?
Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
That's probably not the problem. While it's likey that SQL Server is
escalating row locks to page locks and even possibly a table lock if
enough rows are affected by the insert/update, while that transaction is
running, there are exclusive locks on those rows/pages/table.
When a page has an exclusive lock, no other readers or writers can touch
the page. They are blocked until the update/insert transaction
completes. That is, unless they use the read uncommited or NOLOCK table
hint on the tables in the Select statements. But since you are updating
information, is it ok for your users to read dirty data? I don't know.
That's up to you to determine. Dirty data arises when data is
updated/inserted in a transaction and another user reads the data using
read uncommitted isolation level. if the update transaction then rolls
back the changes the user that selected the data is staring at data that
doesn't exist any longer in the database.
The other option is to keep the insert/update transactions as short as
possible. Use batches if you need to to. That will keep the outstanding
locks to a minimim.
SQL Server 2005 offers a method for readers to see the original data
even if it's being updated by another user, but this option will likley
introduce overhead in the database because the data is temporarily
written to tempdb so it's available for other users to read. And it's a
database-wide setting.
For SQL 2000, it's either dirty reads or blocking. But short
transactions mitigate most of these problems.
David Gugick
Imceda Software
www.imceda.com
|||Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
Another thing to consider is where the newly inserted rows are going.
For example, if you have a clustered index on an IDENTITY column, then
inserting new rows will have less of an effect on existing data because
the new most of the rows are inserted on new pages. Updated rows will
still cause problems.
What is your clustered index on? What data are you inserting? Can you
insert and update in different transactions? Can you also update and
insert in small amounts, say 1,000 rows at a time, rather than all at
once?
David Gugick
Imceda Software
www.imceda.com
|||Thanks for your responses. I believe the table that is being updated does
have a Clustered index on the Primary key field, but it's not an Identity
field.
The data we are inserting is customer lead information.
I will ask about the last 2 questions since it is not me that is doing the
updates.
Thanks again.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:On30MJv4EHA.1596@.tk2msftngp13.phx.gbl...
> Mike W wrote:
> Another thing to consider is where the newly inserted rows are going. For
> example, if you have a clustered index on an IDENTITY column, then
> inserting new rows will have less of an effect on existing data because
> the new most of the rows are inserted on new pages. Updated rows will
> still cause problems.
> What is your clustered index on? What data are you inserting? Can you
> insert and update in different transactions? Can you also update and
> insert in small amounts, say 1,000 rows at a time, rather than all at
> once?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Mike W wrote:
> Thanks for your responses. I believe the table that is being updated
> does have a Clustered index on the Primary key field, but it's not an
> Identity field.
> The data we are inserting is customer lead information.
> I will ask about the last 2 questions since it is not me that is
> doing the updates.
> Thanks again.
>
If the clustered index is on something other than a date or an identity,
you have a few potential issues:
1-The clustered index is probably causing page splits as new rows are
inserted. This is a very expensive operation because it requires a page
is split, a new one created, and rows moved around. While this operation
is going on, both pages are locked by SQL Server, adding to the locking
overhead of this operation.
2- The clustered index is likely requiring the disk heads move all
around the physical disk to locate the page to update. There is nothing
slower than random disk access for a database, further slowing down the
operation.
You can mitigate some of the problems here (not the physical disk heads
moving around) by leaving space in your clustered index using a fill
factor. However, this requires you rebuild the clustered index as needed
to maintain the free space before a bulk update of data. Using a fill
factor will leave a percentage of space available in each row and help
prevent page splitting. However, it will make the table a percentage
larger, but this may not be a problem if the free space is going to be
filled with new data anyway.
And the rebuilding of the clustered index will likely affect concurrency
for that table while the rebuild occurs.
If your clustered index is, in fact, on a column or set of columns that
are causing these problem, you may need to consider changing the index
to something that keeps all new data at the end of the table. The other
option is to temporarily remove the clustered index during the load and
rebuild when complete, but this will cause an automatic rebuild of all
non-clustered indexes as well and may be more expensive than what you
want.
A clustered index on an identity can prevent a host of problems and
speed the load process significantly in this case. It's worth a test to
see if it helps the load and if it affects other queries run on that
table.
David Gugick
Imceda Software
www.imceda.com
Friday, February 24, 2012
Inserting Serial No Column in result of a query
Hello Friends
My problem is
Suppose Query is - Select * from tbl_Employee
TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST
I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this.
Serial Name Post
1 XYZ QER
2 SDF OPO
3 WER IPO
If any body knows please post the solution its urgent.
Where is the serial number supposed to come from?Jason
|||http://www.aspfaq.com/show.asp?id=2427
|||
Serial Number is supposed to be generated through query only.
|||Using the #temp table option described in the article will achieve that for you.
CREATE TABLE
#temp
(
Rank INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
firstName VARCHAR(50),
lastName VARCHAR(50)
)
INSERT INTO
#temp
(
firstName,
lastName
)
SELECT
firstName,
lastName
FROM
people
ORDER BY
lastName,
firstName
SELECT
*
FROM
#temp
ORDER BY
Rank
DROP TABLE #temp
|||I take it no one bothered reading the link I posted..?
![Confused [8-)]](http://pics.10026.com/?src=/emoticons/emotion-43.gif)
|||Yes I did. I just recommended the #temp table approach in it. Thething I didn't like about the way it was done in the article, however,is that it does not create the #temp table first. For performancereasons any #temp table should be explicitly created, not created by aSELECT INTO.
|||What performance reasons?
That may have been true in previous versions of SQL Server (some lockswere placed on tempdb during temp table creation), but it's not anissue in SQL Server 2000.
|||There are a few reasons to my understanding:
the SELECT INTO method will always require a stored procedure recompilation; the explicit CREATE TABLE will not necessarily
|||Regarding recompilation, I don't think SELECT INTO necessarily causes it. I see no difference in behavior between thefollowing three sprocs, monitoring the SQL Compilations/sec and SQLRe-Compilations/sec counters and tracing the SP:Recompile event (on anotherwise dead server):
create proc xxx
as
select 1 as somecol
into #blah
GO
create proc yyy
as
create table #blah(somecol int)
insert #blah values (1)
GO
create proc zzz
as
declare @.blah table(somecol int)
insert @.blah values (1)
GO
I ran each of these numerous times and saw no recompiles with any ofthem. I'm not aware of whether or not SELECT INTO is more likelyto recompile in certain cases, but merely using the syntax does notcause re-compilation.
|||Yes, I meant log shipping. And since my use of SELECT INTO oncecaused our DBA to have to work through the night I have stayed far awayfrom it. The recovery model on the database might very well havechanged since then.
Thanks for your insight. Hopefully the original poster now has what they need. :-)
Inserting row and scope_identity()
With a query such as,
SET NoCount ON
INSERT INTO ...
VALUES ...
SELECT scope_identity()
SET NoCount OFF
, can I trust that the id I get back is for the row that I just inserted, or
can it be incorrect because of other "simultaneous" inserts? I need it for
an ASP page.
Thanks,
Marcus[posted and mailed, vnligen svara i nys]
Marcus (lumbus@.ludd.luth.se) writes:
> With a query such as,
> SET NoCount ON
> INSERT INTO ...
> VALUES ...
> SELECT scope_identity()
> SET NoCount OFF
> , can I trust that the id I get back is for the row that I just
> inserted, or can it be incorrect because of other "simultaneous"
> inserts? I need it for an ASP page.
800 grader, du kan lita p mej, du kan lita p mej.
Yes, you can trust it. scope_identity is local to your process - in
fact local to the scope (procedure, batch etc) you are in.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Tack s mycket - Thank you. ;)
Marcus
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94DC29958E0AYazorman@.127.0.0.1...
> [posted and mailed, vnligen svara i nys]
> Marcus (lumbus@.ludd.luth.se) writes:
> > With a query such as,
> > SET NoCount ON
> > INSERT INTO ...
> > VALUES ...
> > SELECT scope_identity()
> > SET NoCount OFF
> > , can I trust that the id I get back is for the row that I just
> > inserted, or can it be incorrect because of other "simultaneous"
> > inserts? I need it for an ASP page.
> 800 grader, du kan lita p mej, du kan lita p mej.
> Yes, you can trust it. scope_identity is local to your process - in
> fact local to the scope (procedure, batch etc) you are in.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Tack s mycket - Thank you. ;)
Marcus
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94DC29958E0AYazorman@.127.0.0.1...
> [posted and mailed, vnligen svara i nys]
> Marcus (lumbus@.ludd.luth.se) writes:
> > With a query such as,
> > SET NoCount ON
> > INSERT INTO ...
> > VALUES ...
> > SELECT scope_identity()
> > SET NoCount OFF
> > , can I trust that the id I get back is for the row that I just
> > inserted, or can it be incorrect because of other "simultaneous"
> > inserts? I need it for an ASP page.
> 800 grader, du kan lita p mej, du kan lita p mej.
> Yes, you can trust it. scope_identity is local to your process - in
> fact local to the scope (procedure, batch etc) you are in.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
Inserting row and scope_identity()
With a query such as,
SET NoCount ON
INSERT INTO ...
VALUES ...
SELECT scope_identity()
SET NoCount OFF
, can I trust that the id I get back is for the row that I just inserted, or
can it be incorrect because of other "simultaneous" inserts? I need it for
an ASP page.
Thanks,
Marcus[posted and mailed, vnligen svara i nys]
Marcus (lumbus@.ludd.luth.se) writes:
> With a query such as,
> SET NoCount ON
> INSERT INTO ...
> VALUES ...
> SELECT scope_identity()
> SET NoCount OFF
> , can I trust that the id I get back is for the row that I just
> inserted, or can it be incorrect because of other "simultaneous"
> inserts? I need it for an ASP page.
800 grader, du kan lita p mej, du kan lita p mej.
Yes, you can trust it. scope_identity is local to your process - in
fact local to the scope (procedure, batch etc) you are in.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, February 19, 2012
Inserting Record to db1.table from db2.table
Hello!
Can we do this in SQL2000?
Insert into <db1>.<tablename> (fld1,fld2...) Select fld1,fld2 from <db2>.<tablename>
Assuming that these two tables have the same fields but reside different database.
Thanks in advance
bernieinsert into db1.dbo.table select * from db2.dbo.table
Can we do this in SQL2000?
Insert into <db1>.<tablename> (fld1,fld2...) Select fld1,fld2 from <db2>.<tablename>
Assuming that these two tables have the same fields but reside different database.
Thanks in advance
bernieinsert into db1.dbo.table select * from db2.dbo.table
Subscribe to:
Posts (Atom)