Checking Existence (repeating) And Output Count??

Nov 14, 2007

Hi All,

Not played with SQL for a while and am a bit rusty so please excuse me if I sound like a demented idiot! )

It's for a data migration from something wierd to SQL, I just need to be able to advise whether a table is worth migrating or someone should manually enter the data (i.e. if only six random fields are populated in a table then get a secretary to enter it).

I was hoping someone might have already done this and have a query I can edit?!

As far as I can make out the query needs to loop through every table, loop through every row, check each field for an existance of data and output something useful.

I've stared at this for about 3 hours now and tried several different things and none work.....and it's doing my nut! (

Help!!

Regs

View 2 Replies


ADVERTISEMENT

Checking For Row Existence

Nov 1, 2006

Can someone show me some C# code for detecting if a SQL row exists or not?  This seems like a very typical action and I cannot for the life of me find a tutorial online that explains this step.  In my code I'm either going to INSERT or UPDATE a record.  I tried sending a SELECT command through a ExecuteNonQuery, but only got -1 as a response.  Apparently ExecuteNonQuery does not work with SELECT.  I then saw that T-SQL has an EXISTS keyword, but I cannot see anyway to use that from within C#.So...can anyone share the typical code they use to identify if a row exists or not within a database.  I guess I was execting there to be some method available to do this sort of thing.

View 1 Replies View Related

Checking For Existence Of Files

May 14, 2008

So I want to check a directory for a particular extention of file. ex: *.txt, *.zip etc. (I'm using T-SQL by the way)

I tried this:

Code:

insert #a EXEC master..xp_fileexist 'G:wklyld_SQLRMAPOLLOADUntar*.txt'



this code works as long as I give it a specific file name, but if I try the *.txt or *.zip it wont work.

I've also been trying to run

Code:

EXEC MASTER.dbo.xp_cmdshell 'dir "G:wklyld_SQLRMAPOLLOADUntar*.*"



and then copy the results to a temp table and then run queries against the table. But I havent had much luck with that either.

Can anyone help? Thanks in advance.

View 4 Replies View Related

Checking For Existence Of A Temp Table Before Droping It

Dec 31, 2003

I'm familiar with how to check for the existence of a table before dropping it using the following command:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xxx]

How does one check for the existence of a temp table (using # syntax) before dropping it? I've tried various flavors of this command and none work. One flavor is

use tempdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#xxx]

CREATE TABLE #xxx (
NumID INTEGER IDENTITY(1,1),
Exhibitor_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL
)

Thanks.

Nick

View 2 Replies View Related

SQL 2012 :: Checking Existence Of Row Prior To Insert?

Aug 11, 2015

I have a situation where our stored procedure inserts records from table_1 to table_2 when they don't already exist (uses the EXIST statement) on that table. If table_1 contains multiple records that are the same, it appears after the 1st record has been inserted, it does not recognize it as being there when it checks the existence when attempting to insert record 2.

Here's an example of the script:

insert into table_2 (col1,col2,col3)
select col1,col2,col3
from table_1 t1
where not exists (select '1'
from table_2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2
and t1.col3 = t2.col3)

Data from Table_1 -- Assume that table_2 does not contain these records

col1 col2 col3
AA 11 A1
AA 11 A1
BB 22 B2

All 3 records would be inserted to table_2 in this example.

View 7 Replies View Related

Checking For Existence Of File Fails During Validation

Aug 29, 2007

OK. Here's my situation. I check for the existence of a dummy .txt file using a script. I send an e-mail if it does not exist and exit package. The .txt file only exists if another .xls file is present which I import. However, during the validation phase of the package, the package fails because the .xls file does not exist. Is there a way to bypass the validation step? The only solution I came up with is to have a two-step job. The first runs the file check step and sends the e-mail. The second attemps to run the package and fails. Not a very graceful exit.

View 3 Replies View Related

Point Of Information - Checking For Existence Of A Table In Another Database...

Feb 22, 2005

Hi all,

While cleaning up some code, I ran across the following statement in a stored proc - the purpose of which is to determine if a table exists in the local database: SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[XML_PRINTDATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1of course I removed it from the IF just for testing purposes, but my quandry is this...
Why chose that select (converting table name to object ID) rather than just doing THIS:SELECT * FROM dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1

I first thought it was to gain access to the "id" column value (and that may yet be the purpose of it), but the second code seems to work just peachy (I assume because the id column is present in the sysobjects table itself).

A follow-on question is this:
When I try to do the same check from another server (i.e.SELECT * FROM APRECEIVE1.DailyProd.dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1) it of course fails because OBJECTPROPERTY only looks for the id on the local database.

So, do I CARE if it is a user table? (I am reasonably sure it is, of course) and if so, is there a way to check on the remote server for the object type?

Bottom line is I Think I can just simplify things and check for the object name on the remote server, but just don't want to take away any "warm fuzzy feeling" generated by the original stored proc, if such a warm fuzzy is of any benefit (though don't get me started on the relativity of warm fuzzies, I wrote my Thesis on that ;) )

View 9 Replies View Related

Checking For Existence Of Parent Variable In Child Package

Oct 16, 2007

I have a parent package that calls a child package, when I run the parent package the child package picks up a variable value from the parent in a script task and runs fine, the problem I'm facing is when I run just the child package, the script task fails because it doesn't know about the parent variable. The dilemma I'm facing is in my child script task, if I add the parent variable to the ReadOnlyVariables list then the task fails because the parent variable doesn't exist when I just run the child. If I don't add the parent variable to the ReadOnlyVariabls list then if I try to use it then the task fails saying that the variable doesn't exist in the variables collection.

Is there a way to check for the existence of the parent variable, so when I just run the child package I don't get an error and I don't have to change my task every time I choose to run the child package only vs running the parent/child?

Thanks.

View 6 Replies View Related

Checking If A Table Is Empty Using Count

Sep 10, 2007

Hi,

I want to use something like select count(*) from table name = 0; to check whether a table is empty,

is this possible?

Thanks for any info.

Al.

View 4 Replies View Related

SQL CASE Output COUNT Into Two Columns

Aug 27, 2007

Hi Everyone,
 I am trying to do a query where I need to use as little C# as possible to build my gridview.  Basically I have a column called statusID.  There are about 15 options for this column but I only want to count certain ones.  I want to count when statusID = 3 and output that into a column called "fullUnitsUsed" but when the value is 4 or > 13 I want it to count and put the result into a column called "halfUnitsUsed".  I also want it to count based on the month.  To accomplish this I have used CASE and GROUP BY.  This has worked to some extent.  Currently if I COUNT for one month I get the correct number of fullUnitsUsed and halfUnitsUsed used for January.  Unfortunately the query returns 2 records for the month.  The first one has a value for fullUnitsUsed and halfUnitsUsed is NULL, the second record has fullUnitsUsed as NULL and halfUnitsUsed has the correct value.  I was hoping to output one record where both fullUnitsUsed and halfUnitsUsed have data.  My other problem is that if I test for the entire year (which is what this query is supposed to do) there are 5 records returned for each month, 3 of the records have fullUnitsUsed and halfUnitsUsed both as NULL and for the other 2, one has fullUnitsUsed with data and the other has halfUnitsUsed with data and the other column in both records is NULL.  The values for fullUnitsUsed and halfUnitsUsed are counted for the entire year as well, which I only want it to count based on each month.  Below is my query, any suggestions about how to approach this will be greatly appreciated.  If any clarification is needed please let me know.  Again if I could get this to work completely with SQL and not need to use any more C# than I have to it would be preferable.
SELECT People.lastName + ', ' + People.firstName AS fullName, Property.Name, NYSDDSORegion.Description, OpenDays.[month], OpenDays.maxOpenDays,CASE Attend.statusID
    WHEN 3 THEN COUNT(Attend.statusID)
    END AS fullUnitsUsed,CASE Attend.statusID
    WHEN 4 THEN COUNT(Attend.statusID)
    WHEN 14 THEN COUNT(Attend.statusID)
    WHEN 15 THEN COUNT(Attend.statusID)
    WHEN 16 THEN COUNT(Attend.statusID)
    WHEN 17 THEN COUNT(Attend.statusID)
    WHEN 18 THEN COUNT(Attend.statusID)
    WHEN 19 THEN COUNT(Attend.statusID)
    WHEN 20 THEN COUNT(Attend.statusID)
END AS halfUnitsUsed
FROM Attend INNER JOIN People ON Attend.personID = People.personID
                     INNER JOIN Property ON Attend.propertyID = Property.propertyID
                     INNER JOIN NYSDDSORegion ON Property.RegionID = NYSDDSORegion.RegionID
                    CROSS JOIN OpenDays
WHERE (Attend.attendDate BETWEEN '1/1/2007' AND '12/31/2007')
GROUP BY Property.Name, People.lastName, NYSDDSORegion.Description, People.firstName, OpenDays.monthID, OpenDays.[month], OpenDays.maxOpenDays, Attend.statusID
ORDER BY Property.Name, fullName, NYSDDSORegion.Description

View 2 Replies View Related

OUTPUT @count =no Of Records Returnd By Dynamic Where Clause

Aug 18, 2005

Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic.
Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.

How should the query be constructed, executed, and then the Count(*) value returned?

"WHERE <condition1> AND <condition 2> ;


"AND <condition 2> " may exist or may not exist in the query; it is dependant."

View 1 Replies View Related

Trying To Set Output Variable To Row Count Result Of SQL Execute Task

Nov 5, 2007

I am building this as an expression, but it is not working. I am trying to return the row count into a variable to use later in an update statement. What am I doing wrong? I am using a single row result set. I have one variable defined in my result set. I am receiving an error stating: Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". Any help is appreciated!

SELECT count(*) FROM hsi.itemdata a
JOIN hsi.keyitem105 b on a.itemnum = b.itemnum
JOIN hsi.keyitem106 c on a.itemnum = c.itemnum
JOIN hsi.keyitem108 d on a.itemnum = d.itemnum
WHERE a.itemtypegroupnum = 102
AND a.itemtypenum = 108
AND b.keyvaluechar = " + (DT_WSTR,2)@[User::Branch] + "
AND c.keyvaluechar = " + (DT_WSTR,2)@[User:epartment] + "
AND d.keyvaluesmall = " + (DT_WSTR,7)@[User::InvoiceNumber] + ")

View 6 Replies View Related

Loop Query To Update Output Depending Upon Count Of Distinct Entries

Jun 25, 2013

I need to update the result depending upon the count of distinct entries.

Example

ID Employee Region State
1 ABC AMEA MI
2 DEF AMEA MI
3 XYZ APAC TX

I want the result as below

ID Employee Region State
1 ABC AMEA MI-1
2 DEF AMEA MI-2
3 XYZ APAC TX

since the count of Region is 2

I tried using DECLARE @intFlag INT and stuff but wasn't able to get the solution.

View 2 Replies View Related

.NET 2.0 And .NET 1.1 Co-existence?

Oct 26, 2006

I downloaded Quest Software's freeware version ofComparison Suite. When I attempt to install, it tellsme that it requires .NET framework 1.1.4322. I alreadyhave .NET 2.0 installed (as part of the MS-SQL native client install)and I really don't want to mess that up.Is it *safe* to have the installer download/install .NET 1.1to coexist with 2.0? Is it likely to work?

View 1 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

Existence Of A Value In Table

Sep 24, 2007



Hi,

I have a table with the follwing values

Table Cats
{

CatID, date
-----------------------
Cat1, D1
Cat2, D2
Cat3, D3
}

I just wanted to check whether Cat1 exists in the table. Can anyone post the query
Thanks
~MOhan

View 6 Replies View Related

How To Know Existence Of The Data In The Databse

Jun 9, 2008

Hi Friends,I have one table in the databse,i.e userTable with one field userNameIn my form I have one Label ,textbox for entering the userName and One button for submit,So I am entering the data into the table(userTable) after clicking on the submit buttonBut my problem is before entering the data into the table I want to find wheather the given data exits or notif its not exists the data has to insert into the table otherwise its has to display the message"the user is already existed"for this I wrote the code like this in C# public Boolean isUserExists()    {        SqlCommand cmduserName = new SqlCommand("select count(*) from userTable where userName= " + txtuserName.Text + ")", conn);        SqlDataReader rdr = null;        rdr = cmduserName.ExecuteReader();                 conn.Open();               int count = 0;        while (rdr.NextResult())        {            count = rdr.GetInt32(1);        }        conn.Close();        if (count == 0)        {            return false;        }        else        {            return true;        }              protected void Button1_Click(object sender, EventArgs e)    {        if(isUserExists())            {                Response.Write("Opps ! User already Exists");                                       }                SqlCommand adapInsert = new SqlCommand("insert into userName values('" + txtuserName.Text + "')",conn);        conn.Open();        adapInsert.ExecuteNonQuery();        conn.Close();        Response.Write("data inserted");       }   is it write or not because I am not getting the output .please tell me any one where I have to change the code ThanksGeeta 

View 3 Replies View Related

Co-Existence Of 6.5 & 7.0 Client Software

Aug 20, 1999

We are currently running MS-SQL 6.5 and are getting new apps which require 7.0.
The new apps will be on their own server(s). My question is - Can a PC run both the
6.5 client and the 7.0 client (simultaneously) to access both 6.5 and 7.0?

View 4 Replies View Related

Broadcast Existence Of SQL Server

Feb 7, 2006

Hi All:

Does anyone know where to find the property that disables the broadcast of the sql server from the point where you do not see the server show up in the list when one goes to

"New SQL Server Registration" -> "..."

beside the "Server" field. The dialog itself is titled "Registered SQL Server Properties". Assume both SQL Servers are both behind the firewall.

View 5 Replies View Related

How To Add Identity To Existence Column

Jul 27, 2013

I have created a table and want to alter that table.

I want to add identity to my existence column.

How to add identity to my existence column?

View 5 Replies View Related

Testing For Cursor's Existence

Jul 23, 2005

How is it possible to test at the beginning of a stored procedure if acursor I want to declare already exists? (So I don't cause an error bydeclaring it).ThanksBruno

View 2 Replies View Related

Sum Is Repeating The Value!!!!!

Oct 23, 2007


I have a list that is grouped by Department inside it I have a table with two columns: name and hours. For example:

Department A

Name Houres
Mike 1

Department B

Name Houres
Mike 1
Steve 1

Department C

Name Houres
Mike 1
Steve 1

Outside the list I have another table that has a column for total hours for all departments:
Which in this case is 5. Everything work fine, but I have problem with displaying the total hours for all departments. In other words this will be my output
5
5
5
5
5
5
5
5
5
5
5
5

It will be repeated multiple times. I believe I have to add group to the table that contains the column (total hours for all departments.) but I can€™t do that b/c It is the total for all departments. Also I tried to hide duplicate wich works, but the problem with that when I export it to pdf it will print empty pages.

Any thoughts!

Thanks

View 1 Replies View Related

Detect Existence Of Datetime Fields

Jul 20, 2005

I'm looking for an efficient t-sql script to loop through all usertables in a db and determine/print the value of each row having adatetime field (including cases where there are multiple datetimefields per row)Help greatly appreciated..

View 2 Replies View Related

Transact SQL :: Check For Existence Before Inserting

Nov 3, 2015

I have a webpage where users can connect with other users by sending them a request. Sending the request just puts a row in a connect table that contains the users id, the targetusers id, a requesteddate, an accepted date and a disconnectdate (for the original requester to end the connection and a reject bit the the target can reject the request. Hoever I need to check to assure that the connect does nt already exist either as pending (requestdate is not null and accept date is null) or both dates are not null (connection already complete.).

ALTER PROCEDURE [dbo].[requestConnect]
-- Add the parameters for the stored procedure here
@requestor int,
@requested int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

[Code] ....

View 4 Replies View Related

How To Check The Existence Of A Column In A Table

Jun 23, 2007

Dear All,



I wanted to know how do I know or check that whether a column exists in a table. What function or method should I use to find out that a column has already exists in a table.



When I run a T-SQL script which i have written does not work. Here is how I have written:

IF Object_ID('ColumnA') IS NOT NULL
ALTER TABLE [dbo].[Table1] DROP COLUMN [ColumnA]
GO



I badly need some help.

View 9 Replies View Related

SQL Query To Detect Non-existence Of Certain Records

Dec 27, 2007

Here is my query:

SELECT dnn_Roles.RoleName, xyzUser.FirstName, xyzUser.LastName, xyzUser.Email, xyzRewardPoint.Points, xyzRewardPoint.RewardID
FROM xyzRewardPoint INNER JOIN
xyzUser ON xyzRewardPoint.UserID = xyzUser.UserId INNER JOIN
dnn_UserRoles INNER JOIN
dnn_Roles ON dnn_UserRoles.RoleID = dnn_Roles.RoleID ON xyzUser.ProviderId = dnn_UserRoles.UserID
WHERE (dnn_UserRoles.RoleID = 3) OR
(dnn_UserRoles.RoleID = 4) OR
(dnn_UserRoles.RoleID = 6)
ORDER BY dnn_UserRoles.RoleID

What I need is to extend this query to detect any users who exist in dnn_UserRoles.RoleID 3, 4 or 6 but do not have a RewardID value of '43' in the xyzRewardPoint table.

View 4 Replies View Related

How To Check For Table Existence Before Dropping It?

May 8, 2006

Apologies if this has been answered before, but the "Search" function doesn't seem to be working on these forums the last couple of days.

I'd just like to check if a table already exists before dropping it so that I can avoid an error if it doesn't exist. Doing a web search, I've tried along the lines of
"If (object_id(sensor_stream) is not null) drop table sensor_stream"
and
"If exists (select * from sensor_stream) drop table sensor_stream"

In both of these cases I get the error: "There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = if ]"

Sooooo... what is the standard way to check for existence of a table before dropping it? Someone help? This seems like it should be simple, but I can't figure it out.

Thanks in advance!
Dana

View 7 Replies View Related

TSQL Checks File Existence?

Sep 3, 2007

Hi,


Does TSQL provide methods to check if a file exists?


For example, a TSQL script will read data in a .dbf file into SQL Server. It will check if the file exists before read. How to do this check?


Thank you.

View 1 Replies View Related

Existence Of Index For Temp Table

Oct 4, 2007



Hi!
SS2005:

create table #tmp(a int)

create index idxt1 on #tmp(a)

insert into #tmp values (42)

select * from sys.indexes

where name like '%idx%'

order by name

But I can't see any rows about idxt1 :-(

If I say

create index idxt1 on #tmp(a)

againg I got error, because it exists.

How to check for existence using query?

B. D. Jensen

View 3 Replies View Related

Rows Repeating

Jun 4, 2008

This is actually not asp.net, but I'm hoping someone can help me. I have a report that is pulling from a couple of different tables. I am trying to add a meal choice to the report. Let's say they have the option of choosing chicken, beef or fish. They check the checkbox next to the choice. My report is pulling the infor, but it is putting 3 rows for each person not taking into consideration what choice they chose. The checkbox's all write to the same column - ses. Here is the code for the stored procedure:
 CREATE PROCEDURE [dbo].[spGetCourseEmailList1]( @Code1 char(9)) AS SELECT DISTINCT  dbo.[names].lname as LastName,  dbo.[names].fname as FirstName,dbo.[evser].ses as MealChoice,dbo.[evldg].paid as AmountPaid, dbo.[names].gp as PreferredAddress,  dbo.[names].mi as MiddleInitial,  dbo.[names].nname as NickName,  dbo.[names].xname as Suffix,  dbo.[names].hphone as HomePhone,  dbo.[names].email as EmailAddress,  dbo.[names].addr1 as HomeAddress1,  dbo.[names].addr2 as HomeAddress2,  dbo.[names].city as City,  dbo.[names].st as State,  dbo.[names].zip as ZipCode,  dbo.[firms].fname1 as FirmName1,  dbo.[firms].fname2 as FirmName2,  dbo.[firms].faddr1 as FirmAddress1,  dbo.[firms].faddr2 as FirmAddress2,  dbo.[firms].fcity as FirmCity,  dbo.[firms].fst as FirmState,  dbo.[firms].fzip as FirmZip,  dbo.[firms].fphone as FirmPhone,  dbo.[names].udflist1 FROM dbo.[firms] INNER JOIN dbo.[names] ON dbo.[firms].firm = dbo.[names].firm
INNER JOIN dbo.evldgON dbo.[names].id = dbo.[evldg].id
INNER JOIN  dbo.evregON dbo.[evldg].id = dbo.[evreg].id
Full OUTER  JOIN dbo.evserON dbo.[evreg].code1 = dbo.[evser].code1
WHERE dbo.[evldg].code1 = @Code1 AND  dbo.[evreg].code1 = @Code1 AND dbo.[evser].code1 = @Code1 AND dbo.[names].xwebflag <> 'Y'ORDER BY dbo.[names].lname, dbo.[names].fnameGO
The items in bold are what I added.
 

View 3 Replies View Related

Repeating Identity

Feb 11, 2005

When using Access - you had to be careful about the autoincrement feature. If you delete a record from a table ( autoincrement id=1000) -then compact/repair - then add a new record to that table - the autoincrement field will say 1000 - if that autoincrement value was used to uniquely identify something - it is no longer unique. This is all background for my question...

Does SQL 2000 do this also ? I must have read somewhere that it doesn't - since I have code that moves records around ( delete from one table - insert into other), but the other night, I was awakened by the thought that SQL2000 does the same as Access - i.e. repeating identity after compact/repair

Do I need to worry ?

View 5 Replies View Related

Repeating Record

Jun 7, 2004

How can I display same repeating record in a table?

Any help will be appreciated.

View 3 Replies View Related

Repeating Matches

May 18, 2007

Hi. I am new to SQL.I hope you veteran out there to help me solve the simple problem i met.

CREATE TABLE BASKET(
B# NUMBER(6) NOT NULL,
ITEM VARCHAR(6) NOT NULL,
CONSTRAINT BASKET_PKEY PRIMARY KEY(B#, ITEM) );

My statement

SELECT DISTINCT L1.ITEM, L2.ITEM,COUNT(L1.B#)
FROM BASKET L1 ,BASKET L2
WHERE L1.B# = L2.B#
AND L1.ITEM <> L2.ITEM
GROUP BY L1.ITEM,L2.ITEM;

the result is

ITEM ITEM COUNT(L1.B#)
------ ------ ------------
BEER MILK 5
BEER BREAD 4
BEER BUTTER 2
MILK BEER 5
MILK BREAD 6
MILK BUTTER 5
BREAD BEER 4
BREAD MILK 6
BREAD BUTTER 5
BUTTER BEER 2
BUTTER MILK 5
BUTTER BREAD 5

The problem is how to get rid those repeating group like (BEER,MILK) and (MILK,BREAD)?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved