Trying To Append A Field If An Insert Happens To Contain A Duplicate
Jan 9, 2004
I deliberately intend to add some duplicates to one of my tables. For eg
Job User IsAdmin
JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5
JobID 235User ID 2
JobID 235User ID 9
JobID 235User ID 10
JobID 235User ID 1
I know its bad practice to do such a thing but there is a genuine reason. What I need to do is to be able to have a SQL statement that appends true to the IsAdmin field whenever it encounters the next UserID thats happens to be a duplicate. Hence the above would look like:
Job User IsAdmin
JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5 True
JobID 235User ID 2
JobID 235User ID 9 True
JobID 235User ID 10
JobID 235User ID 1 True
Thanks
View 1 Replies
ADVERTISEMENT
Oct 2, 2007
Hello Everyone:
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
How do I fix this?
TIA
View 4 Replies
View Related
Aug 27, 2004
Hello,
How do I append data on an update?
I have a table with a field that is nVarchar(1000) and the initial insert is a few sentences. If I wanted to add to that row using an update statement and without starting at the end of the sentences, how would I write that?
Update table set fieldname = 'more data' where value = @variable
instead of
Update table set fieldname = 'initial data more data' where value = @variable
and the 'more data' appends to the initial data... hmmm
help please.
View 3 Replies
View Related
Jun 14, 2004
Hi all,
I have a table with fields name Days1, Days2, Days3 - I am trying to use a loop in conjunction with a counter to identify each of these fields - I can't quite get the correct syntax and it is driving me crazy!!!
Here's the proc:
WHILE @Counter < 4
BEGIN
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot INNER JOIN
tbl_SurgerySlotDescription ON tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode AND
tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label LEFT OUTER JOIN
tbl_Appointment ON tbl_SurgerySlot.SurgerySlotKey = tbl_Appointment.SurgerySlotKey AND
tbl_SurgerySlot.ExtractDate = tbl_Appointment.ExtractDate
WHERE (tbl_SurgerySlot.ExtractDate = @ExtractDate) AND (tbl_Appointment.AppointmentKey IS NULL) AND
(tbl_SurgerySlot.StartTime > @DateFrom) AND (tbl_SurgerySlot.StartTime < @DateTo) AND (tbl_SurgerySlotDescription.IsBookable = 1)
SET @FieldName = 'Days' + CONVERT(VARCHAR(20),@Counter)
INSERT INTO tmp_Availability (@FieldName)
VALUES (@AppointmentsCount)
SET @DateTo = DATEADD(Day,1,@DateTo)
--Increment the loop counter
SET @Counter = @Counter + 1
When I run the above the follwoing message is displayed:
Server: Msg 208, Level 16, State 3, Line 36
Invalid object name 'tmp_Availability'.
The object IS valid so I'm lost....
View 8 Replies
View Related
Feb 27, 2006
Hi,
Can anybody tell me how to append data to ntext field?
Joydeep
View 9 Replies
View Related
Dec 10, 2007
I am trying to append some text to a full text field in SQL Server 2000. I tried this; but, it didn't work:
UPDATE DefendantEventPros SET EventComment=EventComment + ' This event was completed on "& Date() &".' WHERE EventNumber="& eventnumber &"
I get an error saying:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals add, type equals text.
How can I append some text to the end of a field? Thanks for any help!
View 14 Replies
View Related
Jan 22, 2008
my code:
Code Block
void AppendTableTest()
{
// Define ADOX object pointers, initialize pointers. These are in ADOX namespace.
_CatalogPtr m_pCatalog = NULL;
_TablePtr m_pTable = NULL;
try {
HRESULT hr = S_OK;
TESTHR(hr = m_pCatalog.CreateInstance(__uuidof(Catalog)));
// Open the catalog
m_pCatalog->PutActiveConnection("Provider='Microsoft.JET.OLEDB.4.0';data source='c:\new.mdb';");
//m_pCatalog->Tables->Delete("MyTable");
TESTHR(hr = m_pTable.CreateInstance(__uuidof(Table)));
m_pTable->PutName("MyTable");
m_pTable->Columns->Append("Column1",adInteger,0);
m_pTable->Columns->Append("Column2",(DataTypeEnum)21,8);
m_pTable->Columns->Append("colBinary",adBinary,8);
//m_pTable->Columns->Append("Column3",adVarWChar,50);
m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable));
printf("Table 'MyTable' is added.");
// Delete the table as this is a demonstration.
//m_pCatalog->Tables->Delete("MyTable");
printf("Table 'MyTable' is deleted.");
}
catch(_com_error &e) {
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
TRACE(" Source : %s description : %s ", (LPCSTR)bstrSource, (LPCSTR)bstrDescription);
}
but i got a invalid type error. I cannot understand.
why adox can not append adUnsignedBigInt field ?
Somebody can give me a answer? Thanks.
View 1 Replies
View Related
Jul 26, 2004
How can I append a string to the field value in the select list
SELECT Code + '-20' FROM tb.....
I want to the above to return 2000-20 for example.
How can I do this?
Mike B
View 1 Replies
View Related
Jul 23, 2005
Hello all,Number of rows in a table was increased significantly. Is there a way to seewhether data was inserted in to the table, or whether data was appended tothe table?Thanks in advance,Do.--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related
Jan 14, 2015
I am trying to come up with one statement that will INSERT a row if the primary key does not exist but APPEND if it does
Would the following statement be correct?
INSERT INTO Products (a,b,c) VALUES (2, Cotton Socks, $12) ON DUPLICATE KEY (b=Cotton Socks), (C=$12)
where a is the primary key?
View 3 Replies
View Related
Apr 21, 2007
Hi,
in table aspnet_membership of ASPNETDB.mdb file, i saw a kind of duplicate field like "LoweredEmail" containing the same value as field "Email" (also in table users field "LoweredUsername" ..).
I guess it's for containing email in lowercase, but why those fields?
Thanks
Tartuffe
View 2 Replies
View Related
Feb 29, 2000
I have a field called RegId. RegId is of datatype NVARCHAR (20).
RegId
-----
12322
2122111
23423
etc
etc
I want to run a query to find out if there are duplicate entries in this field.
Any ideas on how I can achieve this?
Thanks in advance,
Anthony
View 1 Replies
View Related
Jul 20, 2005
I want to add the content of a table into anotherI tried to copy all fields, except the primary key:INSERT INTO table2(field2, field3, field4, ...)SELECT field2, field3, field4, ...FROM anotherDB.dbo.table1gives the following error:Violation of UNIQUE KEY constraint...Cannot insert duplicate key...Why?I didn't ask him to copy the key column; Isn't the SQL Server supposedto know how to increment the key ?
View 2 Replies
View Related
Aug 20, 2007
Hi,
I have several row records in which
name email
--------- ---------
name1 abc@abc.com
name2 abc@abc.com
name3 abc@abc.com
name4 123@123.com
name6 123@123.com
How should I write the query which can return records with unique email address with any name attached?
The expected record set will be
name email
--------- ---------
<any> abc@abc.com
<any> 123@123.com
I was thinking to select the distinct of email and just stuck at here. I encountered this problem few times and still cannot solve it. Any help will great appreciated. Thanks in advance.
View 7 Replies
View Related
Jul 10, 2007
Hi everybody couldn't get through with saving my data on the table with two primary keys...
my table structure is this
pubidintUnchecked (primary key)
pubchar(1)Unchecked
publchar(1)Unchecked
pubcodechar(2)Unchecked (primary key)
a sample data is here
pubid pub publ pubcode
1 a b ab
1 b b bb
2 a b ab
2 b b bb
when i save this table modifying the pubid and pubcode as primary keys the following error displays...
Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.
what i understand is that on the primary key duplicates are not allowed how could i allow it?
thanks
View 7 Replies
View Related
Mar 15, 2004
I am looking for some help in pulling certain people out of a table
This is the basic setup of my table
PK ID NAME MEETING
1 11111 Joe CLASS98
2 22222 Jane CLASS98
3 33333 Bob CLASS98
4 11111 Joe CLASS04
5 22222 Jane CLASS04
6 44444 Sally CLASS04
What I am wanting to do is Select only the people who attended CLASS98 but didn't attend CLASS04
I could just remove the CLASS04 people by sorting if I could just return the rows that do not duplicate the ID and meeting is either CLASS04 or CLASS98
Either way would get me to my goal.
Any help is GREATLY appreciated.
Thanks
Jimmyjoe
View 11 Replies
View Related
Oct 17, 2005
Obviously, I'm a complete n00b at SQL.
I have a table in Access 2003 with about 6,000 records and there are about 20 records that have duplicate data in the first field (CompID).
I'm trying to make the first field my primary key, so I need to fix these duplicate entry.
I could export to Excel and fix the problem that way, but in the interest of learning SQL I want to figure out how to do it properly.
Thanks in advance for what is hopefully a simple answer.
View 1 Replies
View Related
Jan 3, 2007
Hi All !
Below is a snippet of MS SQL inside ASP that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get back green, red, blue, and yellow which is correct. On another similar search different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic Glass Sand. I want to remove the repeating elements returned in this field. I hope this makes sense.
PS I tried to use distinct but with no luck I want just one of each in the example below.
Thanks in Advance!
Scott
==============================
SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "
SQL1 = ""
SQL1 = SQL
SQL = SQL & "WHERE " & Where & " "
==================================
Here is a piece of the problem field, note repeating colors etc.
CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS
View 2 Replies
View Related
Mar 25, 2008
I have a table like
TradeID ActionID
58096 3663
58096 3664
78901 2235
78901 2236
I want to select the only the TradeID with the highest ActionID
I tried using
select distinct tradeid,actionid
From
cct
Where ActionID = (SELECT MAX(ActionID)
FROM cct1
WHERE cct1.TradeID = cct.TradeID)
group by tradeid,actionid
but the result is not correct
please help
In god we trust,everything else we test.
View 4 Replies
View Related
Oct 22, 2007
my table structure is
id field1
1 i am from india
2 i am from usa
3 i am from delhi
So i want to remove common data from field1 , means after run the query table should be like
id field1
1 india
2 usa
3 delhi
thanks in advance
saumitra tamrakar
View 8 Replies
View Related
Jan 3, 2007
Hi All,
Below is a snippet of MS SQL inside some VB that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense.
Below is the SQL and the results from the returned page.
PS I tried to use distinct but with no luck I want just one of each in the example below.
Thanks in Advance!
Scott
==============================
SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "
SQL1 = ""
SQL1 = SQL
SQL = SQL & "WHERE " & Where & " "
==================================
Here is a piece of the problem field, note repeating colors etc.
CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11
View 1 Replies
View Related
Jul 23, 2001
Hello,
Not sure how to do this. I think I need to use a Group By query. I have a "Products" table with the following fields:
Program#
Number
UPC
Item
Item#
Size
Dept
Everything is specific to the Program#. In other words, for every instance of a Program# there can be more than one Product, which is specified by the "Number" field. So: SELECT * FROM Product WHERE [Program#] = '12345' should return this:
12345 | 1 | 000012345678 | Cookies | 98765 | 12ct | Retail |
12345 | 2 | 000012345678 | Cake | 98765 | 12ct | Retail |
12345 | 3 | 000012345678 | Ice Cream | 98765 | 12ct | Retail |
However, some recordsets are returning like this:
12345 | 1 | 000012345678 | Cookies | 98765 | 12ct | Retail
12345 | 1 | 000012345678 | Cake | 98765 | 12ct | Retail
12345 | 2 | 000012345678 | Ice Cream | 98765 | 12ct | Retail
In which case I have to fix them (the "Number" field) with an update query sp they are numbered sequentially. Luckily, this doesn't happen very often.
Can someone help me with a query that will return only those records with duplicate values in the "Number" field? I am not sure how to construct this query which I will use as a stored procedure.
TIA,
Bruce Wexler
Programmer/Analyst
View 1 Replies
View Related
Jul 20, 2005
I have a .NET program that can connect to either an Access 97 database or anSQL Server 7 database. In the database I have two tables which have a fieldcalled ID. When I run a query like "SELECT A.*, B.* FROM A, B", the queryreturns those fields as "A.ID" and "B.ID" when connected to Access 97, butas "ID" and "ID" in SQL Server 7. Is there anyway to get SQL Server toprepend the table name to the field name in a case like this and not returnduplicate field names like that without having to specify aliases for thefields?- Don
View 1 Replies
View Related
Mar 27, 2006
I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names.
Can anyone make recommendations here that might help?
Oh, this is SQL200 in case that impacts what you might advise!!!!
View 2 Replies
View Related
May 28, 2015
In my SSIS package, i have a field test_method_number coming from OLE DB Source. I used Derived transformation to trim test_method_number: TRIM(test_method_number)
Now in the next Derived Transformation, i see duplicate test_method_number. How to get rid of this duplicate?
View 6 Replies
View Related
Jul 22, 2012
All, Using access 2003 frontend and sql server 2008 backend. I have an append query to insert 80000 from one table to an empty table. I get an error:
"Microsoft Office Access set 0 field(s) to Null due to a type conversion failure, and didn't add 36000 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations."
I know this error normally comes if there are dups in a field that doesnt allow.
View 1 Replies
View Related
Aug 16, 2007
I am using SQL Server 2000. I have a table (tbDupes) with duplicate records. I want to populate another table (tbUnique) with unique records.
My tbDupes has about 50 columns and three of them make up my primary key in tbUnique. I thought I could get unique records by using the following SQL statement.
Code Snippet
INSERT INTO tbUnique
SELECT * FROM tbDupes
I received this error: Cannot insert duplicate key
Is there a database setting that will allow me to insert the first record and prevent any duplicates from being inserted? I thought this was the default behavior and think that maybe the DBA changed it.
I know I can achieve this by creating a cursor with tbDupes and then something like "IF NOT EXISTS(...) THEN INSERT". However, I would prefer to avoid using a cursor.
View 6 Replies
View Related
Nov 14, 2007
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View 6 Replies
View Related
Aug 26, 2015
I've inherited a table of members that has the following structure:
CREATE TABLE [dbo].[dimMember](
[dimMemberId] [int] IDENTITY(1,1) NOT NULL,
[dimSourceSystemId] [int] NOT NULL CONSTRAINT [DF_dimMember_dimSourceSystemId] DEFAULT ((-1)),
[MemberCode] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](250) NOT NULL,
[LastName] [nvarchar](250) NOT NULL,
[Code] ....
Based on the way the data loads into the table there's a possibility of some records being near duplicates of each other. For example, we can have a member that has records that have the same first name, last name, SSN, but different addresses, membercodes, subscribercode etc... This can happen in pretty much any variation thereof.
What I want to do, is add a new column and use that to group the similar records under based on comparing on several columns. By this I mean, if a member matches 4 of the 7 values below with another member, we would group these:
First Name (1st 3 characters)
Last Name
DOB
CurrentAddress1
MemberCode
SSN
SubscriberCode
I'm at a loss of how to structure the SQL to update the new column in the table.
View 9 Replies
View Related
Oct 18, 2007
How can I prevent duplicate inserts or entries to a table?
Thank you.
Note: I am using SQL Server and coding ASP.net pages in VB.
View 7 Replies
View Related
Jan 9, 2004
We have an issue that our SQL Replication between our Application Server (holding 13 months of data) and our Reporting Server (holding 5-6 years of data).
Both systems are running on NT4, SQL Server 7 Ent SP3.
Our issue is that we used to run SQL Replication but then stopped using it, and used another method of replicating our data from the App Svr to Rpt Svr. However, we have now been told that we have to use SQL Replication.
We set it up as usual, but when the log reader starts up, it finds the 1st transaction and moves it into the distribution database, but it then fails when attempting to do the 2nd transaction with the error "Cannot insert duplicate key row in object MSrepl_transactions with unique index ucMSrepl_transactions.
We have checked the tables in the publisher db and they don't hold any duplicates, so I can only assume the key it is talking about is the key field in the distribution database, but this is a Binary field which I believe is populated by replication itself!! Is this correct?
If so, why is it trying to enter duplicates? And can we resync replication to fix it?
View 1 Replies
View Related
Feb 20, 2004
Hi, I need to insert rows into table1 from table2 and table3 but I don't want to insert repeated combinations of col2, col3. So, table1 has the primary key col2, col3.
This the table1:
create table table1(
col1 int not null,
col2 int not null,
col3 int not null,
constraint PK_table1 primary key (col2, col3)
)
This is my "insert" code:
INSERT INTO table1
SELECT table2.col1,table2.col2, table3.col3
FROM table2, table3
WHERE table2.col1 = table3.col1
Wich conditions shoud i add to this code?
Thanks.
fmilano.
View 2 Replies
View Related
Jun 27, 2006
The following quote is the exact error message that I get when I attempt to create a pull subscription. The STAppointment is one of a 15 databases that need to replicate inorder for the application SalonTouch to run properly.
What is causing this error? Is it user rights? Is it a problem with sql2005? Am I missing a step? Is there a problem with the database I am working with? What else?
TITLE: New Subscription Wizard
------------------------------
SQL Server could not create a subscription for Subscriber 'D6LHWQ91PORTSUN9'.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot insert duplicate key row in object 'dbo.MSmerge_replinfo' with unique index 'uc1MSmerge_replinfo'.
The subscription could not be created.
The subscription properties table 'MSsubscription_properties' does not exist in the current database.
Changed database context to 'STAppointment'.
The statement has been terminated. (Microsoft SQL Server, Error: 2601)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=2601&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
View 3 Replies
View Related
