|
I have a .csv file with following column:
Col1,Col2
And the data table has one extra datetime column:
Col1,Col2,TimeStamp
While importing data using BULK INSERT from the CSV, is there a way to specify the value for the TimeStamp column too? (Timestamp would be same for all the newly imported rows)
Suhredayan
|
|
|
|
|
How about defining a default value for the column?
ALTER TABLE dbo.tablename ADD CONSTRAINT
DF_TimeStamp DEFAULT '20091218' FOR TimeStamp
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Ashfield wrote: How about defining a default value for the column?
That may not work since the timestamp is same only per bulk insert. I do have a solution for this, wrap bulk insert inside a stored proc, and update the the timestamp from SP. But this would introduce few additional steps, was wondering if this can be avoided in case if BULK INSERT already has this feature.
Thank you,
Suhredayan
|
|
|
|
|
I have a time series data in which a numerical indicator fluctuates over time. If I want to find out for which given day, this indicator has crossed above 30 in the past 3 days. Is it possible to write a SQL query that retrieve this information?
The logic to check for the crossing movement is:
IF indicator(day x) > 30 AND indicator(3 days ago from day x) <30 THEN
Return "YES WE HAVE AN UPWARD CROSSING MOVEMENT THROUGH 30 FOR PAST 3 DAYS!"
END IF
Can this be accomplished using a SQL CASE statement with a HAVING clause or something else?
I'd greatly appreciate any help! If possible please provide a sample code 
|
|
|
|
|
An exists should do it - as far as I can tell from your description
select column_list....
from table1 t1
where indicator > 30
and exists (select 1 from table1 t2 where t2.pk = t1.pk and t2.indicator < 30 and t2.dateadded = dateadd(day,-3,t1.dateadded))
where PK is your primary key
[Edit] I had missed the date bit from the exists originally [/Edit]
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
modified on Tuesday, December 15, 2009 11:17 AM
|
|
|
|
|
Hi Ashfield, Your code looks exactly like what I need! I'll give it a try after getting home. 
Many thanks again.
modified on Tuesday, December 15, 2009 10:19 PM
|
|
|
|
|
no problem
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Please do not cross post.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Thanks for your help in the other section, sorry about cross-posting. Will try to prevent that in the future. 
|
|
|
|
|
Hi everyone,
my table is
6219 HBO 02 0
6220 HBO1 0201 6219
6221 HEAO 0202 6219
6222 HTS 0203 6219
6231 MEAO 0403 6228
6232 MTS 0404 6228
6234 Midde 05 0
6235 Overi 06 0
6228 MBO 04 0
6236 WO 10 0
6237 Post 07 0
6239 Basi NULL NULL
6448 Athe 05001 6234
but i need like this,
6219 HBO 02 0
6220 HBO1 0201 6219
6221 HEAO 0202 6219
6222 HTS 0203 6219
6228 MBO 04 0
6231 MEAO 0403 6228
6232 MTS 0404 6228
6234 Midde 05 0
6448 Athe 05001 6234
6235 Overi 06 0
6236 WO 10 0
6237 Post 07 0
6239 Basi NULL NULL
Nothing is Impossible. Keep always Smiling...
|
|
|
|
|
|
that is all child records should be displayed next to parent.
Nothing is Impossible. Keep always Smiling...
|
|
|
|
|
If you tell us that the fourth field is the parent's ID (and the first field is its ID), then we will have an easier time trying to figure out what you want.
modified on Tuesday, December 15, 2009 10:57 AM
|
|
|
|
|
Try this.
Inputs:
declare @t table(childid int,name varchar(20),code int,parentid int)
insert into @t values (6219,'HBO',02,0)
insert into @t values(6220,'HBO1', 0201,6219)
insert into @t values(6221,'HEAO',0202,6219)
insert into @t values(6222,'HTS',0203,6219)
insert into @t values(6231,'MEAO',0403,6228)
insert into @t values(6232,'MTS',0404,6228)
insert into @t values(6234,'Midde',05,0)
insert into @t values(6235,'Overi',06,0)
insert into @t values(6228,'MBO',04,0)
insert into @t values(6236,'WO',10,0)
insert into @t values(6237,'Post',07,0)
insert into @t values(6239,'Basi',NULL,NULL)
insert into @t values(6448,'Athe',05001,6234)
select * from @t
Query:
;with cte as
(
select
cast(t1.childid as varchar(1000)) [path]
, t1.childid
,t1.name
,t1.code
,t1.parentid
,0 AS [Level] from @t t1 where parentid = 0 or parentid is null
union all
select
cast([path] + '/' + cast(t1.childid as varchar(1000)) as varchar(1000)) [path]
,t1.childid
,t1.name
,t1.code
,t1.parentid
,c.[Level]+1 AS [Level]from @t t1
join cte c
on c.childid = t1.parentid
)
select childid,name,code,parentid
from cte order by [path]
Output:
childid name code parentid
6219 HBO 2 0
6220 HBO1 201 6219
6221 HEAO 202 6219
6222 HTS 203 6219
6228 MBO 4 0
6231 MEAO 403 6228
6232 MTS 404 6228
6234 Midde 5 0
6448 Athe 5001 6234
6235 Overi 6 0
6236 WO 10 0
6237 Post 7 0
6239 Basi NULL NULL
Niladri Biswas
|
|
|
|
|
Thanks for ur timely help...
Nothing is Impossible. Keep always Smiling...
|
|
|
|
|
Hi!
Can someone please tell me how would I go about creating a Stored Proc to select records between dates?
I have 2 variables, date1 and date2 I want to create a stored proc as follows:
select * from table between date1 and date2 where the dates are variables in the stored proc.
Illegal Operation
|
|
|
|
|
select * from table
where datecolumn between date1 and date2
e.g.
declare @t table(activity varchar(10),datecol datetime)
insert into @t
select 'activity1', '2009-12-01' union all
select 'activity2','2009-12-02' union all
select 'activity3','2009-12-15' union all
select 'activity4','2010-01-02' union all
select 'activity5','2009-12-31'
Declare 2 date variables
declare @date1 datetime,@date2 datetime
set @date1 = '2009-12-01'
set @date2 = '2009-12-15'
I want to select records between 1st Dec 2009 to 15th Dec 2009.
Query:
select * from @t
where datecol between @date1 and @date2
Output:
activity datecol
activity1 2009-12-01 00:00:00.000
activity2 2009-12-02 00:00:00.000
activity3 2009-12-15 00:00:00.000
Niladri Biswas
|
|
|
|
|
CREATE PROCEDURE usp_GetDataFromDateRange(
@Start DATETIME,
@End DATETIME
)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM MyTable
WHERE DateCol BETWEEN @Start AND @End
RETURN
END
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
i have table A with field password varchar(15), am migtrating the data to password in another table datatype varbinary with script component transformation. i set the password to byte stream
i have this code to encrypt the password so i can store it as varbinary
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Try
Dim hashBytes As Byte()
Dim encoding As New UnicodeEncoding
hashBytes = encoding.GetBytes(Row.app)
Dim sha As New SHA1CryptoServiceProvider
Dim crpytPassword As Byte() = sha.ComputeHash(hashBytes)
Row.passout = crpytPassword
Catch ex As Exception
End Try
End Sub
but am getting this error
[AccountProfile New Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
[AccountProfile New Destination [16]] Error: There was an error with input column "pass" (88) on input "OLE DB Destination Input" (29). The column status returned was: "The value could not be converted because of a potential loss of data.".
thanks
|
|
|
|
|
Hi,
We have event-based data currently being logged in relational database structures. For various reasons this data need to be stored elsewhere, possibly on our existing PI historian database. We need to ascertain whether PI can relatively easily store this type of event based data, and ultimately produce reports using RT Web Parts, also relatively easily. If not, we would rather log it in a SQL Server database.
1. This data are obtained via a SSIS package from flat files and stored in a SQL server database.
2. Currently we have around 550 event based jobs running parallel typically on a daily basis.
3. This constitutes in approximately 100 000 records every 5 minutes being logged. That’s more or less 350 records every single second.
4. These events are currently sorted on a BigInt type incremented number from low to high. Can PI log values at a micro sub second level so that the values can be displayed in the correct order it was logged? Or are there some other way to do this?
5. Note that EvTime only saves at second level. EvSeqNo is necessary here to sort the data sequentially, as many records are logged per second.
6. Can Pi handle this huge influx of data?
7. A sequence can constitute a various number of events, from starting that sequence by starting a job by an operator, holding or diverting it. Starting, holding, diverting and so forth constitutes an event. There are various other events as well
8. A job can start another job as well.
9. EvJob, EvLevel, EvLabel, EvKey,EvText, EvSeqNo are all stored in separate database columns. Must this be concatenated into one delimited string or is there an easier way to store the data in PI?
10. EVJob should typically the tag name. However when a job is started by an operator EVJOb can be something like :Citect which means that the actual job being started must be retrieved from the EvText string.
11. Once all this data is being logged, can it easily be retrieved to display in reports using RT Web Parts?
|
|
|
|
|
(first posted in The Lounge but referred onwards)
Hi all
Whilst I have done quite a bit of ASP.Net programming in the past, most of my recent development work has been with Ruby on Rails. I'm now starting up a new project that requires ASP.Net and I'm trying to see how to do a couple of the really great features in RoR.
The main one is database migrations.
In RoR, you have a logical schema for the database tables stored as a series of migrations: each of which contains the changes to up or down the rev level of the database tables. As you develop the models, you add migrations. This makes rapid development involving databases really easy.
Does anything similar exists for VS and ASP.Net? I've been through much of the ASP.Net MVC pages but all the references I've seen are too micky mouse to see anything useful (i.e. they all deal with local MDF databases rather than a remote SQL Server database).
Regards
Gareth
===
GarethHowell
http://www.elastictime.com
|
|
|
|
|
|
SELECT DISTINCT test
FROM [abc].[dbo].[a]
inner join [def].[dbo].[b]
on [abc].[dbo].[a].[mail] = [def].[dbo].[b].mail
inner join idx
on idx.uid = mail.uid
where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67'
and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'
The above query filters the first id but it is ignoring the and bit ?
So
where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67'
works but
and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'
doesn't.
Is there anything am doing wrong. Can i not put where clause like above.
Thank You
Andyyy
modified on Monday, December 14, 2009 6:28 AM
|
|
|
|
|
AndyInUK wrote: where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'
What are you trying to achieve with this?
If id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' is true then logically id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43' must also be true, so it is unnecessary.
On the other hand, if id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' is false then the second test after the AND is irrelevant and it doesn't matter whether it is true or false.
I don't understand why you have included the test for id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43' since it doesn't seem to do anything useful in your query, unless I am missing something.
|
|
|
|
|
basically user can have many ids - So i have to get the users with id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' only and it should not include the users who has got id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' and also id = '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'
Becuase if i only use the first where clause it gives me users with that id plus they have many other ids associated with them which i don't want.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
