Exclusive Row Locking in SQL SERVER 2000

Hi
I have a table with 1 million records. I Have 5 applications (identical) that read from that table and perform the actions. Now, I want an exclusive locks on the data selected. The sql statement is below.

select top 1000 * from Numbers
With (***I need this part *** Exclusive lock on the selected data, not allowing other apps to even read)
where IsSent = 1

How to achieve this. Please explain.

Regards,
Noorul

You can do something like below:

begin tran

select top 1000 * from Numbers
With (xlock, rowlock)
where IsSent = 1

And you will have to keep the transaction open for the locks to be effective. But this is not really a good way to use the database engine. Holding a transaction open with locks consumes lot of resources & with above locking you will essentially be blocking any process that tries to do anything with these rows. You will be better off having another column in the table that tracks the state of each row. You can update the selected row(s) from each application and work with that data offline. There are also other ways of doing the same.

|||Thanks Uma,

Ok, if that would consume more resources then how about splitting the table into 5 tables (with no repetition of course) and using those tables? If so, then could you provide me the code to how to split the tables plz. I am relatively new to SQL

Regards,
Noorul

excluding timestamp field in insert

I need to create a lot of simply queries that copy records from one table to
another. The queries are like the one below:
INSERT INTO tblEmp
SELECT tblEmp2003.*
FROM tblEmp2003
WHERE tblEmp2003.EmployeeId='001'
My problem is that all the tables contain a timestamp so the querys fail
because the timestamp is not uptable. Is there an exclusion verb that I can
use to exclude the timestamp field. I know I could simply list all the
fields and not include the timestamp column, but given all the queries I nee
d
to setup that would take forever.
Thanks"MarkT" <MarkT@.discussions.microsoft.com> wrote:

>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
>Thanks
No, there is no way around listing the columns. You might make the
job less difficult if you wrote a query to generate the text of the
INSERT commands though. You might get some ideas from the proc below,
which generates a SELECT for a table.
Roy
CREATE proc dbo.sp__select
(@.tblname varchar(50),
@.alias varchar(50) = NULL)
AS
select CASE WHEN C.colid = 1
THEN 'SELECT '
ELSE ' '
END +
CASE WHEN @.alias IS NOT NULL
THEN @.alias + '.'
ELSE ''
END +
C.name +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE CHAR(13) + CHAR(10) + ' FROM ' + O.name +
CASE WHEN @.alias IS NOT NULL THEN ' as ' + @.alias
ELSE ''
END
END
from sysobjects O, syscolumns C
where O.id = C.id
and O.name = @.tblname
order by C.id, C.colid
GO|||On Mon, 14 Nov 2005 19:31:01 -0800, "MarkT"
<MarkT@.discussions.microsoft.com> wrote:
>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
The query analyzer will generate the insert and select skeletons for
you. It is a pity SQLServer can't be smarter about the *.
J.

excluding records that are already present in a table...

Hello, I'm stucked with trying to identify records that are already present
in a table when queried from another table...
For example, if I have 2 tables (Table A and Table B), Table B has 3 fields
(a,b,c) out of which (a,b) are primary keys. I'm trying to insert records
into Table B from Table A if they are not already present... here's my sql
statement
insert into TableB
(a,b,c)
select a,b,c from TableA
where
TableA.a not in (select a from TableB)
and TableA.b not in (select b from TableB)
its returning me no records as inserted because
[select a,b,c from TableA
where
TableA.a not in (select a from TableB)
and TableA.b not in (select b from TableB)]
is not identifying the records that are not in. If I have only 1 primary key
then there's no problem.
Can anyone point out what's wrong with this statement? Thanks a lot in
advance.I think this will work:
select * from TableA
where not exists(select * from TableB
where TableB.a = TableA.a
and
TableB.b = TableA.b)
Bryce|||try
insert into TableB
(a,b,c)
select a,b,c from TableA
where
not exists (select 1 from TableB where TableB.a = TableA.a and TableB.b and
TableA.b)
"Nestor" wrote:

> Hello, I'm stucked with trying to identify records that are already presen
t
> in a table when queried from another table...
> For example, if I have 2 tables (Table A and Table B), Table B has 3 field
s
> (a,b,c) out of which (a,b) are primary keys. I'm trying to insert records
> into Table B from Table A if they are not already present... here's my sql
> statement
> insert into TableB
> (a,b,c)
> select a,b,c from TableA
> where
> TableA.a not in (select a from TableB)
> and TableA.b not in (select b from TableB)
> its returning me no records as inserted because
> [select a,b,c from TableA
> where
> TableA.a not in (select a from TableB)
> and TableA.b not in (select b from TableB)]
> is not identifying the records that are not in. If I have only 1 primary k
ey
> then there's no problem.
> Can anyone point out what's wrong with this statement? Thanks a lot in
> advance.
>
>

Excluding part of select statement if no data is returned in results

I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.

Here is my select statement:

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

The section below is the part that may or may not contain information:

SELECT (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft

So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!

TIA

BUMP... Somebody...|||

Okay, I tried adding the ISNULL to the statement, but I think the problem is because until a request has been put in there is nothing linking the employee table for the JOIN on the request table. When they put in a request it adds an entry to the request table for them. Up till that point, there will be nothing matching the two tables.

Here is my statement as it stands now. Is there anyway to get the results to show if the INNERJOIN isn't finding any results in the request table?

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

I know it seems I am just talking to myself at this point, but I would LOVE for someone to join my conversation. Thanks in advance for any help!!!

|||

Okay, I figured it out. Had to switch my query to a LEFT OUTER JOIN.

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeLEFTOUTER JOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

exclude rows

Table 1 is just a reference table. Users add values to table 2.

I need to select/exclude records from table1 where the id2 in table2 = 1.

How get the following results:

table 1
----
id
----
a
b
c
d
e
f
g

table 2
----
id / id2
----
b / 1
c / 1
d / 1
f / 1
c / 2
d / 2
a / 4
b / 4

need results
----
id
----
a
e
g

any suggestions?

thanksSELECT * FROM tblOne WHERE id_field IN (SELECT id_field FROM tblTwo WHERE other_id = 1)

Exclude Null Dates

I can't seem to figure out how to exclude records with a null in a date
field as part of a filter expression. I've tried all of the following:
!=<blank>
!= System.DBNull
!=DBNull
!=""
!=''Try using IsNothing, like:
=IIF(IsNothing(Fields!MyDateField.Value), 1, 0)
Kaisa M. Lindahl Lervik
"Cindy Mikeworth" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:utiawBl6GHA.4232@.TK2MSFTNGP02.phx.gbl...
>I can't seem to figure out how to exclude records with a null in a date
>field as part of a filter expression. I've tried all of the following:
> !=<blank>
> != System.DBNull
> !=DBNull
> !=""
> !=''
>|||Awesome! That did the trick. Thanks for the reference tips as well.

exclude blank records

I have created a script that returns every column and row that is queried
and some of the fields are blank. I want to only return the fields that are
populated. Below is the script. Any advice would be appreciated:
SELECT Relation.xparent_prov_id,
Relation.Parent,
Provider.DataSource_ID as prov_datasource_ID,
Provider.Provider_Name,
Provider.Provider_Type,
Provider.Degree_Type,
FEIProviderStatus.Status,
Provider.DataSource_ID,
Provider.City,
Provider.State,
evClinician_Profile.arabic
CASE
WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
ELSE ''
END AS Arabic,
CASE
WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
ELSE ''
END AS Chinese,
CASE
WHEN evClinician_Profile.french = 'Y' THEN 'French'
ELSE ''
END AS French,
CASE
WHEN evClinician_Profile.german = 'Y' THEN 'German'
ELSE ''
END AS German,
CASE
WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
ELSE ''
END AS Hebrew,
CASE
WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
ELSE ''
END AS Italian,
CASE
WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
ELSE ''
END AS Japanese,
CASE
WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
ELSE ''
END AS Russian,
CASE
WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
ELSE ''
END AS Spanish
INTO #TEMP
FROM Provider
INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
INNER JOIN evClinician_Profile ON Provider.Provider_Key =
evClinician_Profile.RelMan_Key
INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
FEIProviderStatus.ProviderID
SELECT * FROM #TEMP
DROP TABLE #TEMP
Message posted via http://www.webservertalk.comAs a general suggestion, you can use a WHERE clause in your query like:
WHERE '' NOT IN ( Arabic, Chinese, ... Spanish )
Anith|||What if one of the fields is blank and others are populated, do you want to
reject the whole row because of this?
If so, specify a WHERE clause as mentioned in an earlier reply.
Ilyan Mishiyev
IGM Consulting Corporation
Enterprise Web Solutions
www.igmcc.com
"Jay via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:c3177033e42f430f85a389ecc7a323f6@.SQ
webservertalk.com...
>I have created a script that returns every column and row that is queried
> and some of the fields are blank. I want to only return the fields that
> are
> populated. Below is the script. Any advice would be appreciated:
> SELECT Relation.xparent_prov_id,
> Relation.Parent,
> Provider.DataSource_ID as prov_datasource_ID,
> Provider.Provider_Name,
> Provider.Provider_Type,
> Provider.Degree_Type,
> FEIProviderStatus.Status,
> Provider.DataSource_ID,
> Provider.City,
> Provider.State,
> evClinician_Profile.arabic
> CASE
> WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
> ELSE ''
> END AS Arabic,
> CASE
> WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
> ELSE ''
> END AS Chinese,
> CASE
> WHEN evClinician_Profile.french = 'Y' THEN 'French'
> ELSE ''
> END AS French,
> CASE
> WHEN evClinician_Profile.german = 'Y' THEN 'German'
> ELSE ''
> END AS German,
> CASE
> WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
> ELSE ''
> END AS Hebrew,
> CASE
> WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
> ELSE ''
> END AS Italian,
> CASE
> WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
> ELSE ''
> END AS Japanese,
> CASE
> WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
> ELSE ''
> END AS Russian,
> CASE
> WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
> ELSE ''
> END AS Spanish
> INTO #TEMP
> FROM Provider
> INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
> INNER JOIN evClinician_Profile ON Provider.Provider_Key =
> evClinician_Profile.RelMan_Key
> INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
> FEIProviderStatus.ProviderID
> SELECT * FROM #TEMP
> DROP TABLE #TEMP
> --
> Message posted via http://www.webservertalk.com|||No, if the field is populated I want those to return those records.
Message posted via http://www.webservertalk.com|||That doesn't answer Ilyan's question. For example, given the following:
CREATE TABLE T1 (x INTEGER NOT NULL PRIMARY KEY, y CHAR(1) NULL, z
CHAR(1) NULL)
INSERT INTO T1 VALUES (1,'A',NULL)
INSERT INTO T1 VALUES (2,NULL,'B')
INSERT INTO T1 VALUES (3,'A','B')
You could exclude rows where either Y or Z is NULL:
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
AND z IS NOT NULL
or only where BOTH are NULL
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
OR z IS NOT NULL
You said you wanted to return "fields that are populated". Does that
mean you want to see a different number of columns depending on what
data exists? You'll have to do that either client-side, or with Dynamic
SQL or using a set of IF statements to cover all the various cases. A
static query always returns the same number of columns - it can't be
changed at runtime.
David Portas
SQL Server MVP
--|||I think I solved the issue by using:
WHERE LEN (fieldname) < 0
I did this code for each of the fields that I was querying in the sp. Is
this the most effeicient way to do this?
Message posted via http://www.webservertalk.com|||This achieves nothing except exclude the rows where fieldname is NULL
so you might as well write:
WHERE fieldname IS NOT NULL
Notice that NULL is not the same as an empty string. If you want to
exclude empty strings as well then you can do:
WHERE fieldname > ''
David Portas
SQL Server MVP
--|||You're right I got the same number of rows. Thanks for the feedback.
Message posted via http://www.webservertalk.com

Excel To DataBase (Duplicate Recoards)

for example i exported student table with sno as (PK)

how to handle the upload twise (som of the records already in the data base )

1) How to ignore the existing records in the database

2) How to modify the records which is already exist in the database

1) EXCEL DATA SOURCE(STUDENT.XLS)

2) DATA CONVERSION

3) DESTINATION DATABASE (SQL SERVER 2005 STUDENT TABLE)

this is i have please tell me how to ignore/update the exisitng records

koti

It may be a good idea to load the excel data into a staging table first. This would give you a lot more flexibility than trying to run sql statements on the excel file.

To ignore duplicate records, you could then just do a look up from the staging table to the destination table and only load the rows that fail the lookup.

To update the records is a bit more tricky, as you have to use the OLE DB Command and write the SQL statement yourself.

Hope this helps,

Chris

|||

from the 1st page of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1

EXCEL -SL SERVER 2005

Hi friends

student table contain the 3 columns : SNO SNAME MARKS

by using these controles i can able to upload the records (which is not exist in the database)

Excel Source 1 --student.xls

Data Conversion 1 --for destination datatype convertion

Fuzzy Lookupmap with database student table with sno inner join

Conditional Split (if simularity =1 then ignored the record) else inserted the database

OLE DB Destination save the new records

+++++++++++++++++++++++++++++++++++NOW ++++++++++++++++++++++++++++++++++++

in student.xls containt 7 records (1-7)

in student table(server) containt 7 records (1-7)

but marks is diffrent from excel sheet NOW

i want to update the marks ,field only (may be tomorow more than 1 column i have to update)

== SIMULTANIOUSLY how to insert a new record and EXISTING RECORD update only marks ========

REGRADS

KOTI

Basically you want to update the row if it already exists in the target table. If that is the case, then this thread will help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1