Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing. SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"Many thanks in advance!
Hello, Its hard trying to explain this. I have 3 tables Table 1 is where the users are stored, each user has a username and a userrank Table 2 is where the points that decides the userrank are stored Table 3 contains the available userranks like this
Table 1 (user_list) looks briefly like this:username nvarchar(20),userrank int, -- Reference to Table3 id... alot more fields Table 2 (settings_profile) looks like this:username nvarchar(20),total_active_points int,... some more fields Table 3 (data_ranks) looks like this:id int primary key auto inc,rankname nvarchar(20),min_pts int,max_pts int
Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code: WHILE (SELECT @username = username, @id = id, @temp1 = ap_sentmails, @temp2 = ap_createdthreads, @temp3 = ap_createdanswers, @temp4 = ap_signguestbook, @temp5 = ap_blogcomment, @temp6 = ap_createblogentry, @temp7 = ap_profilefirsttime, @temp8 = ap_profilephoto, @temp9 = ap_activateguestbook, @temp10 = ap_addnewfriend, @temp11 = ap_superguruvote, @temp12 = ap_forumtopicvote, @temp13 = ap_labervote, @temp14 = ap_funstuffitemvote, @temp15 = ap_movievote, @temp16 = ap_actorvote, @temp17 = ap_money_new WHERE (created < Dateadd(dd, -7, @todaysdate))BEGINSET @sum = 0SET @sum = @temp1 + @temp2 + @temp3 + @temp4 + @temp5 + @temp6 + @temp7 + @temp8 + @temp9 + @temp10 + @temp11 + @temp12 + @temp13 + @temp14 + @temp15 + @temp16 + @temp17UPDATE settings_profile SET total_active_points = total_active_points - @sum WHERE (username = @username)DELETE FROM konto_daylist WHERE (id = @id)END Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.Something like this... DECLARE @username nvarchar(20)DECLARE @pts int, @rank int ...something that starts a loop thru table A (user_list) and get the username into @username... SELECT @pts = total_active_points FROM settings_profile WHERE (username = @username)-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranksSELECT @rank = id FROM data_ranks WHERE (pts_min => @pts AND pts_max < @pts)UPDATE user_list SET rank = @rank WHERE (username = @username) ...next persion in the loop... This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank... But how do i loop thru all the users? with a cursor?
If I have a table with three fields (A, B, C) and 'A' is a primary key, 'B' and 'C' do not have any content and another table with the same number of fields contains the same fields(A, B, C) and the fields do contain data.
I want to populate the first table with the data from the second table where the primary key 'A' is equal.
I'm the assistant to the assistants assistant and have no SQL base at all but have to change dates in our SQL database. I've got the script for how to do an update/replace for non date fields and have been successful at that. Unfortunately when I try to use the script to change date fields it does not work. It goes through the process and acts like it is working (even asks me if I want to update the records) but when I look in the database the dates have not changed. Below is the script that I am using, any help you can give me will help me keep my job (and pay for the beer I'm sure to need after work today) Thanks in advance for your help. I am trying to change the date '30/08/2007' to '28/08/2008'.
My Script:
update Main_table set valuedate=replace(valuedate,'30/08/2007','28/08/2008') where country='FR' and valuedate='30/08/2007'
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Hi, I am relatively new to this stuff. I am using Microsoft SQL Server Management Studio Express (9.00.1399.00) Can someone tell me the way to get a table and its content to another database (I use two at webhosts4life) Or perhaps a way to export the data of a table so I can do it at a later stage.
Is that at all possible with this program or do I have to use the non-express version?
Can anybody help me with the generation of Table of Contents for a report using SQL Server 2005 Reporting Services. Let me ellaborate. The scenerio is...i m having a report of lets say 500 pages grouped on employees, showing the performance of each employee between specific date range. Now if the manager prints the report of 500 pages he will be more intersted to jump directly to a perticular employee's page which means my printed report had to have a Table of Contents on my grouped criteria (which in this case is employee number). I would really appriciate if someone can suggest me a solution for this.
Using Sql Server 2005 Express and Management Studio I need to create a SQL insert statement for the contents of a table (FullDocuments) so that I can run the query on another server with that same table schema (FullDocuments) and the contents will automatically be inserted into the new instance of the FullDocuments table.In Management Studio I have used "Script Table as" for the create table query. The second instance of FullDocuments has been created on the remote server. Now how do I generate an insert query for the contents of FullDocuments so that the contents can be moved/inserted to the new instance of the table?Thanks for any help provided.
I am trying to perform a query in SQL that will merge the contents of various table, using search criteria to narrow down the results. All of the tables have the exact same fields, and the search criteria is the same for all of them. What I am looking for is the most efficient way to perform this. I am using SQL stored procedures, and passing the results into my code. Note that in my real-world scenario there are more than three tables.
1) Merge all the tables into one and then perform the search criteria: SELECT a, b, c FROM (SELECT a, b, c FROM Table1 UNION ALL SELECT a, b, c FROM Table2 UNION ALL SELECT a, b, c FROM Table3) AS MY_MERGE WHERE {SEARCH CRITERIA}
2) Perform the search criteria on each table and merge the results SELECT a, b, c FROM (SELECT a, b, c FROM Table1 WHERE {SEARCH CRITERIA} UNION ALL SELECT a, b, c FROM Table2 WHERE {SEARCH CRITERIA} UNION ALL SELECT a, b, c FROM Table3 AS MY_MERGE WHERE {SEARCH CRITERIA})
I'm copying date from a sql server 2000 table to a sql server 2008 R2 table using a Linked Server.I need to be sure the remote table is correct before processing it.I first delete all the remote table contents and after that, I insert into remote_table from local_tableWhat happens if the communication is interrupted during the insert?I mean, Can I be sure all records were copied just checking if record count > 0 ?
An application uses a database table having proprietary information. We do not want our customer to be able to look at that. This being a real-time application, performance can not be sacrificed. What is the best way to keep the table data non-viewable without sacrificing the performance?
i'm trying to figure out a way to replace a handful of "Illegal" characters in our SQL tables. We are using Project Server and some of these characters are causing errors or other issues in OLAP Cube.
I'm trying to figure out a way to change the following characters to an underscore ( _ ) :
illegal characters are: / ( ) . , ' : - &
Can I just create a SQL query that loops through a column to replace all of these characters at once? or do I have to replace one character at a time?! I tested replacing one at a time and it works with the REPLACE function.
I'm not all that familiar with MSSQL, I've spent past few years working with MySQL instead.
how can my query loop? or would using CASE help me out?
thanks. any feedback is much appreciated. If you need to know more, let me know.
I have a table that stores all the processed data from other tables. How can I replace the same data in this table when I do "reprocessing"? It's kinda like a combination of delete then insert kind of thing. I cannot simply insert as it will become duplicates. any idea?
I am using MSSQL 2000 and trying to debug a stored procedure that has a #temp table in it. I am using the .NET SQL debugger and I would like to display or view the contents of the #temp table while debugging. Does anyone know how to do this?
I have a large table containing about 800 million rows with an average row length of about 1K. The columns in the table are char columns. I need to move the contents of this table into a similar table where the target columns are varchar. The original table column definitions are compatible with the target table but the reverse is not necessarily true. For example, one column is being changed from int to bigint. The table is partitioned.
So, what is the fastest way to migrate the data. I was thinking to unload each partition into a flat file and load the target table running multiple load streams? Is this a good way?
I have a real table with an identity column and a trigger to populate this column.
I need to import / massage data for data loads from a different format, so I have a temp table defined that contains only the columns that are represented in the data file so I can bulk insert.
I then alter this table to add all the other columns so that it reflects all the columns in the real table. I then populate all the values so that this contains the data I need.
I then want to insert into the real table pushing the data from the temp table, however this gives me errors stating that the query returned multiple rows.
I specified all the columns in the insert grouping as well as on the select from the temp table.
ANY thoughts / comments are appreciated. This is beginning to drive me nuts.
Table: User name, field1 Jack 1000|1001|1003 Berg 2000|1001|2004 Paul 1001|1000 Jane 1001
Now, I would like to replace all "1001" with nothing, and also remove the "|"-separator behind 1001 if it exists, basically removing both "1001" and "1001|", so the resulting table looks like this:
name, field1 Jack 1000|1003 Berg 2000|2004 Paul 1000 Jane
My tries, been plenty, but here are some:
Code: UPDATE UserTable SET field1 = REPLACE(field1, '1001', '') UPDATE UserTable SET field1 = REPLACE(field1, '1001|', '')
But the above queries replaces field1 only if the whole field matches '1001' or '1001|'...
The above queries do work, just like I want them to. I just happened to write them in this order on this post... did not do a copy of the actual query.
Hi,I have some tables where I import data in, lots of field have gotten aNULL value which the application can not handle.Now can I replace each NULL value with '' in a columns with:update <tableset [<column>] = '' where [<column>] IS NULLBut because there are lots of columns this is pretty much work, alsothere are multiple tables.Is there an easy way to replace all NULL values in all columns in atable?Thanks in AdvanceBob
I'm trying to replace a particular part of a row in a table with a new value.
The row is called "DataPath" and it has a lot of values like so:
mashOperationsComponent Data FilesSantec
I want to run a query to replace the mash with our DFS namespace share name companysharesDepartments but keep everything else past the mash part of the original row.
I'm currently running this query, it says it is altering 30,000 rows, but it doesn't look like it's doing anything at all..
UPDATE dbo.Part SET DataPath = REPLACE(DataPath,'company.localsharesDepartments','mash') WHERE DataPath like 'mash\%'
So for example, it would change the mash above to
company.localsharesDepartmentsOperationsComponent Data FilesSantec
What are the options to create a table of contents based on the report items in a List Control? Document Mapping works for online viewing. A table of content would make the report easier to read when it's printed.
I have an application written in Visual Basic 2008, using a SQL Express 2008 database. This application runs in several locations on stand-alone PC's and there is no network connecting them. I have to maintain a SQL database in a central location an would like to be able to distribute updated tables from that central location by using an USB stick and replace the tables of the same name in the different locations with the updated ones on my USB stick. This I would like to do using code within my VB2008 application.
Can someone please help me on the way to achieve this. Is it possible to give some concrete code examples, as I am a newbie as a SQL Server user.
SELECT Gruppo_Assegnatario, [100] as stato1, [101] as stato2, [102] as stato3 FROM ( select
[Code] ...
That extracts only zeros (columns "stato1", "stato2", "stato3"):
Gruppo_Assegnatariostato1stato2stato3 SDB_BE Vita Antiriciclaggio0 00 SDB_BE Vita Assistenza clienti000 SDB_BE Vita Emissione000 SDB_BE Vita Gestione Rendite000 SDB_BE Vita Liquidazioni000
[Code] ....
Unlike the "SourceTable":
select CASE_ID_, Stato, Gruppo_Assegnatario FROM TicketInevasiPerGruppoEStato extracts
CASE_ID_ Stato Gruppo_Assegnatario HD0000003736734 AssegnatoSDB_GBS Variazione HD0000003736739 AssegnatoSDB_GBS Variazione HD0000003736743 AssegnatoSDB_GBS Variazione HD0000003736783 AssegnatoSDB_GBS Variazione HD0000003736806 SospesoSDB_BE Vita Selezione
[Code] ....
How can I get the right count in the first data mining replacing the zeros (columns "stato1", "stato2", "stato3")?
I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):
UPDATE dbo.target_table set target_column = case when source_column_1= 'ABC'then 'XYZ' when source_column_2= '123'then 'PDQ'
[Code] ....
The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.
The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.
What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
Hi All, I have a Problem while updating one table data from another table's data using sql server 2000. I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms) I need to update TableA with TableB's data using a single query that i have including in a stored procedure.