Delete/Move Certain Info From Column - Leave The Rest?

Nov 10, 2005

I have a column that contains extra info that needs to be moved to another column or deleted alltogether. is there a way to select these items and move them to another column, leaving the rest of the data in the original column?

EXAMPLE

MYTABLE >COLUMN1
May have Data Like: ABCDE123 SER1 or XYZ12DEFSer1:1

WHAT I NEED TO DO IS
Move anything after the SER1 to a new column and retain the rest of the data in the original column. making it look something like this:

COL1 COL2
ABCDE123 SER1
XYZ12DEF Ser1:1

Another question is if there is a way to delete extra spaces? Like make all data that has two or more extra spaces, just single spaces and any additional spacing after a row of data, delete all additional spaces after the last letter/character/number.

One more question - What would be a great resource to learn MS SQL in more depth?

I am trying to learn ASP/VBScript/JavaScript/ and now MS SQL all at once b/c this is what my business depends on. Trying to be 5,000 hats at once can get confusing and overwhelming, so I am looking for any "Crash course" I can to learn as much, as fast as possible. Any direction or ideas?

Thank you.

View 4 Replies


ADVERTISEMENT

Delete Or Leave Msdb Tbls From Sql2k?

Apr 22, 2008

I did an in-place upgrade and I was wondering if I should get rid off tables in MSDB SQL 2005 carried over from SQL 2000?

Thanks.

View 4 Replies View Related

How Can I Keep Only The Top Ten Rows And Delete The Rest

Dec 17, 2007

I have a table that I would like to only keep the top 10 rows for each username. How can I kep the top 10 and delete the rest?

View 17 Replies View Related

How To Move Info From 2 Tables To 1

Apr 7, 2008

Hello all,

Ive been tasked with writing a SQL query to move information from 2 tables(old1 and old2) into 1(new). table new already has all the information from old1, but also has some additional columns that are encompassed from old2. Also some of the columns in table new need to be assigned a 1 or a 0 if the information is present in old2. Here is what I came up with:

UPDATE new

SET new.IsRentalLocation = 1
new.IsMainCampus = 1
new.IsLearningCenter = 1
new.IsStudentResource = 1
new.AlternateStateDisp = 0
new.Directions = tbl_old2.Directions
new.Catering = tbl_old2.Catering
new.Lab = tbl_old2.Lab

FROM new, old1, old2

WHERE new.CampusID = old1.CampusID
AND old1.LearningCenterID = old2.LearningCenterID

Does this look right? or should I be using an insert command?

View 4 Replies View Related

Move Info From A Table In One Db To Another

Mar 25, 2008

Hello All.

I just made some huge changes to a database on the development computer. However, I want to transfer all of the information from the Employees table in the old DB to the new DB (no changes made on that table).

Can anyone point me in the right direction?

View 4 Replies View Related

Delete/recreate Or Move Log Device

Apr 9, 1999

I need to remove log segment of one database from current position to another drive. The current log segment is used by two databases. This was created by mistake. Now we need to fix the problem and create another log segment for the other database. If we keep the log setment as is, we will have problems deleting one of the databases in the future.

Thanks for your help,
Ana.

View 1 Replies View Related

Transact SQL :: Instantaneously Move And Delete Data

May 6, 2015

I am using SQL Server 2012 Standard Edition. I have a requirement to instantaneously move data from 3 tables that are dependent on each other in a database to another database to same tables with same structure and dependencies.

I can set up replication to manage this. However the data once moved over has to be deleted from the source database immediately after the move. Hence replication is ruled out. Also data is continuously being insert into source database into those 3 tables.

I want to create a sql agent job that handles the data move and delete process and shcedule it to run once every minute. What is a best strategy to handle this without causing deadlocks in the source database?Below is the ddl and all objects in source database match the destination database the only difference is destination has 100 tables and source has only 3 tables which are shown below

CREATE TABLE [dbo].[StackPosition](
[StackPositionId] [uniqueidentifier] NOT NULL,
[AccountTriggerId] [uniqueidentifier] NOT NULL,
[StackPositionStatusId] [int] NOT NULL,
[QueuedAt] [datetime] NOT NULL,
[LastUpdatedAt] [datetime] NULL,

[Code] ....

View 3 Replies View Related

SQL Server 2012 :: Strategy To Delete / Move Millions Of Rows In A Database?

Apr 16, 2015

I am using SQL Server 2012 SE.I am trying to delete rows from a couple of tables (GetPersonValue has 250 million rows and I am trying to delete 50Million rows and GetPerson has 35 Million rows and I am trying to delete 20 million rows). These tables are in TX replication.The plan is to delete data older than 400 days old.

I tried to move data to new tables from the last 400 days and it took me like 11 hours. If I delete data in chunks of 500000 then its taking a long time to rebuild indexes(delete plus rebuild indexes 13 hours). Since I am using standard edition partition wont work.

find ddl below:

GO
CREATE TABLE [dbo].[GetPerson](
[GetPersonId] [uniqueidentifier] NOT NULL,
[LinedActivityPersonId] [uniqueidentifier] NOT NULL,
[CTName] [nvarchar](100) NULL,
[SNum] [nvarchar](50) NULL,
[PHPrimary] [nvarchar](50) NULL,

[code]....

View 1 Replies View Related

Try To Use Column Info Twice In VIEW In SQL 2005

Sep 15, 2007

Hi,
I keep running into the following Error message when I try to execute the query below. Can anyone please give me an idea of how I can solve this issue and keep my data??
 CREATE VIEW [dbo].[view_mCover]
AS
SELECT dbo.tProduct.intProductId AS dblCoverId, dbo.tProduct.strProductName AS strCoverName,strProductCode AS strCoverCode, dbo.tProduct.strProdPictUrl AS strCoverThumbnailSrc,
dbo.tProduct.intSubCategoryId AS dblCoverCategoryId, dbo.tProduct.strProdPictUrl AS strCoverSrc, dbo.tProduct.floatPrice AS floatCoverCost,
dbo.tProduct.intStockQuantity AS intProductStock, dbo.tProduct.isInDesignTool as isInDesignTool
FROM dbo.tProduct INNER JOIN
dbo.tCategory ON dbo.tProduct.intCategoryId = dbo.tCategory.intCategoryId
WHERE (dbo.tCategory.intCategoryId = 6) AND (dbo.tProduct.isDeleted = 0)



 ERROR message
===============
 Error Message: Column Name 'StrProdPictURL' appears more than once in  the result column list
 
Thanks in Advance...
 DollarJunkie

View 5 Replies View Related

IDENTITY Column (further Info Reqd)

Jun 14, 2002

Just a query, I have several tables that have bigint identity columns, I need to know what will happen when it reaches the upper limit. It will be most likely that earlier id fields have been deleted by the time it reaches the upper limit, so will it reuse numbers?

Cheers
Karyn

View 1 Replies View Related

Row Info Wanted In Column (like Cube)

Aug 26, 2004

Hi

I have a table called tblsample, where i have information stored row wise. Ther four quarter information is stored for many years. I want those information column wise for a given year.

say

select col1, col2 from tblsample where rqtr=1 and ryear = 2000
select col1, col2 from tblsample where rqtr=2 and ryear = 2000
select col1, col2 from tblsample where rqtr=3 and ryear = 2000
select col1, col2 from tblsample where rqtr=4 and ryear = 2000

i want information like

for the Year 2000

1 qtr 2 qtr 3 qtr 4 qtr


How to acheive this in MSSQL 2000

View 3 Replies View Related

Problems Querying Column Info

Sep 10, 2004

I am trying to print out column names and it seems i am doing something wrong. Any help??

View 2 Replies View Related

Problems Querying Column Info

Sep 10, 2004

I am trying to print out column names and it seems i am doing something wrong. Any help??

use DBI;
# use DBIx::DBSchema::Column;

#open connection to Access database
$dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver
(*.mdb);dbq=C:Tempdatabase.mdb');


# retrieving field names
$cursor = $dbh->prepare("SELECT * FROM combined_structures where 1=0");
$cursor->execute;
my($fieldNames, $i);
$fieldNames = $cursor->{"NAME"};
print "Field Names = ";
for($i=0; $i < @$fieldNames; $i++)
{
print "$fieldNames->[$i], ";
}
print "";

View 3 Replies View Related

Move Data From One Column To Another Column And Row

Aug 7, 2006

need some help converting a table which has one row for each year. Displaying the data so the each row will contain all of the information for the title and all of the years displayed in separate columns.

I have a SQL Querry which returns the data looking like this (the querry is grouping by Title and by year, then adding up the quantity of rows):

Basic Information needed for Life Insurance 25 2005
Basic Information needed for Life Insurance 45 2006
Ea Chairmans Conference 10 2005
EA Chairmans Conference Press Release 33 2005
EA Chairmans Conference Press Release 21 2006
EA Chairmans Inner Circle Press Release 16 2005
EA Chairmans Inner Circle Press Release 46 2006
EA Honor Ring Press Release 13 2005
EA Honor Ring Press Release 35 2006
EA National Conference Press Release 6 2005
EA National Conference Press Release 9 2006

I need this data displayed like this:

Title 2005 2006
Basic Information needed for Life Insurance 25 45
Ea Chairmans Conference 10 10
EA Chairmans Conference Press Release 33 21
EA Chairmans Inner Circle Press Release 16 46
EA Honor Ring Press Release 13 35
EA National Conference Press Release 6 9


What I want to do is to basically say:

If Row 1 Title = Row 2 Title and Row 1 Year = Row 2 (Year + 1)) Then

add to grid Row 1 Title, Row 1 Qty, Row 2 Qty

Skip Row 2 and go to Row 3 (repeat this step until end of data)

End

Is there any way in the SQL querry to retrieve this data in this format so that I can display it in a datagrid?

Is there any way to display the data in this pattern.

Below is the code I am using now, but since the moving of the data is done in code it is very slow.
****************** Global Variables

Dim dbName As String = "Region_Web_Page_Hits"
Dim ColumnCount As Integer




Function ReturnColumnHeaderAndHitsDataSet() As System.Data.DataSet
'******** This will bring back the column Headers and Gross Hits per Column
Dim ColumnHeaderAndHitsQueryString As String = "SELECT DISTINCT TOP 100 PERCENT " & _
"YEAR(dat_Date) AS str_Year, COUNT(str_PageName) AS int_YearHits " & _
"FROM dbo.tbl_web_page_hits " & _
"WHERE (str_URL LIKE N'%press_Release%') " & _
"GROUP BY YEAR(dat_Date) " & _
"HAVING (Not (Year(dat_Date) Is NULL)) " & _
"ORDER BY YEAR(dat_Date)"

Dim ColumnHeaderAndHitsDataSet = LoadDataArray(ColumnHeaderAndHitsQueryString, dbName)
Return ColumnHeaderAndHitsDataSet
End Function


Function ReturnTableDataSet() As System.Data.DataSet
'This query string will return the data needed to display the report.
Dim TableQueryString As String = "SELECT TOP 100 PERCENT " & _
"str_PageName, COUNT (str_PageName) AS int_Hits, YEAR(dat_Date) AS str_Year " & _
"FROM dbo.tbl_web_page_hits WHERE str_URL LIKE '%Press_Release%' " & _
"GROUP BY str_PageName, YEAR(dat_Date) ORDER BY str_PageName"
Dim TableDataSet = LoadDataArray(TableQueryString, dbName)
ColumnCount = TableDataSet.Tables(0).Columns.Count
Return TableDataSet
End Function

'******* Function to Load the Data Array
Function LoadDataArray(ByVal queryString, ByVal dbName) As System.Data.DataSet
Dim ConnectionString As String = ConnectionStr(dbName)
Dim sqlConnection As SqlConnection = New SqlConnection(ConnectionString)
Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCommand)
Dim dataSet As DataSet = New DataSet
dataAdapter.Fill(dataSet)
sqlConnection.Close()
Return dataSet
End Function



Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

If Not Page.IsPostBack Then
Dim Sort_Field As String = ""
dg_Data.DataSource = QueryMethod(Sort_Field)
dg_Data.DataBind()
End If
End Sub

Currently I have a bunch of Classic ASP code which puts the data in to an array and then into a table with a series of if statments, but it is really slow.

I want to bind it to a data grid and if possible let the SQL statement format the data for me.

View 1 Replies View Related

Delete Rows In One Table By Referencing Another Table Info

Sep 16, 2004

I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.

Here is a basic breakdown of what I am trying to do:

Table1 (the table where the rows need to be deleted from)
Column_x (Holds the id that is unique to the various rows of data - User ID)

Table2 (Holds the user information & has the associated ID)
Column_z (holds the User ID)

I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.

Code:


SELECT Column_z
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z <> Column_x


This did not seem to do what I needed, it did not delete any rows at all.

I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2

Then I tried another scenerio that I also needed to do:

Code:


SELECT Column_z, Column_a
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z = Column_x AND Column_a='0'



'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.

Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.

Any ideas on what I am doing wrong here?

View 3 Replies View Related

Updating Column With Info - String Or Binary Data Would Be Truncated

Jan 29, 2015

I wrote this query to pull some information involving 3 tables dbo.NewFamNbrs$, dbo.CGIItemMaster and dbo.CGIFamilyMaster. In addition I want to perform an update statement to update column dbo.CGIItemMaster.FamilyIDX with the information of dbo.CGIFamilyMaster.IDX. I also know that truncation is indicating that I'm trying to insert data into a field not large enough. So I performed a len() function on the both columns and order by desc and they both came up with 4 as being the max.

Query:

SELECT a.PART, a.Family, b.FamilyIDX, c.FamilyID, c.IDX
FROM dbo.NewFamNbrs$ a LEFT OUTER JOIN dbo.CGIFamilyMaster c ON a.Family = c.FamilyID LEFT OUTER JOIN dbo.CGIItemMaster b ON a.PART = b.PART

Results of query:

PART Family FamilyIDX FamilyID IDX
000127233TF011468 TF01 506
000129880TF011468 TF01 506
003110 MET061468 MET06 1915

UPDATE STATEMENT:

begin tran
update dbo.CGIItemMaster
set FamilyIDX=c.IDX
FROM dbo.NewFamNbrs$ a LEFT OUTER JOIN
CGIFamilyMaster c ON a.Family = c.FamilyID LEFT OUTER JOIN
dbo.CGIItemMaster b ON a.PART = b.PART

View 2 Replies View Related

Transact SQL :: How To Get Info Which Stored Procedure Updated A Column For Particular Timestamp

Jul 31, 2015

How to get the details of a stored proc or sql query which updated a particular table for specified time stamp or interval. Is there any query to get this?

View 3 Replies View Related

How Leave Db With 1 Ldf While It Was Created With 2 Ldf ?

Jan 18, 2002

I want to remove 2 big ldf files (400 mgb and 300 mgb) and start db with only one ldf.
I tried sp_attach_db specifing only mdf file but geting errors
-------
microsoft help PRB: sp_attach_single_file_db Does Not Work for Databases with Multiple Log Files (Q271223)
..... You cannot attach a database that has been created with multiple log files without also attaching all the log files.
----

how can I remove those big ldf files and create new ldf (defult(1mgb)

View 1 Replies View Related

Leave DB Connection Open

Mar 20, 2008

Hello, I have always closed my db connections asap after opening them and have always thought this was the best practice.  But now I am wondering if I can just leave the connection open.
I created a windows service which runs on a timer and executes every 5 seconds.  The problem is that the code takes longer than that to process and the code may be executing three times at once. Im worried that since I use the same connection each time the code executes that a connection opened by the first run through may be closed or reopened by the second or third running of the code resulting in an error.  The connection is opened and closed about 10 times each time the timer event fires.  Should I open the connection once and leave it open when the service is started and close it when it is stopped or on any error?
 
Thanks

View 2 Replies View Related

How To Move From One Column To Another Using Sql Query With Asp Programming

Aug 29, 2006

I have a table A I have Five columns and Each consist of Five rows of textcolumn 1column 2column 3column 4column 5Thru Sql coding i want to move from one column to another column - When Date Changes I need ASp and sql code for this Or ATleast i need sql query for how to move one column to another

View 1 Replies View Related

Can I Move Some Column Position By Using SQL Command ?

Jun 1, 2007

I created SQL table follow by XSD fileAnd when any users added new column to XSD in ordinal position = 3 But after my program successfully created new column, its position is the last position  What can I do ???? I suspect  why I can't set it (I've looked for solution on MSDN already)even though We can see ordinal position bythis query  SELECT *FROM INFORMATION_SCHEMA.Columns   What can I do for solving ???? Help me please 

View 1 Replies View Related

Move Data From One Column To Another In Same Table

Sep 12, 2004

Okay, after I got everything imported, I found that a few thousand columns had "Shifted" on me. So now I am trying to "Shift them over" to where they need to be.

I did this:
INSERT INTO dbo.TABLENAME (COLUMN_NAME_TO_BE POPULATED)
SELECT COLUM_NAME_OF_INFO_TO_BE_MOVED
FROM dbo.TABLENAME
WHERE MFG = 'MANUFACTURER_NAME' AND PN LIKE '8888888888'
GO

I populated the PN column with 8888888888 to use as a reference point, and the MFG column already was populated with the correct name, so I was using those as my unique reference points.

Other columns that have the same MFG name are correct, so I have to use two unique identifiers to specify the actual data that needs to be moved.

It inserted 'NULL' in the whole table after I ran it in the Query Analyzer. It appeared to disregard the WHERE statement all together
Any ideas on what I am doing wrong here?

Is there a way to move the data from one column to the next one over by specifying other WHERE criterias?

View 1 Replies View Related

Move Data Basedn On X Amt Of Digits In Column

Nov 8, 2005

I am looking for a solution to move all Part Numbers for a specific manufacturer that is 9 digits long to a new column.

Let me explain............

I have a product table that has three columns of product codes associated with that product (Part Number as PN, Series as Series, and Industry Code as ICDE). I now want to create a fourth column (Manufacturer Code as MCDE) as my database has grown for another product specific numerical designator that is specific for one manufacturer.

Currently I have the specific 9 digit codes in the same column as the Part Numbers, though they all have their specific rows (Part Numbers & Manufacturer Codes are not in the same cell).

Now the Part Numbers have various numbers, letters, and special characters, but the specific 9 digit manufacturer codes are pure numbers.

So my question is............

How does one go about moving these specific 9 digit codes to their new column and out of the Part Number column?

They will all be exactly 9 digits, no special characters, no letters, no spaces.

Is there a way to tell MS SQL to just move anything for that specific manufacturer that has the 9 digit manufacturer numbers to the new column, bypassing anything with letters, special characters or any part number that is not a pure 9 digit number?

View 2 Replies View Related

Move Column Of Data To Another Table With A Join

Oct 12, 2013

I have two tables. They both have an identity field to join them together. One of the tables has a column of data that I want to put in the other table. I need to make sure the data is brought over using the join so it updates that column for the right record.

I am struggling with this. Should I use a update or an insert? I'm leaning toward update but I can't figure out how with the join.

This is what I have tried so far:

Update grpcon.GroupID = groups.GroupID
from grpcon Inner Join groups
on grpcon.GroupNum=groups.GroupNum

View 1 Replies View Related

At What Throughput Should I Leave A Connection Open ?

Feb 3, 2005

I am used to writing applications that hit the database "every so often" and am happy with opening and closing the connection to SQL Server for each one.

I am now writing an application that monitors a table where rows are written to it by a 3rd party application at possibly several rows per second. My job is to "pickup" those rows, analyse the data and move them to different tables. This will be done with a timer which is currently set to tick every second.

My question is: At what stage should I start to think about keeping open a permanent connection to the database ?

1 row per second ?

100 rows per second ?

Any suggestions appreciated.

Steve.

View 2 Replies View Related

How To Delete Data When Column Have Depency With Other Column?

Dec 2, 2007

in Table A Column is 
PriKey             No       Name
1                      1        Apple
2                      2       Orange
3                      3        Juicy
in Table B column is
Prikey             ColumnA           Price
1                         1                     10
2                          3                     2
3                          2                     5
 
TableA.Prikey have Depency with TableB.ColumnA    
 
when I am trying to Delete data from Table A , I got error message becaue the depency
how to delete data when there have depency?
 
I just know when table have trigger , we can disable trigger before delete Data, and enable trigger when data deleted
does there have a way to disable depency and then enable ?
 
thank you

View 14 Replies View Related

Is There A System Table With Timestamp Info Or DTS Job Info?

May 7, 2007

I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.

ddave

View 3 Replies View Related

Does SQL Server Move Records Between Partitions When Updating The Partition Key Column?

Apr 4, 2007

If I run an UPDATE query on a table which is partitioned by the column I am updating - will the records be moved to another partition?



ie. I have a table where Historical bit column marks whether a particular record should go to Partition1 (=0) or Partition2 (=1). Now, I update a record in that table and change the Historical column value from 0 to 1. What happens with that record?

View 3 Replies View Related

How To Leave The Focus In A Control When Click In Any Part Of The Form.

Jul 2, 2007

Hi there,

I have a form with textbox, combos, ect... I'd like to know how I can make to leave the focus of one of this control when i click in any part of the form that is not a control, and so it's not focusable.



cheers.

View 1 Replies View Related

List Causes Adobe To Leave Blank Page When Exporting

Jan 11, 2008

Hi,

My report page settings are as follows

page width 28cm
page height 21cm

all margins 1cm

My report when rendered to the screen outputs the correct number off pages...HOWEVER when i export it to adobe i get a trailing blank page which accompanies each page break.

Can anyone please help ?

My report has a table which is bounded to a list with separate fields at the top of the list specifying the username in a text field and the table displays the user details, For each user there is a page break.

I have notice when i got rid off the list everything is ok, however the list seems to be within the boundaries of the report

View 5 Replies View Related

Is It Bad Practice To Leave A SqlCeConnection Open Through The Duration Of A Mobile Application?

Jan 24, 2008

I know that with traditional SQL systems, it is important to only open connections to a sql server when they are needed. However, since there is no "server" in mobile apps, is it bad practice to leave one open throughout the duration of an application?

The application is going to be constantly reading and writing to the data tables and it seems like it might be a good idea to leave it open.

Thanks!

View 1 Replies View Related

SQL: How To Display Top 5 Then Sum The Rest

Apr 6, 2006

Hello,I would like to query the top 5 best companies' sales (total sales),then total the rest, what is the quickest and effective SQL to queryit?Thanks in advance

View 8 Replies View Related

Analysis :: TOP N Plus Rest

Nov 17, 2015

I need to create a dynamic top count of customers, plus the rest. I've seen many posts on this, however the difference I want is that the rest needs to be expandable to show what customers actually make up the rest category. For example:

Top

Cust1
Cust5
Cust2
Rest
Cust3
Cust4

I was thinking potentially the top/rest could be a shell dimension and then scope on these members to show the top and rest, however I haven't been able to achieve this.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved