I want to put some trace in the a UDF, so I put print in the function. IT gave error. Can anyone please explain why this happen. But this work with SPs.
Cheers
Shimit
Hi,this is just simply one of the limitations UDFs take, like not being able to execute something via EXEC or executing a SP in common, or using GETDATE() in functions...
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Hi,
When iam trying to print the report from the application i.e winforms application, at the first time it is displaying the Security waring(pop up message)
Do you want to install the software?
Name:Microssoft Sqlserver
Publisher:Microsoft
with Install and don't install buttons.
Once we install , from the next time printing it is not displaying this error message.
Whether it is of IE version Problem.
Thanks in advance.
Ian
Does anyone have an idea about what the message means and how I can fix it?
I had to create a proxy recently to have an SSIS package write a file to a network folder. I am by no means an expert on the subject, but here is what I had to do.
I had to first create a windows account with the right credentials. I then created a credential, a proxy for the credential, and of course set the "Run As" parameter for the step to the proxy. I made sure that the new account was added as a sysadmin in SQL Server, added the account to the "Users" group on the server, as well as to the Logon as batch security policy. When I still had problems with the package being able to read a file it was related to the credentials of the account, which I had the network admin fix. Everything now runs fine.
I apologize if I have stated the obvious to you, but just wanted to share with you all the steps I had to take to perform a task very similar to what you are trying to do. Of course, I had to also use an "absolute" network path and not a mapped drive letter.
Hope this helps.
I just tried to apply a set of primary keys to a table and after a
very long time (like an hour or more) i got this error message ...
Unable to create index 'PK_master_data'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
UNIQUE INDEX terminated because a duplicate key was found for index ID
1. Most significant primary key is '2050500'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
.... i am new to sqlserver ... is this saying to me that i have a
duplicate record? because when i query the recordset for duplicate
records i get none!
please help ASAP.
Thanks
Mogitcumming@.smorgonsteel.com.au (mogi) wrote in message news:<d1729549.0401272220.49ea5ee6@.posting.google.com>...
> Hi there,
> I just tried to apply a set of primary keys to a table and after a
> very long time (like an hour or more) i got this error message ...
> Unable to create index 'PK_master_data'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
> UNIQUE INDEX terminated because a duplicate key was found for index ID
> 1. Most significant primary key is '2050500'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
> constraint. See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ... i am new to sqlserver ... is this saying to me that i have a
> duplicate record? because when i query the recordset for duplicate
> records i get none!
> please help ASAP.
> Thanks
> Mogi
Yes, there are duplicates. How are you querying the recordset for
them? A query like this should find them:
select PrimaryKeyColumn, count(*)
from dbo.MyTable
group by PrimaryKeyColumn
having count(*) > 1
Simon
I have a database table where a primary key is defined. When I enter data that is the same as another table, it does not allow and throws an error which I do want. What happens is that a Server Error in Application error type is thrown with the following message:
Violation of PRIMARY KEY constraint 'PK_cs_sc'. Cannot insert duplicate key in object 'cs_sc'. The statement has been terminated.
How can I detect the error in my own asp.net page code so that it does not forward my users to the asp.net server error page? Heres my code below... how can I check for success or failure within this code?
Dim conSqlConnect As SqlConnection
Dim strInsert As String
Dim cmdInsert As Sqlcommand
conSqlConnect = New SqlConnection( "Server=localhost;uid=var;pwd=var;database=var" )
strInsert = "Insert cs_sc ( [name] ) Values ( @.name )"
cmdInsert = New SqlCommand ( strInsert, conSqlConnect )
conSqlConnect.Open()
cmdInsert.ExecuteNonQuery()
conSqlConnect.Close()erm, try...catch?|||give the table script, please|||What do you mean by table script?sql
Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?
INSERT INTO [destSchema].[destTable]
SELECT t2.*
FROM [srcSchema].[srcTable] t2
LEFT JOIN [destSchema].[destTable] t1
ON t2.[DateTime] = t1.[DateTime]
WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])
ORDER BY t1.[DateTime];
Is [destTable].[DateTime] the primary key?
Code Snippet
INSERT INTO [destSchema].[destTable]
SELECT
t2.*
FROM
[srcSchema].[srcTable] t2
LEFT OUTER JOIN
[destSchema].[destTable] t1
ON
t2.[DateTime] = t1.[DateTime]
WHERE
t1.[DateTime] IS NULL
|||
Yes
|||The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.
This query will give you a list of dupe dates.
Code Snippet
select t2.[DateTime]
from [srcSchema].[srcTable] t2
where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])
group by t2.[DateTime]
having count(*)>1
|||The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.
The statement has been terminated.
|||If the code lists dupes, you will have to clean your data in table t2 before you insert it into t1. The bottom line, you have to guarantee the data from t2 is unique before you commit inserting into t1 - this involves either deleting the duped data or only selecting a row for each name. Else, you will get the primary constraint violation. This is by design.
Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.
|||The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):
[DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL
Here is some sample data from the source table that demonstrates the problem (non-black lines indicates duplicated rows). Note that the DateTime value is duplicated but the other fields contain different values:
2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:22:00,502.90,502.90,502.90,502.90
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:26:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,498.40,498.40,498.40,498.40
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30
2005-11-28 18:39:00,498.10,498.10,498.00,498.00
Desired results:
2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30
Any and all help appreciated.
|||Here you go.
Code Snippet
create table #tmp([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)
go
create table #tmp2([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)
go
insert #tmp
select '2005-11-28 18:21:00',498.70,498.70,498.70,498.70
union all select '2005-11-28 18:22:00',498.50,498.50,498.50,498.50
union all select '2005-11-28 18:22:00',502.90,502.90,502.90,502.90
union all select '2005-11-28 18:23:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:26:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:26:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:30:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:31:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:32:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:33:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:34:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:36:00',502.50,502.50,502.50,502.50
union all select '2005-11-28 18:39:00',502.40,502.40,502.30,502.30
union all select '2005-11-28 18:39:00',498.10,498.10,498.00,498.00
go
;with cte
as
(select *,
row_number() over(partition by [datetime] order by [datetime] ) r
from #tmp
)
insert #tmp2
select [Datetime],Field1,Field2,Field3,Field4
from cte
where r=1
and not exists(select 1 from #tmp2 t2 where t2.[Datetime]=cte.[Datetime])
go
select * from #tmp2
go
drop table #tmp, #tmp2
With mycte
as
(SELECT myDatatime, f1, f2, f3, f4 FROM
(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum
FROM dupDateTimedata) t
WHERE RowNum=1)
SELECT * INTO dupDateTimedataRemoved
FROM mycte
|||limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.
2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00
|||Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.
We may need a little more clarification from rwbta to confirm your result.
My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.
|||
Since we partition by datetime, order by datetime again will force the engine to generate the rownumber based on the logical order of the rows which we then select only the first row. Essentially, it is equivalent to "select top 1 * from tb" - this is what was asked by the OP as the desired result.
|||This certainly turned out more complicated than I imagined. What additional information is needed?
Just as a summary, my original intention was to insert records into a new or existing table without including duplicate DateTime (primary key) values. If that's not possible, I would like to remove records in the source table which contain duplicate DateTime values.
Since the fields are not likely to contain exactly the same values in the duplicated records, DISTINCT won't work. Only the DateTime values are duplicated, inserting only the first occurrence of a duplicated DateTime would be acceptable. Or, alternatively, deleting subsequent duplications in the source table.
|||Below is what I have done to resolve this problem. Add a primary key ID to the source table to aid in identification of duplicate DateTime's. Then delete duplicates. After that I can insert into a new or existing table.
Add PK ID:
ALTER TABLE srcSchema.srcTable
ADD
DataID int NOT NULL IDENTITY(1, 1),
CONSTRAINT PK_srcTable PRIMARY KEY(DataID)
Delete Duplicates:
DELETE FROM
t1
FROM
srcSchema.srcTable t1
INNER JOIN
(
SELECT
MIN(DataID) AS DataID,
[DateTime]
FROM
srcSchema.srcTable
GROUP BY
[DateTime]
HAVING
COUNT(*) > 1
) t2
ON(
t1.[DateTime] = t2.[DateTime]
AND
t1.DataID <> t2.DataID
)
Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?
INSERT INTO [destSchema].[destTable]
SELECT t2.*
FROM [srcSchema].[srcTable] t2
LEFT JOIN [destSchema].[destTable] t1
ON t2.[DateTime] = t1.[DateTime]
WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])
ORDER BY t1.[DateTime];
Is [destTable].[DateTime] the primary key?
Code Snippet
INSERT INTO [destSchema].[destTable]
SELECT
t2.*
FROM
[srcSchema].[srcTable] t2
LEFT OUTER JOIN
[destSchema].[destTable] t1
ON
t2.[DateTime] = t1.[DateTime]
WHERE
t1.[DateTime] IS NULL
|||
Yes
|||The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.
This query will give you a list of dupe dates.
Code Snippet
select t2.[DateTime]
from [srcSchema].[srcTable] t2
where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])
group by t2.[DateTime]
having count(*)>1
|||The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.
The statement has been terminated.
|||If the code lists dupes, you will have to clean your data in table t2 before you insert it into t1. The bottom line, you have to guarantee the data from t2 is unique before you commit inserting into t1 - this involves either deleting the duped data or only selecting a row for each name. Else, you will get the primary constraint violation. This is by design.
Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.
|||The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):
[DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL
Here is some sample data from the source table that demonstrates the problem (non-black lines indicates duplicated rows). Note that the DateTime value is duplicated but the other fields contain different values:
2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:22:00,502.90,502.90,502.90,502.90
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:26:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,498.40,498.40,498.40,498.40
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30
2005-11-28 18:39:00,498.10,498.10,498.00,498.00
Desired results:
2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30
Any and all help appreciated.
|||Here you go.
Code Snippet
create table #tmp([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)
go
create table #tmp2([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)
go
insert #tmp
select '2005-11-28 18:21:00',498.70,498.70,498.70,498.70
union all select '2005-11-28 18:22:00',498.50,498.50,498.50,498.50
union all select '2005-11-28 18:22:00',502.90,502.90,502.90,502.90
union all select '2005-11-28 18:23:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:26:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:26:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:30:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:31:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:32:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:33:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:34:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:36:00',502.50,502.50,502.50,502.50
union all select '2005-11-28 18:39:00',502.40,502.40,502.30,502.30
union all select '2005-11-28 18:39:00',498.10,498.10,498.00,498.00
go
;with cte
as
(select *,
row_number() over(partition by [datetime] order by [datetime] ) r
from #tmp
)
insert #tmp2
select [Datetime],Field1,Field2,Field3,Field4
from cte
where r=1
and not exists(select 1 from #tmp2 t2 where t2.[Datetime]=cte.[Datetime])
go
select * from #tmp2
go
drop table #tmp, #tmp2
With mycte
as
(SELECT myDatatime, f1, f2, f3, f4 FROM
(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum
FROM dupDateTimedata) t
WHERE RowNum=1)
SELECT * INTO dupDateTimedataRemoved
FROM mycte
|||limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.
2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00
|||Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.
We may need a little more clarification from rwbta to confirm your result.
My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.
|||
Since we partition by datetime, order by datetime again will force the engine to generate the rownumber based on the logical order of the rows which we then select only the first row. Essentially, it is equivalent to "select top 1 * from tb" - this is what was asked by the OP as the desired result.
|||This certainly turned out more complicated than I imagined. What additional information is needed?
Just as a summary, my original intention was to insert records into a new or existing table without including duplicate DateTime (primary key) values. If that's not possible, I would like to remove records in the source table which contain duplicate DateTime values.
Since the fields are not likely to contain exactly the same values in the duplicated records, DISTINCT won't work. Only the DateTime values are duplicated, inserting only the first occurrence of a duplicated DateTime would be acceptable. Or, alternatively, deleting subsequent duplications in the source table.
|||Below is what I have done to resolve this problem. Add a primary key ID to the source table to aid in identification of duplicate DateTime's. Then delete duplicates. After that I can insert into a new or existing table.
Add PK ID:
ALTER TABLE srcSchema.srcTable
ADD
DataID int NOT NULL IDENTITY(1, 1),
CONSTRAINT PK_srcTable PRIMARY KEY(DataID)
Delete Duplicates:
DELETE FROM
t1
FROM
srcSchema.srcTable t1
INNER JOIN
(
SELECT
MIN(DataID) AS DataID,
[DateTime]
FROM
srcSchema.srcTable
GROUP BY
[DateTime]
HAVING
COUNT(*) > 1
) t2
ON(
t1.[DateTime] = t2.[DateTime]
AND
t1.DataID <> t2.DataID
)
Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?
INSERT INTO [destSchema].[destTable]
SELECT t2.*
FROM [srcSchema].[srcTable] t2
LEFT JOIN [destSchema].[destTable] t1
ON t2.[DateTime] = t1.[DateTime]
WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])
ORDER BY t1.[DateTime];
Is [destTable].[DateTime] the primary key?
Code Snippet
INSERT INTO [destSchema].[destTable]
SELECT
t2.*
FROM
[srcSchema].[srcTable] t2
LEFT OUTER JOIN
[destSchema].[destTable] t1
ON
t2.[DateTime] = t1.[DateTime]
WHERE
t1.[DateTime] IS NULL
|||
Yes
|||The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.
This query will give you a list of dupe dates.
Code Snippet
select t2.[DateTime]
from [srcSchema].[srcTable] t2
where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])
group by t2.[DateTime]
having count(*)>1
|||The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.
The statement has been terminated.
|||If the code lists dupes, you will have to clean your data in table t2 before you insert it into t1. The bottom line, you have to guarantee the data from t2 is unique before you commit inserting into t1 - this involves either deleting the duped data or only selecting a row for each name. Else, you will get the primary constraint violation. This is by design.
Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.
|||The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):
[DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL
Here is some sample data from the source table that demonstrates the problem (non-black lines indicates duplicated rows). Note that the DateTime value is duplicated but the other fields contain different values:
2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:22:00,502.90,502.90,502.90,502.90
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:26:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,498.40,498.40,498.40,498.40
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30
2005-11-28 18:39:00,498.10,498.10,498.00,498.00
Desired results:
2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30
Any and all help appreciated.
|||Here you go.
Code Snippet
create table #tmp([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)
go
create table #tmp2([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)
go
insert #tmp
select '2005-11-28 18:21:00',498.70,498.70,498.70,498.70
union all select '2005-11-28 18:22:00',498.50,498.50,498.50,498.50
union all select '2005-11-28 18:22:00',502.90,502.90,502.90,502.90
union all select '2005-11-28 18:23:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:26:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:26:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:30:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:31:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:32:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:33:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:34:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:36:00',502.50,502.50,502.50,502.50
union all select '2005-11-28 18:39:00',502.40,502.40,502.30,502.30
union all select '2005-11-28 18:39:00',498.10,498.10,498.00,498.00
go
;with cte
as
(select *,
row_number() over(partition by [datetime] order by [datetime] ) r
from #tmp
)
insert #tmp2
select [Datetime],Field1,Field2,Field3,Field4
from cte
where r=1
and not exists(select 1 from #tmp2 t2 where t2.[Datetime]=cte.[Datetime])
go
select * from #tmp2
go
drop table #tmp, #tmp2
With mycte
as
(SELECT myDatatime, f1, f2, f3, f4 FROM
(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum
FROM dupDateTimedata) t
WHERE RowNum=1)
SELECT * INTO dupDateTimedataRemoved
FROM mycte
|||limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.
2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00
|||Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.
We may need a little more clarification from rwbta to confirm your result.
My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.
|||
Since we partition by datetime, order by datetime again will force the engine to generate the rownumber based on the logical order of the rows which we then select only the first row. Essentially, it is equivalent to "select top 1 * from tb" - this is what was asked by the OP as the desired result.
|||This certainly turned out more complicated than I imagined. What additional information is needed?
Just as a summary, my original intention was to insert records into a new or existing table without including duplicate DateTime (primary key) values. If that's not possible, I would like to remove records in the source table which contain duplicate DateTime values.
Since the fields are not likely to contain exactly the same values in the duplicated records, DISTINCT won't work. Only the DateTime values are duplicated, inserting only the first occurrence of a duplicated DateTime would be acceptable. Or, alternatively, deleting subsequent duplications in the source table.
|||Below is what I have done to resolve this problem. Add a primary key ID to the source table to aid in identification of duplicate DateTime's. Then delete duplicates. After that I can insert into a new or existing table.
Add PK ID:
ALTER TABLE srcSchema.srcTable
ADD
DataID int NOT NULL IDENTITY(1, 1),
CONSTRAINT PK_srcTable PRIMARY KEY(DataID)
Delete Duplicates:
DELETE FROM
t1
FROM
srcSchema.srcTable t1
INNER JOIN
(
SELECT
MIN(DataID) AS DataID,
[DateTime]
FROM
srcSchema.srcTable
GROUP BY
[DateTime]
HAVING
COUNT(*) > 1
) t2
ON(
t1.[DateTime] = t2.[DateTime]
AND
t1.DataID <> t2.DataID
)
In the first one the columns are :
TABLE PROMOTION :
PROMOTION_ID; PROMOTION_DESC;
16a;-20% discount;
16A;-30% discount;
AbC;no discount;
aBc; -90% discount;
The second table, TD_PROMOTION, has the same structure, but the column PROMOTION_ID column has a primary key constraint.
When i want to insert data from PROMOTION to TD_PROMOTION, i've got a primary_key constraint error, because it seems that the database makes no difference between the codes 16a and 16A and between AbC and aBc.
Is there a way to fix this problem ?
Thank you in advance,
Eric.
You can specify if SQL Server should be case sensitive or not using collation. In this instance the column must have a case sensitive collation in order for you to be able to specify any type of unique constraint on it. For example, the first example will fail whereas the second will work, notice the CI and CS for case insensitive and sensitive.
CREATE TABLE test1 (
col1 varchar(20) COLLATE Latin1_General_CI_AS PRIMARY KEY
)
INSERT INTO test1 VALUES ('ASD')
INSERT INTO test1 VALUES ('asd')
CREATE TABLE test2 (
col1 varchar(20) COLLATE Latin1_General_CS_AS PRIMARY KEY
)
INSERT INTO test2 VALUES ('ASD')
INSERT INTO test2 VALUES ('asd')
DROP TABLE test1
DROP TABLE test2
Collation can be set at the column or database level. If set at database level then all character columns without a collation specified adopt the database collation.
There are many collations available to you, search Books Online for a list of these.
Nick
DBA http://www.comoni.co.uk
