|
The following query will delete records from tableB that have a key value which is already present in tableA.
delete
from tableB
where exists
(select top 1 * from tableA
where keyvalue = tableB.keyvalue)
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Ohhh, that's much less troublesome than my solution (and doesn't require me to create a 2nd temp table). I'll 5 you if it works.
5'd! Thanks!
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
Alrightie, the following code works.
--declare and initialize variables
DECLARE @publisher varchar(25);
SET @publisher = 'mypublisher';
DECLARE @dateStart datetime;
DECLARE @dateEnd datetime;
SET @dateStart = '2008-02-27 00:00:00.000';
SET @dateEnd = '2008-03-01 23:59:59.999';
declare @byteChunk int
set @byteChunk = 2;
DECLARE @sizeDivider bigint;
SET @sizeDivider = CASE (@byteChunk)
WHEN 0 THEN 1024
WHEN 1 THEN 1048576
WHEN 2 THEN 1073741824
WHEN 3 THEN 1099511627776
WHEN 4 THEN 1125899906842624
END;
--create our temporary tables
CREATE TABLE #tempB
(
DownloadDate datetime,
Bytes int,
PublisherName varchar(50),
UniqueKey varchar(80)
)
CREATE TABLE #tempC
(
DownloadDate datetime,
Bytes int,
PublisherName varchar(50),
UniqueKey varchar(80)
)
-- gather all of the appropriate records from another table, and put
-- them into a temporary table
insert into #tempB (DownloadDate, Bytes, PublisherName, UniqueKey)
select CAST(FLOOR(CAST(EndTimeStamp as float)) as DateTime) as endtimestamp, TotalBytesReceived, PublisherName, Convert(varchar, CAST(FLOOR(CAST(EndTimeStamp as float)) as DateTime)) + '.' + @publisher as UniKey
from dbo.downloads
where LOWER(publishername) = LOWER(@publisher)
and EndTimeStamp between @dateStart and @dateEnd
order by endtimestamp;
-- transfer only unique records to a second temporary table (records that
-- don't already exist in exist in dailybandwidth) - we're trying to side-step
-- the error handling in SQL and effectively let the Stored proc insert what it
-- can and ignore the duplicate records
insert into #tempC (DownloadDate, Bytes, PublisherName, UniqueKey)
SELECT DownloadDate, Bytes, PublisherName, UniqueKey
FROM #TEMPB
where not exists
(
SELECT DownloadDate, Bytes, PublisherName, UniqueKey
FROM dailybandwidth
where #tempb.uniquekey = dailybandwidth.uniquekey
)
-- transfer records from 2nd temp table (guaranteed to be unique)
-- into destination table
insert into dbo.DailyBandwidth (DownloadDate, Bytes, PublisherName, UniqueKey)
select DownloadDate, SUM((Bytes*1.0) / @sizeDivider) as Bytes, PublisherName, UniqueKey
from #tempC
group by DownloadDate, Publishername, UniqueKey
order by DownloadDate asc
-- drop our temp tables
drop table #tempB
drop table #tempc
I know it seems cumbersome, but it's the only way I could figure out how to do it. The original table contains over 5 million records (and it grows by several hundred thousand records every day). The ultimate goal is to tally up all bytes for a given day/publisher, and store them into a much smaller table (representing 1 day of downloads per publisher). This data will ultimately be presented on a web page, so speed of retrieval is paramount.
If anyonw can suggest a better way, I'm most certainly all ears.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
Hello, first time asking a question here, so please be gentle
I'm filling a dataset and using OleDbDataAdapter.Update() to insert it into a DBF table using Jet. The update is running painfully slow and I've read up a bit on how to speed up and saw something about starting the transaction before running the update. Doesn't really seem to do much for me. I have a table write that goes on for 24 minutes. Any help would be appreciated.
Code snippit:
If Not cnDBConn.State = ConnectionState.Open Then
cnDBConn.Open()
End If
Dim transID As System.Data.OleDb.OleDbTransaction = cnDBConn.BeginTransaction()
cmInsert.Transaction = transID
daAdapter.InsertCommand = cmInsert
daAdapter.DeleteCommand = Nothing
daAdapter.UpdateCommand = Nothing
daAdapter.ContinueUpdateOnError = False
daAdapter.Update(dsOut, sCurrentTable)
|
|
|
|
|
How do i get a package variable "varPath" from within the Data Flow script component in SSIS
modified on Thursday, March 13, 2008 10:04 AM
|
|
|
|
|
You'll have to define it as either a read or read/write variable in the script properties. Once you've done that you can access it from within the script.
|
|
|
|
|
|
Hi!!
I'm having a problem( again) while creating a .dbf file. First, I have this line to create the table:
Create Table Results.dbf (BuyDate Date)
And this worked well.
Now, I've this line to add a date in the table:
Insert Into Results.dbf (20070922)
But it gives me a "Data type mismacth" error. I've already saw that the date format is YYYYMMDD. So where is the error here??
|
|
|
|
|
Rafael Carmo wrote: Hi!!
I'm having a problem( again) while creating a .dbf file. First, I have this line to create the table:
Create Table Results.dbf (BuyDate Date)
And this worked well.
Now, I've this line to add a date in the table:
Insert Into Results.dbf (20070922)
But it gives me a "Data type mismacth" error. I've already saw that the date format is YYYYMMDD. So where is the error here??
AFAIK, a DBF file stores all data as ASCII text only - maybe this is the issue here...
|
|
|
|
|
Just only ASCII text?? So why is there a Date format for .dbf file...??? I tried to insert '20070922', "20070922" and this didn't work as well...
|
|
|
|
|
Snaider wrote: Just only ASCII text?? So why is there a Date format for .dbf file...??? I tried to insert '20070922', "20070922" and this didn't work as well...
It's been a while since I have seen a DBF file... I think the Date type in a DBF file is a 10-char storage. Maybe you could try '03/13/2008' (depending upon your system locale)
|
|
|
|
|
Try this
Insert Into Test2 values ('09/22/2007');
and check the data in database.
Do good and have good.
|
|
|
|
|
It didn't worked...but I've just find out how to do it:
Insert Into Results.dbf Values (CtoD([06/18/2005]))
Only by this way it worked.
Thx for everybody's help
|
|
|
|
|
Dang. I'm stuck doing DBF stuff again!
But to answer your question, you can use the "CTOD" function as you've seen. Or you can use {} as the field delimiters for an actual date field. So:
Insert into Results({09/22/2007})
|
|
|
|
|
hi! I have a problem with sql server. I want to find a record from a database, with like 30 tables in it, and lots of records. I don't know exactly where that record is, in which table. So, cause I am a beginner in SQL server, with the knowledge what I have, I did this:
SELECT * FROM dbTest WHERE VALUES ='some value';
and it gives me error, near VALUES, so I don't know how to find it, any help will be welcomed, so I can resolve this issue. Thanks ahead
|
|
|
|
|
laziale wrote: I don't know exactly where that record is, in which table. So, cause I am a beginner in SQL server, with the knowledge what I have, I did this:
SELECT * FROM dbTest WHERE VALUES ='some value';
and it gives me error, near VALUES, so I don't know how to find it,
Well, if dbTest is the name of the database then it won't work. You must specify the name of the table. If you don't know your database well enough to know where things are then you should be asking a colleague for some sort of ER diagram and data dictionary (documentation) to show you where things are.
|
|
|
|
|
no, I know the name of the database, the name is dbo_Customers, but that database has around 30 tables, and lots of records inside, so any help on some query, to find the text, like 'sometext' will be helpfull. Thanks for replying
|
|
|
|
|
laziale wrote: but that database has around 30 tables, and lots of records inside, so any help on some query, to find the text, like 'sometext' will be helpfull. Thanks for replying
You write 30 queries. Or you find out where the data is held.
As I said, you need to ask for a ER diagram and data dictionary to find out how the database is structured and what it all means. Randomly fishing around inside tables for some value is really not the way to go.
|
|
|
|
|
You have to give table name in select statement and if you want to search sometext from multiple tables, you should use multiple querires for multiple tables.
Do good and have good.
|
|
|
|
|
can you give me some example please. Thanks
|
|
|
|
|
select * from tablename where columnname = 'sometext';
Please tell what is "Values" in your query?
It must be the column name in your table on which you want to find the sometext.
Do good and have good.
|
|
|
|
|
the value is 'noordzeeweg' but the think is that we don't know exactly in which table or column exactly is the record, and we have around 30 tables with lots of columns and records inside. Thx for helping
|
|
|
|
|
laziale wrote: the value is 'noordzeeweg' but the think is that we don't know exactly in which table or column exactly is the record, and we have around 30 tables with lots of columns and records inside.
You say "we don't know" which implies that no one knows where this data is in your database. I therefore have to advise you to start creating documentation for your database. It is totally absurd to be running a database and not know what sort of information is in it and where it is.
All I can say is that the value hints that it might be the name of a road or street. Do you have any tables that hold that kind of information?
|
|
|
|
|
Hi Friends..
Can any body help me on....Database Migration.First time I am going to do this.
what approach i should follow.or any best approach you have implemented.
Oracle to SQL Server Migration
Dasrimk
|
|
|
|
|
Heres the scenario...
An ASP Page calls a c# component registered using regasm /tlb seehash.dll
I know the registration of the dll has gone fine because for testing purposes the dll just returned a simple string 'IamSeeHash' which got displayed on the asp page. Then I went back to my seehash.dll, uncommented the following line
Database db = DatabaseFactory.CreateDatabase();
and recompiled the dll and re-registered it again.
now I get the error 'Object reference not set to an instance of an object.'
I figure this could be because the config file cant be located by the asp process, so I created a copy of the config files in the SystemRoot%\System32 directory as 'dllhost.exe.config' and also 'asp.dll.config'
another copy named as 'index.asp.config' in the asp pages root directory. still I get the same error
I even went back to my seehash.dll and modified the line to add the specific database name
Database db = DatabaseFactory.CreateDatabase("MyDb");
then it says cant locate the database in the configuration file
What is going wrong? Please help.
Thanks.
<b>+Heres my config file +</b>
<configuration>
<configSections>
<section
name="dataConfiguration"
type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings,
Microsoft.Practices.EnterpriseLibrary.Data" />
</configSections>
<connectionStrings>
<add
name="MyDb"
providerName="System.Data.SqlClient"
connectionString=
"server=(local)\SQLEXPRESS;
database=MyDb;
UID=sa;PWD=usa;" />
</connectionStrings>
<dataConfiguration defaultDatabase="MyDb"/>
</configuration>
<b>+Heres my folder structure +</b>
For the asp page
C:\Inetpub\wwwroot\simpleAspSite
For the dlls (all referenced dlls of the datablock are in this directory)
c:\mydlls\seehash.dll
I am continually amazed by how little code is required to use atomic clocks in satellites 11,000 miles above my head.
|
|
|
|