I have a table name "ABCD" When I run a Select statement on this table, it returns 100 rows. I would like to store this 100 records for future.
In future I will have the same table with same sturcture. I want to insert this 100 rows into this table.
Any suggestions.
Thanks in Advance
Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
View 6 Replies View Relatedwriting the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
--Final output should be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4
HI,
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
Thanks ,
Sunil
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.
View 2 Replies View RelatedI have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.
;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
Thanks in advance.
I need to store a user ID of whoever made the last change to a record in a table. I have added a field with a default set to 'suser_sid()'.
Is it wise to store SID's in a database table if you are using NT authentication? My concerns are that if the user is deleted by the NT system administrator then I lose data integrety also if the database is backed up and restored onto a different machine with different users it won't work at all.
I'd be grateful for any help with this.
Rich
Hi, can someone help me with this problem. I have recently downloaded VWD and have been playing around with it a bit. I was windering how do I add an image file to a SQL database. I have used SQL in the past but I have never store an image in it. I can add text data and so forth, but I need some pointers into adding images to the sql database. I want to be able to store the images and then retrieve them to a GridView. I have the images in an image folder within the website, but I do not know how to input the reference or link to the images in an sql table.
A quick example, say I have a table called Images, with the following info:imgID (int)imgDesc (varchar(max)imgFile (img)imgTitle (varchar(20))
To input this in sql I would write Insert Into Images (imgDesc,imgFile,imgTitle) Values ("Large fishy", "", "The Big Fish") what would I need to put for imgFile?
I would really appreciate if someone can point me in the right direction.
hi all, what sql data type should i use to store variable length bit patterns no longer than 30 bits?
View 4 Replies View RelatedHi all! I am back with a new problem.
I am designing a database in which the booking is on an hourly basis instead of daily basis. So I want my colums in a table to be like this:
================================ID(PK)Date(In this table I want date only)TimeSlot(in this table I want time only)CourtNo(facility which is to be booked)Booking ID(FK to booking table)================================
The thing is that SQL server donot have a date only format. I am totally stuck on this one, please help! The database which I created will be ideal if I can just store the date!
Regards,
Taimoor
Hi whats the best way to store an image in SQL, for instance the image of a product, bearing in mind the website will be hosted by a hosting company.
View 4 Replies View RelatedHi All,Does anybody know how i can store a paragraph with spaces, newline, empty lines in a sql server 2005 database table? What data type should i declare the table entry to take? I will also like to retrieve the data with what ever i would have stored it with.
View 6 Replies View RelatedMy forms are taking user input, then HtmlEncoding them prior to being stored in the SQL DB. For some reason, SQL is storing quotes as � and it is causing the HTML when decoded in the page to not be rendered properly.
Has anyone come across this issue before?
For example (without encoding for readability):
SQL should store the parsed string as: <a href="someurl" class="main">
but for some reason it's being stored as: <a href=�someurl� class=�main�>.
Thoughts?
Does anyone have thoughts or know of resources that explain the pro's and con's of storing images in a database versus keeping the image in the file system and just storing the path to the image in the database?
View 1 Replies View RelatedHello All!
Can some one give SQL Statement on storing images from disk to the database columns.
Once I store the image how do I test it out that the image is stored correctly?
For example this is what I did
I have a table called table1., field called name
insert into table1(name) values(C:123.bmp)
Is the above statement correct? will it store the file or not?
if it does store them then how do I view them?
Can some one please help me...
Thanks in advance for your help
Venkat
Hi again.
How do I store in SQL SERVER with 2 decimal places?
I set Data Type to Decimal with Scale set to 4 and Precision 19. But this doesn't work.
Any ideas? Thanks.
Which datatype supports miliseconds?
View 2 Replies View RelatedCREATE TABLE [dbo].[blogs] (
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO
using this script i have created my blog table.
and a procedure given below. i am using to insert data in it.
CREATE PROCEDURE SP_BlogAdd
@blog_title nvarchar(500),
@blog_desc_full varchar(8000),
@blogger_name nvarchar(100),
@mailid nvarchar(100),
@blog_desc nvarchar(125),
@cat_name varchar(100),
@b_url varchar(250),
@author nvarchar(250)
AS
INSERT INTO blogs(blog_title, blog_desc_full, blogger_name, mailid, blog_desc, cat_name, b_url,author)
VALUES(@blog_title ,@blog_desc_full ,@blogger_name,@mailid ,@blog_desc ,@cat_name ,@b_url,@author)
GO
now, the problem i m facing is.
i am using varchar datatype for [blog_desc_full] [varchar] (8000).
i want more than this size to store data in it.
please give me some detailed code.
i tried text datatype but i didnt succeed. how to use text datatype.
i replaced with text datatype.but in length i couldnt type. it shows only 16.
Please help me out.
regards,
ASIF
best way to store questionnaire data in a database.Since different questionnairs have different questions and formats i.e dropdown, radio, checkboxes etc building such a database model becomes highly complex.
I've read that if data schema is complex and higly variable it may be better to use an xml document and store that in a databse. However I dont quite understand how you store xml to a database. Do you simply store the entire structure in something like a nvarchar column or is there some other way to store xml to a database.
If you store the entire structure to the databse then how do you query the content to generate reports.
example xml:
Code:
<survey>
<meta>
<id>sample</id>
</meta>
<questions>
<question id="1" type="singlechoice" page="1">
[code]...
I use SQL 2K with an Access 2000 project as front end. I have four date fields in a table. The first one should contain a date (eg 20th of February), the second one a starting hour (eg 8:00h), the third one the finishing hour (eg 10:00h) and the fourth should calculate the difference between the second and third column.
So I created four datetime fields. Storing a date in the first column is no problem, but storing only hours doesn't seem to do what I want. When I enter 8:00 from within the Access, it is stored 1/1/1900 8:00 in SQL Server. How can I make sure that the hours are saved with the date of the second column, so either as 8:00 without further information or as eg 20-2-2004 8:00?
Hello Friends,
How to store newline charecters in sqlserver db. Iam implementing a varchar type attribute.
Regards,
Ch.Praveen
how can i store time in my table
1 hr:55 mins:22 secs
i need to store date alone in a new field from the existing field which is stored date along with time.
Eg:
old field: emplogintime (date and time)
new field: emploginday (date)
Hi,
I'm having a DB designed for me, and I'm inspecting it and wondering what in general is the better way to do this.
We have a product, which we are counting "product views". The DB designer has created columns called "view_today" and "views_alltime".
I specified I wanted a normalized database, I'm thinking this is technically not normalized ? Am I correct ?
Wouldn't it be better to have a query that counted the views off the logging table ? I can't see any advantage to doing it the way its been designed except to save time.
Thanks for any input !
Mike123
Hello all,I have been working with several databases here that are basically datamarts. A lot of the entities have an attribute that is a particularyear and month. For example, a financial transaction may be posted fora particular month, regardless of the actual date on which it occurred.In this system, these year/month combinations have typically beenstored as integers of the form YYYYMM. My question is, how have othersstored this type of information and what advantages/disadvantages haveyou found to your method?The problem that I have found with the current method is that youcannot easily find the difference between two of these dates. Forexample, 200401 - 200312 = 89 (not 1). Storing the values as datetimes(using the first of the month) allows for DATEDIFF(mm, '2003-12-01','2004-01-01') = 1. Of course, a little extra (and meaningless) data isbeing stored. In case the table sizes makes a difference here due tothe extra data being stored, we are usually talking about over 100Mrows.I don't like the idea of storing the values in two columns (year andmonth) because that does nothing to improve on the ability to performuseful functions on the values and very importantly, the month reallyhas no meaning without the year, so I don't think that it should bestored by itself.Thanks for any advice/insight.-Tom.
View 1 Replies View Related
I am writing an app in c#. I will be using the following code to get a number from the user. I had to make the variable a string to prevent any kind of conversion to be required.
string newID;
Console.WriteLine("Enter a new ID");
newID = Console.ReadLine();
Console.WriteLine("The new ID is {0}", newID);
Console.ReadLine();
The ID that this will be collecting will be stored in a SQL Server 2005 Express database. The field in the database is of the type int. Will there be a problem saving this number from the code above into the database? Will there be a problem b/c the datatypes don't match?
..........................................thanks to any willing to help
CREATE TABLE [dbo].[blogs] (
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO
using this script i have created my blog table.
and a procedure given below. i am using to insert data in it.
CREATE PROCEDURE SP_BlogAdd
@blog_title nvarchar(500),
@blog_desc_full varchar(8000),
@blogger_name nvarchar(100),
@mailid nvarchar(100),
@blog_desc nvarchar(125),
@cat_name varchar(100),
@b_url varchar(250),
@author nvarchar(250)
AS
INSERT INTO blogs(blog_title, blog_desc_full, blogger_name, mailid, blog_desc, cat_name, b_url,author)
VALUES(@blog_title ,@blog_desc_full ,@blogger_name,@mailid ,@blog_desc ,@cat_name ,@b_url,@author)
GO
now, the problem i m facing is.
i am using varchar datatype for [blog_desc_full] [varchar] (8000).
i want more than this size to store data in it.
please give me some detailed code.
i tried text datatype but i didnt succeed. how to use text datatype.
i replaced with text datatype.but in length i couldnt type. it shows only 16.
Please help me out.
regards,
ASIF
Is there any method to store the MP3 file in database. And also I want free text search in the table? Please help me its important
Thanks in advance
Hi,
is it possible to
- store a User Defined Function in a table
- find and execute it from a SELECT expression
- using column-data?
Thanks,