|
I know the bulk insert won't work on the CSV itself. I know that the below code is incorrect to do much of what I want. Problem is I don't know how make it all work together.
The first part should work as is, I just need to pass the file name to the procedure.
Edited the first part here to actually work. Plus adding in all the normalization updates I know need to be there.
CREATE TABLE [dbo].[TempCsvImport](
[RealEstateNumber] [nvarchar](11) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[StreetNumber] [nvarchar](10) NOT NULL,
[StreetName] [nvarchar](50) NOT NULL,
[Type] [nvarchar](10) NOT NULL,
[Direction] [nvarchar](10) NULL,
[Unit] [nvarchar](20) NULL,
[City] [nvarchar](20) NOT NULL,
[ZipCode] [nvarchar](10) NOT NULL)
GO
SET QUOTED_IDENTIFIER OFF
DECLARE @fn varchar(8000)
SELECT @fn = 'C:\Users\owner\Documents\Visual Studio 2010\Projects\UPA Data Miner\UPA Data Miner\bin\Debug\634556001928942124.csv'
DECLARE @cmd varchar(8000)
SET @cmd = "BULK INSERT dbo.[TempCsvImport] FROM '" + @fn + "' WITH(CODEPAGE='RAW',FIELDTERMINATOR = ',', ROWTERMINATOR='\n')"
EXEC(@cmd)
GO
UPDATE [dbo].[TempCsvImport]
SET [ZipCode] = SUBSTRING(ZipCode,1,5)
WHERE LEN([ZipCode]) != 5
GO
UPDATE [dbo].TempCsvImport
SET RealEstateNumber = REPLACE(RealEstateNumber,"-","")
WHERE CHARINDEX("-",RealEstateNumber) != 0
The next part is where I have no clue how to make it all work. The '@' parameters all have information from a record in the TempCsvImport
SELECT * FROM [dbo].[PropertyLookupInformation]
WHERE [dbo].[PropertyLookupInformation].[RealEstateNumber] = @RealEstateNumber
UPDATE [dbo].[PropertyLookupInformation]
SET [Name] = @Name
,[StreetNumber] = @StreetNumber
,[StreetName] = @StreetName
,[Type] = @Type
,[Direction] = @Direction
,[Unit] = @Unit
,[City] = @City
,[ZipCode] = @ZipCode
,[LastReviewed] = CURRENT_TIMESTAMP
WHERE RealEstateNumber = @RealEstateNumber
INSERT INTO [dbo].[PropertyLookupInformation]
([PropertyID]
,[RealEstateNumber]
,[Name]
,[StreetNumber]
,[StreetName]
,[Type]
,[Direction]
,[Unit]
,[City]
,[ZipCode]
,[LastReviewed])
VALUES
( NEWID()
,@RealEstateNumber
,@Name
,@StreetNumber
,@StreetName
,@Type
,@Direction
,@Unit
,@City
,@ZipCode
,CURRENT_TIMESTAMP)
Finally
DROP TABLE [dbo].[TempCsvImport]
If I have to do this all from the program, that is fine. I'd just rather use the database cause it is a lot faster internally than I can do the processing externally.
modified 31-Oct-11 9:15am.
|
|
|
|
|
I never do this type of operation from within the database, too fragile with permissions and operations that are not part of a database operation. Like checking for the source file, moving it to archive and general IO work.
Break your process into 2 operations, does the bulk load work!
Now that you have the data in a table (change it to a temp table later).
You logic of using variables implies a cursor, something to avoid if possible. What is wrong with a normal update and insert query?
Update D Set D,Field = S.Field
--Select *
from DestTable D inner join SourceTable S on D.Key = S.Key
Insert DestTable
Select Field1....
From SourceTable
Where KeyField not in(select keyfield from desttable)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
All the code where I created the temp table and all works like a charm.
The update/insert phase is where I have a problem. It has to do with my limited knowledge of SQL.
Of course during all this I found out the data was being inserted wrong so I have to redo what I've already inserted which gives me a chance to fix the problem. Thank god it isn't live data.
But I finally decided to go with
SET ANSI_WARNINGS OFF
MERGE PropertyLookupInformation as targetDB
USING TempCsvImport as sourceDB
ON targetDB.RealEstateNumber = sourceDB.RealEstateNumber
WHEN MATCHED
THEN
UPDATE
SET Name = sourceDB.Name
,StreetNumber = sourceDB.StreetNumber
,StreetName = sourceDB.StreetName
,Type = sourceDB.Type
,Direction = sourceDB.Direction
,Unit = sourceDB.Unit
,City = sourceDB.City
,ZipCode = sourceDB.ZipCode
,LastReviewed = CURRENT_TIMESTAMP
WHEN NOT MATCHED BY TARGET
THEN
INSERT
(PropertyID
,RealEstateNumber
,Name
,StreetNumber
,StreetName
,Type
,Direction
,Unit
,City
,ZipCode
,LastReviewed)
VALUES
( NEWID()
,sourceDB.RealEstateNumber
,sourceDB.Name
,sourceDB.StreetNumber
,sourceDB.StreetName
,sourceDB.Type
,sourceDB.Direction
,sourceDB.Unit
,sourceDB.City
,sourceDB.ZipCode
,CURRENT_TIMESTAMP)
;
The only thing I'm not sure of doing it this way is performance issues.
Now just to get it put into a stored proc and we are set and ready to go.
|
|
|
|
|
Brownie points for using the MERGE functions, I dislike them only b/c I consider separate update/insert queries simpler to support. You may be right to suspect the performance but I have no helpful info on that.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When I've used BCP in the past, I've created a special table to hold the raw data (all as varchar) and used a trigger to move (and convert) the data to the real destination. This technique also allows for adding foreign keys as necessary.
On the other hand, I only use BCP for occasional imports, if I have data I need to import frequently I write a console app or Windows Service to do it.
|
|
|
|
|
Hi,
anyone heard of and tried a mobile app as mysql client for Android?
Thanks,
Jassim
|
|
|
|
|
Hi,
I have sql server table containing dates,currency and rates.
For non working dates we dont get rates data, so I need a stored proc to fill these gaps.
for example: for Saturday and Sunday, rates data is not received, so for Sat and Sun data with rates of previous working days i.e. Friday should be entered. Same is the case with public holidays also.
Please provide me with some sample proc.
Any help is appreciated!
|
|
|
|
|
Devesh Sinha wrote: Please provide me with some sample proc.
Asking for the codez is just lazy.
You need to decide if you want linier interpolation or a copy forward methodology. Both are very simple comparing the friday and monday rates and creating the new records as required.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi all! I would like to know how to grant sql server role to another role in a database. I have a role which I have named 'GeneralAdministration' who are headed by another role which I have also named 'HeadsOfAdministration'. Each role has its own members.
Now the problem I am having is that in one of the tasks of those in the 'GeneralAdministration' role, they need to select the member in the 'HeadsOfAdministration' role who gave the authorisation for that task. I want a way to get the list of the members in the 'HeadsOfAdministration' role to be displayed in a combobox so that the one who gave the authorisation can be selected. But I have realised that when members in the 'GeneralAdministration' role log in, the 'HeadsOfAdministration' role is not visible to them.
I get an error in my C# application that 'HeadsOfAdministration' is not a role. I would like to know if there is a way to make the 'HeadsOfAdministration' role visible to those in the 'GeneralAdministration' role so that the one who gave the authorisation can be selected. Thanks in advance.
|
|
|
|
|
I seem to remember that SQL Server has some type of an app in which I can create or open a DB file and view the contents, add tables, set keys, add/update/delete records, etc., like one can do in Access. But I don't see it anywhere. Is it in some other installation package?
|
|
|
|
|
You need to install the "SQL Server Management Studio". See here[^] for the Express version.
|
|
|
|
|
When you install SQL Server, you have to choose 'Management Tools' in the list so that SQL Server Management Studio is installed.
|
|
|
|
|
Hi all,
I'm trying to write an update statement with a subquery and can't quite get it right. Hoping you can help. Overall goal of the stored procedure I'm writing is to merge data and make sure I have no duplicates and that the responses associated with a potential duplicate are re-associated with the original record instead of the duplicate.
So......
Basic structure of tables is this:
Rosters table - contains all of the rosters in the database
RosterEntries table - contains a single record for each person in a roster. FK here is back to the rosters table.
ParticipantResponses - contains a record for each response a person gave. FK here is back to the RosterEntries table.
I am merging the rosters. So when I'm done, there will be one roster where there were two. During the merge, I need to make sure that no entries in the RosterEntries table is duplicated and also make sure that the responses that are tied to a duplicate record are reassociated with the original, not the duplicate.
I have an @Existing table variable. This is a subset of the RosterEntries table and it contains only those records that are associated with the original roster.
I have the @Dupes table variable. This is a subset of RosterEntries that contains only those records from the roster to be merged (the one that will be going away) which are duplicates of those already in the RosterEntries table.
So, let's say James Kirk is in the RosterEntries table twice, once associated with RosterID1 and once associated with RosterID2. RosterID2 is going away. So James Kirk's first record would be in my @Existing table variable. James Kirk's 2nd record will be in my @Dupes table and this is the one I want to get rid of. But before I do that, I have to associate his responses with that first record (the RosterID1 one) so I don't have orphaned responses. Basically, I'm trying to replace the FK in ParticipantResponses so that it points to Kirk's first record (RosterID1 record) not the 2nd one (RosterID2 record)
The SQL statement I've been working on is this:
update participantresponses pr, @dupes d
set pr.rostEntID =
(
select e.recid
from @Existing e, @dupes d2
where
e.refid = d2.refid
and e.palias = d2.palias
and e.palias2 = d2.palias2
and e.pweight = d2.pweight
and e.pHandicap = d2.phandicap
and e.loginID = d2.loginid
and e.password = d2.password
)
where pr.rostEntID = d.recID
(I know... there's a lot of where clause activity in the subquery, but that's the specs... dupes are defined as those where all of those fields are equal. Same criteria was used to fill the @Dupes table)
The way I read this (obviously not quite right) is that I want to update the PR table, setting the PR.RostEntID = the recordID from the existing table where that existing entry matches the one in the dupes table.
The error I'm getting is:
Msg 102, Level 15, State 1, Line 125
Incorrect syntax near 'pr'.
Msg 156, Level 15, State 1, Line 139
Incorrect syntax near the keyword 'where'.
Could y'all help me figure out what I'm doing wrong and/or how I can get this replacement made? I know I could do it with a loop and that's probably what I'll end up doing for now but I just KNOW there has to be a way to batch process this.
Thanks in advance!
-- Denise
|
|
|
|
|
If you are using SQL Server then try
update pr
set rostEntID =
(
select TOP 1 e.recid
from @Existing e, @dupes d2
where
e.refid = d2.refid
and e.palias = d2.palias
and e.palias2 = d2.palias2
and e.pweight = d2.pweight
and e.pHandicap = d2.phandicap
and e.loginID = d2.loginid
and e.password = d2.password
)
FROM participantresponses pr
INNER JOIN @dupes d
ON pr.rostEntID = d.recID
Notice the TOP 1 was added to prevent multiples.
|
|
|
|
|
That did it! Thanks a million!!!
Denise Duggan
|
|
|
|
|
I have as table like
TheDate
Type
Value
I need to retreive the last value for each type.
I try a SQL command like
SELECT MAX(TheDate), Type, Value from MyTable GROUP BY Type
But I receive an error :
Erreur SQL : ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
Do you have any idea ?
|
|
|
|
|
I would try
SELECT MAX(TheDate), Type, Value
FROM MyTable
GROUP BY Type, Value
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Thanks for the idea, but it's not enought because the values are diffrent.
I receive in this case :
MAX(TheDate), Type, Value
2011/08/11 , T1 , 10
2011/08/17 , T1 , 12
2011/09/25 , T1 , 11
2011/08/15 , T2 , 10
...
And I only need the last value for T1, T2, ... !
Mike
modified 26-Oct-11 8:09am.
|
|
|
|
|
Then you need to remove the Value column from the query.
SELECT MAX(TheDate), Type
FROM MyTable
GROUP BY TYPE
But then you don't get the value.
So join the result from this query with the original table.
WITH maxdate AS (
SELECT MAX(TheDate) TheDate, Type
FROM MyTable
GROUP BY Type
)
SELECT t.TheDate, t.Type, t.Value
FROM MyTable t join maxdate d
ON t.TheDate = d.TheDate
AND t.Type = d.Type
|
|
|
|
|
Super,
It's working
Thanks
|
|
|
|
|
If you get performance issues on this query you will need a composite index on (Type,TheDate)
|
|
|
|
|
Heres a query using Rank as suggested by Mycroft
SELECT TheDate, TYPE, Value
FROM (
SELECT TheDate, Type, Value, Rank( ) OVER (PARTITION BY Type ORDER BY TheDate DESC NULLS LAST) RN
FROM MyTable
)
WHERE RN = 1
Just a bit curious whether performance is better.
|
|
|
|
|
Try this query
select distinct
(select top 1 t2.thedate from MyTable as t2 where t2.[type]=t1.[type] order by t2.thedate desc) as thedate
, t1.[type]
,(select top 1 t2.value from MyTable as t2 where t2.[type]=t1.[type] order by t2.thedate desc) as [Value]
from MyTable as t1
order by t1.[type]
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
modified 26-Oct-11 13:11pm.
|
|
|
|
|
You first need to find the MAX(TheDate) and then use that to find the Value(s).
select Value
,Type
from TABLE
where TheDate in ( select MAX(TheDate)
from Table
group by Type
)
;
That's off the top of my head, but should be close.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
OK, but you select the max date and not the max date per type
I have several types and I shearch the last value for each type
and not the max date per type
modified 26-Oct-11 10:39am.
|
|
|
|
|