i have to print Go statement in new line through isql command
my isql command is
isql /Q -SServername -dDatabasename -Usa -PPassword -h-1 -n -q "set nocount on Select ' grant execute on ' + name + ' to user go ' from sysobjects where type = 'U' and name not like 'dt%'" -o SFGRants.sql >> Deploy.log
and out is like
select grant execute tablename to user go
but i want in different format like
select grant execute tablename to user
go
like that
thanking in anticipationUSe char(13)
isql /Q -SServername -dDatabasename -Usa -PPassword -h-1 -n -q "set nocount on Select ' grant execute on ' + name + ' to user' + char(13)+' go ' from sysobjects where type = 'U' and name not like 'dt%'" -o SFGRants.sql >> Deploy.log
When u paste the output of the this commands output to query analyzer GO will print in separate linesql
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Primary key vs Clustered Index with respect to Replication.
Is the following statement is TRUE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
I want to double check the above statement is valid
I created two tables with primary and clustered key.
create table tab1
(
col1 int primary key
, col2 int
)
create table tab2
(
col1 int ,
col2 int
)
CREATE UNIQUE CLUSTERED INDEX tab2_ind
ON tab2 (col1)
sp_help tab1
sp_help tab2
Few Observations
NULLABLE
Primary Key NO
Clustered Index YES
Col Constraint.
Primary Key YES
Clustered Index NO
Index
Primary Key clustered, unique
Clustered Index clustered, unique, primary key
For the Primary Key, A Constraint is created with the following values
constraint_type PRIMARY KEY (clustered)
constraint_name PK__tab1__486E7AE7
delete_action (n/a)
update_action (n/a)
status_enabled (n/a)
status_for_replication (n/a)
constraint_keys col1
In the above status_for_replication column value is (N/A)
I think Primary Key does not have any impact on replication.
Since I dont have any constraint for the Clustered Index
I think Clustered Index does not have any impact on replication.
Therefore I think the following statement is FALSE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
Irrespective of Primary Key or Clustered Index both tables will
participate in replication. Is it correct
Please throw some light on this issue.
Thanks in Advance
Rajesh Peddireddyi dont think there is anything to do with replication.
but what i see is, this has something to do with Referential integrity.
column in parent table should be a primary key
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rajesh" wrote:
> Is the following statement is TRUE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> I want to double check the above statement is valid
> I created two tables with primary and clustered key.
> create table tab1
> (
> col1 int primary key
> , col2 int
> )
>
> create table tab2
> (
> col1 int ,
> col2 int
> )
>
> CREATE UNIQUE CLUSTERED INDEX tab2_ind
> ON tab2 (col1)
> sp_help tab1
> sp_help tab2
> Few Observations
> NULLABLE
> Primary Key NO
> Clustered Index YES
> Col Constraint.
> Primary Key YES
> Clustered Index NO
>
> Index
> Primary Key clustered, unique
> Clustered Index clustered, unique, primary key
> For the Primary Key, A Constraint is created with the following values
> constraint_type PRIMARY KEY (clustered)
> constraint_name PK__tab1__486E7AE7
> delete_action (n/a)
> update_action (n/a)
> status_enabled (n/a)
> status_for_replication (n/a)
> constraint_keys col1
> In the above status_for_replication column value is (N/A)
> I think Primary Key does not have any impact on replication.
> Since I dont have any constraint for the Clustered Index
> I think Clustered Index does not have any impact on replication.
>
> Therefore I think the following statement is FALSE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> Irrespective of Primary Key or Clustered Index both tables will
> participate in replication. Is it correct
> Please throw some light on this issue.
> Thanks in Advance
> Rajesh Peddireddy|||On Wed, 10 Aug 2005 11:49:03 -0700, Rajesh
<Rajesh@.discussions.microsoft.com> wrote:
>Is the following statement is TRUE.
>Primary Key will allow tables to participate in replication
Transactional, true.
For Merge, either the PK or another unique index are GUIDs.
>whereas Clustered Index will not allow tables to participate in replication.[/color
]
False. Replication doesn't care about cluster, just about PK and/or
GUID.
J.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
I want to double check the above statement is valid
I created two tables with primary and clustered key.
create table tab1
(
col1 int primary key
, col2 int
)
create table tab2
(
col1 int ,
col2 int
)
CREATE UNIQUE CLUSTERED INDEX tab2_ind
ON tab2 (col1)
sp_help tab1
sp_help tab2
Few Observations
NULLABLE
Primary Key NO
Clustered Index YES
Col Constraint.
Primary Key YES
Clustered Index NO
Index
Primary Key clustered, unique
Clustered Index clustered, unique, primary key
For the Primary Key, A Constraint is created with the following values
constraint_type PRIMARY KEY (clustered)
constraint_name PK__tab1__486E7AE7
delete_action (n/a)
update_action (n/a)
status_enabled (n/a)
status_for_replication (n/a)
constraint_keys col1
In the above status_for_replication column value is (N/A)
I think Primary Key does not have any impact on replication.
Since I dont have any constraint for the Clustered Index
I think Clustered Index does not have any impact on replication.
Therefore I think the following statement is FALSE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
Irrespective of Primary Key or Clustered Index both tables will
participate in replication. Is it correct
Please throw some light on this issue.
Thanks in Advance
Rajesh Peddireddyi dont think there is anything to do with replication.
but what i see is, this has something to do with Referential integrity.
column in parent table should be a primary key
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rajesh" wrote:
> Is the following statement is TRUE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> I want to double check the above statement is valid
> I created two tables with primary and clustered key.
> create table tab1
> (
> col1 int primary key
> , col2 int
> )
>
> create table tab2
> (
> col1 int ,
> col2 int
> )
>
> CREATE UNIQUE CLUSTERED INDEX tab2_ind
> ON tab2 (col1)
> sp_help tab1
> sp_help tab2
> Few Observations
> NULLABLE
> Primary Key NO
> Clustered Index YES
> Col Constraint.
> Primary Key YES
> Clustered Index NO
>
> Index
> Primary Key clustered, unique
> Clustered Index clustered, unique, primary key
> For the Primary Key, A Constraint is created with the following values
> constraint_type PRIMARY KEY (clustered)
> constraint_name PK__tab1__486E7AE7
> delete_action (n/a)
> update_action (n/a)
> status_enabled (n/a)
> status_for_replication (n/a)
> constraint_keys col1
> In the above status_for_replication column value is (N/A)
> I think Primary Key does not have any impact on replication.
> Since I dont have any constraint for the Clustered Index
> I think Clustered Index does not have any impact on replication.
>
> Therefore I think the following statement is FALSE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> Irrespective of Primary Key or Clustered Index both tables will
> participate in replication. Is it correct
> Please throw some light on this issue.
> Thanks in Advance
> Rajesh Peddireddy|||On Wed, 10 Aug 2005 11:49:03 -0700, Rajesh
<Rajesh@.discussions.microsoft.com> wrote:
>Is the following statement is TRUE.
>Primary Key will allow tables to participate in replication
Transactional, true.
For Merge, either the PK or another unique index are GUIDs.
>whereas Clustered Index will not allow tables to participate in replication.[/color
]
False. Replication doesn't care about cluster, just about PK and/or
GUID.
J.
Primary key violation on update
I am getting a primary key violation with an update.
My update statement is like
update e
set e.EID = neid.New_EID
from EmpTable e
inner join NewEID neid on neid.Tech_SSN = e.SSN
and neid.New_EID not in (Select EID from EmpTable)
The problem is that the final (Select EID from EmpTable) is not
refreshed after each update so I end up trying to set the EID to an
existing EID (sometimes there are more than one EID with the same
SSN).
Ideas? Thanks.
-JohnPlease post your DDL + INSERT statements of sample data that are causing the
problem.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John Baima" <john@.nospam.com> wrote in message
news:t7gqs15s3a72ddh2ipem4a80bq8osrng9r@.
4ax.com...
I am getting a primary key violation with an update.
My update statement is like
update e
set e.EID = neid.New_EID
from EmpTable e
inner join NewEID neid on neid.Tech_SSN = e.SSN
and neid.New_EID not in (Select EID from EmpTable)
The problem is that the final (Select EID from EmpTable) is not
refreshed after each update so I end up trying to set the EID to an
existing EID (sometimes there are more than one EID with the same
SSN).
Ideas? Thanks.
-John|||When you use JOIN with non-unique columns in a t-SQL UPDATE statement, you
could get that error. Please post your table structures & sample data along
with expected results for others to better understand and repro your
problem. For details, refer to: www.aspfaq.com/5006
Here is an untested attempt, based on the assumption that you have duplicate
EIDs for each SSN which in turn are duplicates as well:
UPDATE EmpTable
SET EID = ( SELECT MAX( neid.New_EID )
FROM NewEID neid
WHERE neid.Tech_SSN = EmpTable.SSN
AND NOT EXISTS ( SELECT *
FROM EmpTable emp
WHERE emp.EID = neid.New_EID ) )
WHERE EXISTS( SELECT *
FROM NewEID neid
WHERE neid.Tech_SSN = EmpTable.SSN ) ;
Do you have keys in all your tables? Keys are mandatory in all tables.
Without them, you are mostly left with unmanageable mess.
Anith|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote:
>Please post your DDL + INSERT statements of sample data that are causing th
e
>problem.
CREATE TABLE [EmpTable] (
[EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ssn] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_EmpTable_1] PRIMARY KEY CLUSTERED
(
[EID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [NewEID] (
[Tech_SSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Tech_EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
Insert ('12345', '111111111) into EmpTable
Insert ('12346', '111111111) into EmpTable
Insert ('111111111', '123456') into NewEID
The first record in EmpTable can up updated, but when it hits the
second, the update fails.
-John|||What are the desired results of the UPDATE, i.e. what should the rows in
EmpTable look like after the update?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John Baima" <john@.nospam.com> wrote in message
news:9qhqs19sf1e2qi3nqc29mq0ud9p8terd1a@.
4ax.com...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote:
>Please post your DDL + INSERT statements of sample data that are causing
>the
>problem.
CREATE TABLE [EmpTable] (
[EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ssn] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_EmpTable_1] PRIMARY KEY CLUSTERED
(
[EID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [NewEID] (
[Tech_SSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Tech_EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
Insert ('12345', '111111111) into EmpTable
Insert ('12346', '111111111) into EmpTable
Insert ('111111111', '123456') into NewEID
The first record in EmpTable can up updated, but when it hits the
second, the update fails.
-John|||"Anith Sen" <anith@.bizdatasolutions.com> wrote:
>Do you have keys in all your tables? Keys are mandatory in all tables.
>Without them, you are mostly left with unmanageable mess.
I posted the DDL just after this post. Keys are not mandatory but what
I inherited is certainly a mess.
-John|||>>Do you have keys in all your tables? Keys are mandatory in all tables.
> I posted the DDL just after this post. Keys are not mandatory
I think he meant theoretically, not technically. What is a table without a
key? In most cases, a mess, by definition.|||John Baima <john@.nospam.com> wrote:
>I am getting a primary key violation with an update.
As I've looked at these tables for awhile, I think that I can solve
the problem by filtering out duplicates. Is there a general way of
finding duplicate records and then deleting just one? In this case, I
really don't care which record is deleted.
Thanks!
-John|||>> Is there a general way of finding duplicate records and then deleting
KBA ( support.microsoft.com ): 139444
Anith|||On Tue, 17 Jan 2006 19:15:06 GMT, John Baima wrote:
>I am getting a primary key violation with an update.
>My update statement is like
>update e
> set e.EID = neid.New_EID
>from EmpTable e
> inner join NewEID neid on neid.Tech_SSN = e.SSN
>and neid.New_EID not in (Select EID from EmpTable)
>The problem is that the final (Select EID from EmpTable) is not
>refreshed after each update
(snip)
Hi John,
"Each update"? There's only one update in this code!
Remember that SQL is a set-based language. That extends to the
interpretation of data modification statements as well. Rows are not
updated one by one, as you seem to think. The entire results of the
UPDATE statement are first built in a temp holding place; after that,
all affected rows are changed, all at the same time.
The actual implementation doesn't have to follow this to the letter, but
the results should be the same as if it does.
Hugo Kornelis, SQL Server MVP
My update statement is like
update e
set e.EID = neid.New_EID
from EmpTable e
inner join NewEID neid on neid.Tech_SSN = e.SSN
and neid.New_EID not in (Select EID from EmpTable)
The problem is that the final (Select EID from EmpTable) is not
refreshed after each update so I end up trying to set the EID to an
existing EID (sometimes there are more than one EID with the same
SSN).
Ideas? Thanks.
-JohnPlease post your DDL + INSERT statements of sample data that are causing the
problem.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John Baima" <john@.nospam.com> wrote in message
news:t7gqs15s3a72ddh2ipem4a80bq8osrng9r@.
4ax.com...
I am getting a primary key violation with an update.
My update statement is like
update e
set e.EID = neid.New_EID
from EmpTable e
inner join NewEID neid on neid.Tech_SSN = e.SSN
and neid.New_EID not in (Select EID from EmpTable)
The problem is that the final (Select EID from EmpTable) is not
refreshed after each update so I end up trying to set the EID to an
existing EID (sometimes there are more than one EID with the same
SSN).
Ideas? Thanks.
-John|||When you use JOIN with non-unique columns in a t-SQL UPDATE statement, you
could get that error. Please post your table structures & sample data along
with expected results for others to better understand and repro your
problem. For details, refer to: www.aspfaq.com/5006
Here is an untested attempt, based on the assumption that you have duplicate
EIDs for each SSN which in turn are duplicates as well:
UPDATE EmpTable
SET EID = ( SELECT MAX( neid.New_EID )
FROM NewEID neid
WHERE neid.Tech_SSN = EmpTable.SSN
AND NOT EXISTS ( SELECT *
FROM EmpTable emp
WHERE emp.EID = neid.New_EID ) )
WHERE EXISTS( SELECT *
FROM NewEID neid
WHERE neid.Tech_SSN = EmpTable.SSN ) ;
Do you have keys in all your tables? Keys are mandatory in all tables.
Without them, you are mostly left with unmanageable mess.
Anith|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote:
>Please post your DDL + INSERT statements of sample data that are causing th
e
>problem.
CREATE TABLE [EmpTable] (
[EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ssn] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_EmpTable_1] PRIMARY KEY CLUSTERED
(
[EID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [NewEID] (
[Tech_SSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Tech_EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
Insert ('12345', '111111111) into EmpTable
Insert ('12346', '111111111) into EmpTable
Insert ('111111111', '123456') into NewEID
The first record in EmpTable can up updated, but when it hits the
second, the update fails.
-John|||What are the desired results of the UPDATE, i.e. what should the rows in
EmpTable look like after the update?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John Baima" <john@.nospam.com> wrote in message
news:9qhqs19sf1e2qi3nqc29mq0ud9p8terd1a@.
4ax.com...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote:
>Please post your DDL + INSERT statements of sample data that are causing
>the
>problem.
CREATE TABLE [EmpTable] (
[EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ssn] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_EmpTable_1] PRIMARY KEY CLUSTERED
(
[EID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [NewEID] (
[Tech_SSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Tech_EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
Insert ('12345', '111111111) into EmpTable
Insert ('12346', '111111111) into EmpTable
Insert ('111111111', '123456') into NewEID
The first record in EmpTable can up updated, but when it hits the
second, the update fails.
-John|||"Anith Sen" <anith@.bizdatasolutions.com> wrote:
>Do you have keys in all your tables? Keys are mandatory in all tables.
>Without them, you are mostly left with unmanageable mess.
I posted the DDL just after this post. Keys are not mandatory but what
I inherited is certainly a mess.
-John|||>>Do you have keys in all your tables? Keys are mandatory in all tables.
> I posted the DDL just after this post. Keys are not mandatory
I think he meant theoretically, not technically. What is a table without a
key? In most cases, a mess, by definition.|||John Baima <john@.nospam.com> wrote:
>I am getting a primary key violation with an update.
As I've looked at these tables for awhile, I think that I can solve
the problem by filtering out duplicates. Is there a general way of
finding duplicate records and then deleting just one? In this case, I
really don't care which record is deleted.
Thanks!
-John|||>> Is there a general way of finding duplicate records and then deleting
KBA ( support.microsoft.com ): 139444
Anith|||On Tue, 17 Jan 2006 19:15:06 GMT, John Baima wrote:
>I am getting a primary key violation with an update.
>My update statement is like
>update e
> set e.EID = neid.New_EID
>from EmpTable e
> inner join NewEID neid on neid.Tech_SSN = e.SSN
>and neid.New_EID not in (Select EID from EmpTable)
>The problem is that the final (Select EID from EmpTable) is not
>refreshed after each update
(snip)
Hi John,
"Each update"? There's only one update in this code!
Remember that SQL is a set-based language. That extends to the
interpretation of data modification statements as well. Rows are not
updated one by one, as you seem to think. The entire results of the
UPDATE statement are first built in a temp holding place; after that,
all affected rows are changed, all at the same time.
The actual implementation doesn't have to follow this to the letter, but
the results should be the same as if it does.
Hugo Kornelis, SQL Server MVP
Wednesday, March 21, 2012
primary key error with INSERT INTO
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
)
primary key error with INSERT INTO
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
)
primary key error with INSERT INTO
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
)
Subscribe to:
Posts (Atom)