Sql Public Role: rows

Showing posts with label rows. Show all posts

Friday, March 30, 2012

Puzzled by duplicates

Hello all!

Have a question:

Is there way in SQL to determine duplicate rows without using count(), aggregate functions, group by or select distinct?

I only have regular select, join and delete features.

Basically, what are all the possible ways to determine duplicates in data like this?

col1|col2
----
s1--j1
s1--j4
s1--j1
s1--j3
s1--j2

I greatly appreciate your response!
Thanks!You need to add a third column, a unique ID column. Then you can do this

select t1.* from t1
join t2 on t1.col1=t2.col2 and t1.col2=t2.col2 and t1.id != t2.id

Check out my brand new SQL tutorial at http://www.bitesizeinc.net/index.php/sql.html

-Chris
http://www.bitesizeinc.net/|||Is there way to do it without a unique ID? Not to be persistent, but my original goal was to do it only with those simple SQL statements.

I just wanted to make sure that I tried every way possible. Maybe there is a way to use cartesian product or whichever, but it's gotta be based on these simple statements.

If it can only be done using unique ID or aggregate functions, it's a good answer as well.

Let me know if I sound confusing. Great site btw, very original!.

Thanks for your devotion.|||Thank you so much for visitting my site...

I think that you're out of options here.

The best way is using GROUP BY.

Otherwise, you could select DISTINCT CONCAT(field1,' ',field2)

Or use the unique ID

I can't think of anyway else...

-Chrissql

putting error lines in new table or file

I'm importing large text files (12Gig). I know there are rows in the data
that will not parse correctly. What I'm trying to figure out is how to set
up the import and when there is an error parsing a row, that row either gets
saved to another table or text file and then continues to import the data.
This process would continue until the entire file is imported.Hi,
If you use some of the Tasks in the DTS packages you can configure "an
Error file".
If any rows are skipped they are placed in the the file. I'm pretty
sure that you can use this feature with the Text file import.
HTH
Barry

Wednesday, March 28, 2012

Putting Data From DataTable/DataSet into SQL Server Table?

Hello,

I created this DataTable, add rows of data to it, and then display the data on to a form via a repeater.

Dim ds As DataSet = New DataSet
Dim dtTableName As DataTable = New DataTable("dtTableName")
dtTableName.Columns.Add("Description")
dtTableName.Columns.Add("ItemNumber")
dtTableName.Columns.Add("Quantity")
dtTableName.Columns.Add("Price")
ds.Tables.Add(dtTableName)

What I need to do now is create a table in SQL Server and update the database with the data I've collect in my dataset. How do I bind and update this data to a sql server table?

Thanks!
James

Check out this walkthrough (it uses a DataGrid and DataSet):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbwlkWalkthroughUpdatingDataUsingDatabaseUpdateQueryInWebForms.asp

Put first 10 rows in a table and then group by each 25 rows in ano

Hi.
I would like to put the first 10 rows in a table and not have a page break.
Then in another table (which will be shown on another page) I would like to
show the 25 next rows, add a page break, show the next 25 rows, etc.
I could solve this by
System.Math.Ceiling (RowNumber (Nothing)/10)
or
Add a filter with top N = 10.
The tables are identical and uses the same datasource.
When I set the group to System.Math.Ceiling (RowNumber (Nothing)/25)
on the last table nothing happens. All rows are shown. I thought I could
add a filter with bottom N = CountRows - 10 but CountRows don't work in
filters.
And if it did I probably would have problems with the grouping to add page
breaks.
I know there have been similar requests, but I haven't been able to find a
solution.
Bright ideas are appreciated.I think I'll just make two different datasources.
One with the first then rows and one with the rest,
then bind each table to its datasource.
"Knut" wrote:
> Hi.
> I would like to put the first 10 rows in a table and not have a page break.
> Then in another table (which will be shown on another page) I would like to
> show the 25 next rows, add a page break, show the next 25 rows, etc.
> I could solve this by
> System.Math.Ceiling (RowNumber (Nothing)/10)
> or
> Add a filter with top N = 10.
> The tables are identical and uses the same datasource.
> When I set the group to System.Math.Ceiling (RowNumber (Nothing)/25)
> on the last table nothing happens. All rows are shown. I thought I could
> add a filter with bottom N = CountRows - 10 but CountRows don't work in
> filters.
> And if it did I probably would have problems with the grouping to add page
> breaks.
> I know there have been similar requests, but I haven't been able to find a
> solution.
> Bright ideas are appreciated.

Wednesday, March 21, 2012

purge process from large table

Hi,
We have specific request here: there is a large table, around 5,000,000 rows
(3GB). It has unique clustered index, created on 6 of it's 10 columns. We
have to delete around 15% of rows every day, but in a way that table keeps
being available all the time. Deletion criteria is date (where
column_date<=getdate()). There is nonclustered index created on column_date
table. Since there is availability criteria, simple: delete <table name>
where column_date<=getdate() is out of the question because of exclusive
table lock on this table.
Any ideas?
Thanks,
PedjaBy "available" do you mean readable via a SELECT?
If you are using SQL 2005, you can take advantage of the new Read Committed
Snapshot Isolation level, which allows a SELECT to read the most
recently-committed version of a set of data, even while that data is being
modified.
If you are using SQL 2000, your readers can use WITH (NOLOCK) as an option
to the SELECT statements, putting the transaction into read uncommited. Your
readers won't have to wait for writers, but you may get inaccurate data.
Is it possible that you can split this delete operation so that it occurs
several times a day? That way it can delete fewer rows, resulting in less
blocking time.
"Pedja" wrote:

> Hi,
> We have specific request here: there is a large table, around 5,000,000 ro
ws
> (3GB). It has unique clustered index, created on 6 of it's 10 columns. We
> have to delete around 15% of rows every day, but in a way that table keeps
> being available all the time. Deletion criteria is date (where
> column_date<=getdate()). There is nonclustered index created on column_dat
e
> table. Since there is availability criteria, simple: delete <table name>
> where column_date<=getdate() is out of the question because of exclusive
> table lock on this table.
> Any ideas?
> Thanks,
> Pedja|||Mark,
We use sql server 2000. By available, I mean both, read/write operations.
NOLOCK hint won't help, because once it is grabbed by purge process, table i
s
being locked until it is completed (that is why I posted this question
initially), so neither reads nor writes are allowed during this time. My ide
a
was to split deletion process to batches of 1000 rows (set rowcount 1000),
but I wanted to hear some other ideas too.
Thanks
"Mark Williams" wrote:
> By "available" do you mean readable via a SELECT?
> If you are using SQL 2005, you can take advantage of the new Read Committe
d
> Snapshot Isolation level, which allows a SELECT to read the most
> recently-committed version of a set of data, even while that data is being
> modified.
> If you are using SQL 2000, your readers can use WITH (NOLOCK) as an option
> to the SELECT statements, putting the transaction into read uncommited. Yo
ur
> readers won't have to wait for writers, but you may get inaccurate data.
> Is it possible that you can split this delete operation so that it occurs
> several times a day? That way it can delete fewer rows, resulting in less
> blocking time.
> --
> "Pedja" wrote:
>|||Pedja,
you could split up your data into several tables, one table per day.
You can access them via a UNION ALL view. Then the purge is very fast,
you just re-create the view, which is a snap, and drop the oldest
table. There are some di

vantages: some of queries against the view
will work slower, and you will not be able to enforse unique (and
sometimes other) constraints just as easily|||Hi
Divide your deletion into small batches
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Here your DML Statement
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
END
SET ROWCOUNT 0
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:38445382-60B6-4327-A3FE-C5AD8BF88E41@.microsoft.com...
> Hi,
> We have specific request here: there is a large table, around 5,000,000
> rows
> (3GB). It has unique clustered index, created on 6 of it's 10 columns. We
> have to delete around 15% of rows every day, but in a way that table keeps
> being available all the time. Deletion criteria is date (where
> column_date<=getdate()). There is nonclustered index created on
> column_date
> table. Since there is availability criteria, simple: delete <table name>
> where column_date<=getdate() is out of the question because of exclusive
> table lock on this table.
> Any ideas?
> Thanks,
> Pedjasql

Purge old ROWS

looking for a script that will purge rows that are 2
months old. I would like to run this as a Scheduled job..
On Tue, 1 Jun 2004 11:05:01 -0700, Rick wrote:

>looking for a script that will purge rows that are 2
>months old. I would like to run this as a Scheduled job..
Hi Rick,
Assuming your table is called MyTable and the creation date of each row is
stored in a column named CrDate, use this:
DELETE FROM MyTable
WHERE CrDate < dateadd(month, -2, getdate())
Untested - test it first, in a transaction. Rollback or commit as needed.
Don't put it in a scheduled job until you've tested it thoroughly.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for the information, It worked perfectly. FYI: I am using this
for ODBC logging on IIS web servers. They send the logs to a SQL server
and I purge them as they get old.
Works very well thanks,
DELETE FROM inetlog
WHERE LogTime < dateadd(month, -2, getdate())
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Monday, March 12, 2012

PULL huge table from SQL Server Problem

Hey Guys

I have meet the same problem, too.

I create a table with 1,380,000 rows data,

the db real size about 114 MB.

The primary key size is nchar(6).

When I use RDA pull, I found that the primary

key in the PDA disappear. So, It took a long time

to get query response.

But when I delete some rows to 680,000 rows of data.

After I pull, The primary key can pull from the SQL Server.

PS: I didn't change any code. Just delete some rows.

Is that SQL-Mobile's bug?

PS: 1.Database and Temp Database limitation both are 384MB

2.If I use query analyzer to add primary key it works! so strange!!

3.Pull process return "S_OK".

4.After Pull process finished, the db connection still alive. It seems not like

time out problem.

5.Local Connection String:"Data Source='%s\\%s';SSCEatabase Password='%s';SSCE:Encrypt Database='true';SSCE:Max Database Size=384;SSCE:Temp File Max size=384;SSCE:Temp File Directory=%s"

We do have tests that do PULL of 200 MB data and almost 10 Lakh rows. Is that possible for you to give the schema and few more details of your environment so that we can try reproducing and find the root cause.

Thanks,

Laxmi

Friday, March 9, 2012

Pull data from rows to make columns

I have a table with three columns:
AcctNbr, Type, CodeValue
Listed below is an example of the database.
AcctNbr Type CodeValue
1 MAILCODE 99
2 MAILCODE 99
3 MAILCODE 99
4 MAILCODE 90
4 MAILCODE 99
4 SEG1 O
5 MAILCODE 99
6 MAILCODE 99
7 MAILCODE 99
8 MAILCODE 99
9 MAILCODE 99
10 MAILCODE 90
11 MAILCODE 99
12 MAILCODE 99
13 MAILCODE 99
14 MAILCODE 99
15 LIST DS1
15 MAILCODE 99
There are multiple Type's for some AcctNbr's and what I want to do is
run a query on the database so that if the AcctNbr has multiple Type's
and CodesValue's it takes them and creates new columns like so:
AcctNbr MailCode_90 MailCode_99 SEG1
4 90 99 O
So on and so forth. There are multiple Type's and multiple codes that
I need to do this with for each account number. If someone could give
me a base code to try I could start somewhere. I am an SQL novice.
Thanks.
JoshGoogle for pivot in t-sql
MC
"cypherus" <fbsdguy@.gmail.com> wrote in message
news:9bc69bb7-1996-4d07-a0c2-fdace73e4c4f@.y21g2000hsf.googlegroups.com...
>I have a table with three columns:
> AcctNbr, Type, CodeValue
> Listed below is an example of the database.
> AcctNbr Type CodeValue
> 1 MAILCODE 99
> 2 MAILCODE 99
> 3 MAILCODE 99
> 4 MAILCODE 90
> 4 MAILCODE 99
> 4 SEG1 O
> 5 MAILCODE 99
> 6 MAILCODE 99
> 7 MAILCODE 99
> 8 MAILCODE 99
> 9 MAILCODE 99
> 10 MAILCODE 90
> 11 MAILCODE 99
> 12 MAILCODE 99
> 13 MAILCODE 99
> 14 MAILCODE 99
> 15 LIST DS1
> 15 MAILCODE 99
> There are multiple Type's for some AcctNbr's and what I want to do is
> run a query on the database so that if the AcctNbr has multiple Type's
> and CodesValue's it takes them and creates new columns like so:
> AcctNbr MailCode_90 MailCode_99 SEG1
> 4 90 99 O
> So on and so forth. There are multiple Type's and multiple codes that
> I need to do this with for each account number. If someone could give
> me a base code to try I could start somewhere. I am an SQL novice.
> Thanks.
> Josh|||Your best bet is to do this with the Rac utility. It will minimize the ugly
sql coding necessary. If your interested post back and I'll hook you up with
the Rac execute statement for this.
www.rac4sql.net
www.beyondsql.blogspot.com|||On Apr 10, 5:56 am, "steve dassin" <stevenos...@.rac4sql.net> wrote:
> Your best bet is to do this with the Rac utility. It will minimize the ugly
> sql coding necessary. If your interested post back and I'll hook you up with
> the Rac execute statement for this.www.rac4sql.net
> www.beyondsql.blogspot.com
I don't really want to download any new programs just for that. I did
post to a couple other boards and somebody gave me some test code that
throws up errors. Can someone either help me fix the errors or help
me with some new test code? Here's the code that I was given:
SELECT AcctNbr FROM [KMAi_V8].[dbo].[A04CODE],
MAX(CASE WHEN Type='MAILCODE' AND CodeValue='90' THEN CodeValue ELSE
NULL END) MailCode_90,
MAX(CASE WHEN Type='MAILCODE' AND CodeValue='99' THEN CodeValue ELSE
NULL END) MailCode_99,
MAX(CASE WHEN Type='SEG1' AND CodeValue='O' THEN Type ELSE NULL END)
SEG1
FROM Acct
WHERE AcctNbr IN (SELECT AcctNbr FROM Acct GROUP BY AcctNbr HAVING
COUNT(DISTINCT(Type+CodeValue))>1)
GROUP BY AcctNbr
And it tosses up this error:
TITLE: SQL Server Import and Export Wizard
--
The statement could not be parsed.
--
ADDITIONAL INFORMATION:
Deferred prepare could not be completed.
Statement(s) could not be prepared.
Incorrect syntax near the keyword 'GROUP'.
Incorrect syntax near the keyword 'CASE'. (Microsoft SQL Native
Client)
--
BUTTONS:
OK
--
Any ideas?
Josh|||Answer here: http://forums.devshed.com/showthread.php?p=2022538&mode=linear#post2022538
SELECT AcctNbr,
MAX(CASE WHEN Type='MAILCODE' AND CodeValue='90' THEN CodeValue ELSE
NULL END) MailCode_90,
MAX(CASE WHEN Type='MAILCODE' AND CodeValue='99' THEN CodeValue ELSE
NULL END) MailCode_99,
MAX(CASE WHEN Type='SEG1' AND CodeValue='O' THEN Type ELSE NULL END)
SEG1
FROM TableNm
WHERE AcctNbr IN (SELECT AcctNbr FROM TableNm GROUP BY AcctNbr HAVING
COUNT(DISTINCT(Type+CodeValue))>1)
GROUP BY AcctNbr
On Apr 16, 4:26 pm, cypherus <fbsd...@.gmail.com> wrote:
> On Apr 10, 5:56 am, "steve dassin" <stevenos...@.rac4sql.net> wrote:
> > Your best bet is to do this with the Rac utility. It will minimize the ugly
> > sql coding necessary. If your interested post back and I'll hook you up with
> > the Rac execute statement for this.www.rac4sql.net
> >www.beyondsql.blogspot.com
> I don't really want to download any new programs just for that. I did
> post to a couple other boards and somebody gave me some test code that
> throws up errors. Can someone either help me fix the errors or help
> me with some new test code? Here's the code that I was given:
> SELECT AcctNbr FROM [KMAi_V8].[dbo].[A04CODE],
> MAX(CASE WHEN Type='MAILCODE' AND CodeValue='90' THEN CodeValue ELSE
> NULL END) MailCode_90,
> MAX(CASE WHEN Type='MAILCODE' AND CodeValue='99' THEN CodeValue ELSE
> NULL END) MailCode_99,
> MAX(CASE WHEN Type='SEG1' AND CodeValue='O' THEN Type ELSE NULL END)
> SEG1
> FROM Acct
> WHERE AcctNbr IN (SELECT AcctNbr FROM Acct GROUP BY AcctNbr HAVING
> COUNT(DISTINCT(Type+CodeValue))>1)
> GROUP BY AcctNbr
> And it tosses up this error:
> TITLE: SQL Server Import and Export Wizard
> --
> The statement could not be parsed.
> --
> ADDITIONAL INFORMATION:
> Deferred prepare could not be completed.
> Statement(s) could not be prepared.
> Incorrect syntax near the keyword 'GROUP'.
> Incorrect syntax near the keyword 'CASE'. (Microsoft SQL Native
> Client)
> --
> BUTTONS:
> OK
> --
> Any ideas?
> Josh

Wednesday, March 7, 2012

Publisher set to Simple Recovery - Is this a problem?

If the publisher database is set on simple recovery mode and if the
transaction log gets truncated on check point, then is there a chance that
the rows that should be replicated might get lost before the log reader agent
can read them and send them to the distributor. Publisher is a 7.0 box and
the distributor/subscriber is a 2000 box.
Adam,
the rows won't be removed unless the log is marked by sp_repldone, so you're
safe.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Friday, March 30, 2012

Puzzled by duplicates

putting error lines in new table or file

Wednesday, March 28, 2012

Putting Data From DataTable/DataSet into SQL Server Table?

Put first 10 rows in a table and then group by each 25 rows in ano

Wednesday, March 21, 2012

purge process from large table

Purge old ROWS

Monday, March 12, 2012

PULL huge table from SQL Server Problem

Friday, March 9, 2012

Pull data from rows to make columns

Pull data from rows to make columns

Wednesday, March 7, 2012

Publisher set to Simple Recovery - Is this a problem?

Sql Public Role

Blog Archive

About Me