I have a table that I need to delete some data from and put the deleted
data into a different table.
How do I script the following.
If Field1 in Table1 is null, remove that row from Table1 and put it in
a new table called Table2
Regards,
Ciarán
I have a comma delimited data file TestData.txt of the form:
123,asss,aweqrrr ,ssdsff , ,2wwwrwrr
434,sff,dgfdgd ,sffsfsfete ,sd ,dff
I want to load this data into SQL Server tables so that the data will be imported without the trailing spaces(blanks) after each data element .
Thus I will like to trim the data of the extra spaces (blanks) before import.
Desired data to be loaded :
123,asss,aweqrrr,ssdsff,,2wwwrwrr
434,sff,dgfdgd,sffsfsfete,sd,dff
Any help will be most welcomed
I am running a query on multiple tables and the data I get back consists of several repeated rows but with one column different. I want to take out those repeated rows and for the column that is different join that data and separate it by a comma. Can this be done?
Ex.
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 610
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 310
So i would like this data to come up as:
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717,610,310
I would like to remove data from a sqlserver database but keep the structure the the db. How would I do this?
View 9 Replies View RelatedSeems obvious but I can't see how. How would I remove columns from a data flow so that columns which have been used earlier but are not needed for insert/update are taken out of the flow.
I'm asking because the data ends up in a update statement and the flow has got so big it is unreadable.
Cheers, Al
I was wondering if someone could tell me how to use an SQL Function to remove everything from the mess of characters below except 'Test.' Really, any message could be substituted, so it's not as easy to leave everything except the 'Test.' string.
{
tf1ansiansicpg1252deff0deflang1033{fonttbl{f0fnilfcharset0 MS Shell Dlg 2;}{f1fnil MS Shell Dlg 2;}}
{colortbl ;
ed0green0lue0;}
{*generator Msftedit 5.41.15.1507;}viewkind4uc1pard x720cf1f0fs20 Test.f1par
}
It's RichText.
The fs20 could be fs24, fs25, etc. (any numbers). The end of the number would be the point to trim the beginning off.
The '' seems like it would be the point to start trimming the end off.
I've been experimenting with SUBSTRING together with CHARINDEX, but am just not getting even close to coming up with a solution.
Any help would be greatly appreciated
Thank you.
I have a table with several columns of information that I wish to set up some form of schedule to go through this data and remove any special characters that may interfere with other code processes.
Mainly the coma's and the apostrophes. It really messes with my asp pages and scripts when retrieving this information and trying to do other things with it, so I need to figure out how to remove these from the tables so it does not cause these issues.
Knowing this, I cannot figure out how to keep the data in the row/column and just extract the special characters from that data. The other problem is, everything I try requires me to insert either a coma or apostrophe as part of the code string which in lies my issue.
How can I parse through my data, leave the data as-is, but just get rid of coma's, apostrophes, and double quotes?
Does anyone have a basic example that I can use to expand on?
Hi there,
I have a large (420GB) database that has never had data archived off before. I taken a backup to a test server and run a script supplied by the product vendor which has removed a large ammount of old data no longer required.
I have checked within enterprise manager that this data has now gone, however the actual file itself has not shrunk in size. Is there a further step I need to take to get back the space.
Kind Regards
About a month ago I restored a data base that had been originally created in MSDE into a full version of SQL Server 2000. I thought that the size restriction would automatically be removed by putting it into the full version of SQL. I was wrong and about a week or so later the data base reached the 2GB size and needed attention. I received some assistance through Microsoft and they walked my client through the process of removing the size restriction. Since then everything has been OK.
Now I am doing something similar. My client has a data base that was originally created in MSDE. We upgraded them to SQL Express and the data base has now grown to 4GB which is the max that SQL Express allows.
We will be installing SQL for Workgroups this week.
My question is this. Is there a setting I must change inside the data base or in SQL for Workgroups that will allow the data base to grow beyond 4GB? We need to let it expand to whatever they need and I cant seem to find any documentation on whether or not I have to change a setting for this data base.
Thanks for your help. Jean
I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -
SELECT *
FROM
(
select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type
,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo
,n.note
FROM ih_cml_log_entry cml
[code]...
We have deleted 120GB of data but space did not released even after 2 days. Is there any reason for this? tell me how exactly it releases the space after truncating a 120GB table?
View 8 Replies View RelatedI have two tables one list changes of hospital ward and one lists changes of consultant doctor. These can change independently ie a ward change can occur without a consultant change and vice versa. I want to summarise these changes to give the status at each date_serial value.
create table #temp_ward_stay(date_serial bigint,ward_id varchar(10))
;
insert into #temp_ward_stay
values
(201501021200,'W1'),(201501031201,'W2'),(201501091200,'W3'),(201501161200,'W4'),(201501161201,'W5')(201501271200,'W3'),(201502101200,'W5')
;
create table #temp_consultant_episode(
date_serial bigint,
consultant_id varchar(10))
;
insert into #temp_consultant_episode values
(201501021200,'C1'),(201501031200,'C2'),(201501031201,'C3'),(201501091201,'C4'),
(201501121200,'C3'),(201501301200,'C6'),(201502111200,'C6'),(201502111201,'C7')
;
If I use:
select date_serial,consultant_id,null as ward_id from #temp_consultant_episode
union
select date_serial,null as consultant_id,ward_id from #temp_ward_stay
I get:
date_serial ward_id consultant_id
-------------------- ---------- -------------
201501021200 NULL C1
201501021200 W1 NULL
201501031200 NULL C2
201501031201 NULL C3
201501031201 W2 NULL
201501091200 W3 NULL
201501091201 NULL C4
201501121200 NULL C3
201501161200 W4 NULL
[code].....
whereas what I actually want is:
date_serialward_idconsultant_id
201501021200W1C1
201501031200W1C2
201501031201W2C3
201501091200W3C3
201501091201W3C4
201501121200W3C3
201501161200W4C3
201501161201W5C3
[code].....
I can see how I could remove the nulls where the date_serial values are the same:
select ce.date_serial,ward_id,consultant_id from #temp_consultant_episode ce
join #temp_ward_stay ws
on ce.date_serial=ws.date_serial
but I can't see how to move forward from here.
declare @error int, @rowcount int
select @rowcount = COUNT(1) FROMÂ STG_BCDR;
while @rowcount > 0
begin
 BEGIN TRAN Deletion
[code]....
Above code i try to delete records batch by batch to avoid table locking at BCDR table.total records in this BCDRÂ table is 40,000 records. Â However I run the code at execution plan, the BCDR table still clustered index scan which means that the locking still happend.
If i change the delete top (5000)...... to delete top (5).... then thre is clustered index seek, which is good..The problem here is  each time  only delete top 5 records which is means it will realy take very long time to remove those data.
how to cater the situation inorder for me to delete those huge data without table locking happend. If table locking happend , then other user will not be able to access this table at the same time.
It seems that there should be a solution for my situation, but for the life of me I can't seem to figure it out.
I need to compare two "like" tables, containing similar data. Tbl 1 is "BOOKED" (which is a snapshot of inventory) and tbl 2 is "CURRENT" (the live - working inventory table). If I write my query as follows the the subsequent result is "duplicate" data.
Code Block
SELECT booked.item, booked.bin, booked.quantity, current.bin, current.quantity
FROM BOOKED
LEFT JOIN
CURRENT
ON booked.item = current.item
No matter what type of join I use, there is duplicate data displayed for each table. For example, if there are more bins in the BOOKED table that contain a certain product then the CURRENT table will repeat data and vica versa.
As follows:
Item
Bin
Quantity
Bin
Quantity
12345
A01
500
A01
7680
12345
B01
6
A01
7680
12345
C01
20
A01
7680
54321
G10
1032
E15
1163
54321
G10
1032
F20
523
54321
G10
1032
H30
750
98765
Z20
7000
Z20
8500
98765
Y15
2500
Y15
3000
98765
X10
1200
Y15
3000
What I would like to do is display Bin and Quantity only once and the repeating values as NULL or [BLANK]. Or, to display all of the bins from both tables and only the quantities from each table in relation to the bin found in that table, returning a "0" if no quantity exists.
This is what I'm after:
Item
Bin
Quantity
Bin
Quantity
12345
A01
500
A01
7680
12345
B01
6
B01
0
12345
C01
20
C01
0
54321
G10
1032
E15
1163
54321
F20
0
F20
523
54321
H30
0
H30
750
98765
Z20
7000
Z20
8500
98765
Y15
2500
Y15
3000
98765
X10
1200
X10
0
Is this possible? If so, how?
I also might add that it is ok for each table to contain multiple entries for any given item. This is basically being requested as an inventory variance report - inventory before physical count and immediatly after physical count - and will only be run once a year.
-----------------------------------------------
Just thinking out loud here:
What if I created three subqueries, the first containing only BOOKED information, the second containing only CURRENT information and the third being a UNION of both tables? Something like this:
Code Block
SELECT q3.bin, q1.item, ISNULL(q1.quantity, 0) as QTY_BEFORE, ISNULL(q2.quantity, 0) as QTY_AFTER
FROM
(select item, bin, quantity
from BOOKED)q1
Left Join
(select item, bin, quantity
from CURRENT)q2
on q1.item = q2.item
Left Join
(select bin, item
from BOOKED
UNION
CURRENT)q3
on q1.item = q3.item
Order By q1.item
I don't know if I wrote the UNION statement correctly, but I will have to try this when I get back to work...
Any suggestions?
Hi everyone,
I'm trying to integrate SQLEXPRESS in a custom application ("the app"). On installation, a new instance ("myNewInstance") is created, and a new DB ("myDB") is created on that instance.
Upon de-installation of "the app", I remove the new instance. To that end, I'm calling the SQLEXPRESS setup like this:
Code Snippet
setup.exe /qb REMOVE=SQL_Engine INSTANCENAME=myNewInstance
The Problem: While the service and the instance are removed, the instance's data directory is not, and the DB Datafiles are still there. Now if I reinstall the app (re-creating myNewInstance), it uses the same directory structure, and when I try to re-create the DB, I get the following error message:
Code Snippet
Msg 5170, Level 16, State 1, Line 1
Cannot create file '[...]MSSQL.2MSSQLDATAmyDB.mdf' because it already exists. Change the file path or the file name, and retry the operation.
So here's the Question: is there
a) any way to tell setup.exe to completely remove the datafiles when uninstalling the instance, or
b) any way to tell TSQL to overwrite the old datafiles if they exist?
Thanks in advance,
Thorsten
Inside of stored procedure you have the code
create table #datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into #datatable
exec ('select *
from datatable
where id = 1')
select * from #datatable
I still want to retrieve the data and present it in the software's presentation layer but I still want to remove the temp table in order to reduce performance issue etc.
If I paste the code DROP
TABLE #datatable after
insert into #datatable
exec ('select *
from datatable
where id = 1')
Does it gonna work to present the data in the presentation layer after removing the temp table?
The goal is to retrieve the data from a stored procedure and present it in the software's presentation layer and I still need to remove the temptable after using it because I will use the SP many time in production phase.
[URL]
Hi! I am trying to remove an "*" I used to transfer a file from Excel to SQL. I put the "*" in the NULL values so that I would not get an error. Now that I have the file in SQL, I need to remove the :*" from some columns in my table. How can I select all of those in a column and delete them without deleting each individual one. Any ideas?
Thanks for your help.
Scott
Started with maintenance cleanup task set up bak and directory did not delete the files.
Then found this http://blog.solidsoft.com/blogs/richards_infrastructure_blog/archive/2007/07/12/369.aspx
Copy script
use master
declare @dt datetime
select @dt=getdate()-2
EXECUTE master.dbo.xp_delete_file 0,N'D:BackupAcctDB',N'BAK',@dt
Get message
Msg 22049, Level 16, State 1, Line 0
xp_delete_file() returned error 2, 'The system cannot find the file specified.'
Anyone else get problem with removing BAK files
I have a search box to look up using a number as an identifier. Currently my query is where x = @enteredNumber, but some place where a user might get the number it is padded with zeros. We can have them just see the number and retype it.
EG: 000000123 would be entered as 123.
But, I would like to let them copy and paste, but then strip the zeros (left padding only).
EG:
@num = stripPadding(@enteredNumber)
IE:
@num = stripPadding(000000123)
@num = 123
Hi,
I'm using SQL Server 7.0 SP3. I added a column to a table like so:
ALTER TABLE T704_RELATIONSHIP_INDEX_CLIENT_REPORT_STORE
ADD Universe_Member_Set_cd INT NOT NULL DEFAULT 1
What is the syntax for dropping the 'DEFAULT' from this column once it has been created? I've looked in BOL but can't seem to get the syntax correct.
Thanks in advance,
Darrin
I need to remove the duplicates from a table. If a record exits 4 times in a
table I need to delete 3 records and retain only one occurance of that.
I need a query to do this.
Can anybody help.
-Rajesh
Hi all,
I am using SQL 2k SP2 on Win 2K Advance server Cluster. My problem is how to remove builtinAdministrators login. Earlier when I tried it was doing failovers from one node to another and not stopping. I was not able to do anything. Any help is appriciated
thanks,
Minesh.
I wonder if one can give a hint on remove a DB suspect,
Mind you I have used SP_dbremove still did not remove the DB
This is on SQL 7.0 box
Any idea welcomed
Saad
Hello,
A few days ago one our database's default filegroup filled up. To solve the problem one of my colleagues created a new filegroup. This didn't solve the problem as no objects were placed on this filegroup and the initial one is still full. The idea was to expand the original filegroup over a new physical disk, not to create a new filegroup on that disk. Unfortunately, this is what happened.
To solve this I want to delete the new filegroup, but when I issue the command 'alter database PvgOmcsOds remove filegroup ternary' I get the message 'Server: Msg 5042, Level 16, State 7, Line 1 The filegroup 'ternary' cannot be removed because it is not empty.' Yet there're no objects placed on this filegroup.
How can I get rid of this filegroup?
Stef
I am trying to remove a transaction log that has grown too large for its databse and server. I have truncated the log and have run dbcc shrinkfile with the EmptyFile Option, but when I try to use alter database remove file and am I told that the object is not empty and cannot be removed. The server is in dbo use only.
What am I missing?
Hi all,
I am trying to remove one of the filegroups in the development database to shrink its size down. I started with emptying and removing its files and ran:
dbcc shrinkfile
(pubs_data_1,emptyfile)
go
and it ran fine, with 'DBCC execution completed' message
but when I tried :
alter database pubs
remove file pubs_data_1
go
I got 'pubs_data_1 cannot be removed because it is not empty'.
I even tried to go to each of the tables and delete from them first which didn't work, then drop all tables in that filegroup (I knew which tables are located on that filegroup), that didn't work either.
Any ideas? Is there a way to check what else sits on that filegroup?
Thanks
Hi,
We had transactional replication set up by a consultant with remote distributer, push subscription to the publisher (main production database). However, the subscriber machine had fatal crash and is unrecoverable. Now, replication jobs have been failing on distributor and we immediately need to disable and remove replication as it has started causing problems with transaction log backups on production database.
How can I completely remove this whole replication from publisher and distributor?
Please help.
Thanks,
Shaili
Dear Concerned DBAs,
I am wanting to pick someones brain and find out if there is anything I can do to successfully remove and reinstall SQL Server Version 6.5? I have already removed the software from the SQL Setup icon in ProgramsMSSQL Server 6.5 and went and removed the setup.exe and mssql files associated with the software from the server. Now when I attempt to reinstall the software back onto the server after a hard reboot, I do not get the option once the SQL Server Version 6.5 disk is placed into the CD-Rom to install SQL Server. The only options I get are upgrade, server configurations, etc.
Can anyone or everyone give me a hand as to where to take this dilemma other than rebuilding the server from the ground up?
Anticipatorily Yours,
Daimon Russell
Hello all,
I have a table named users. It consists of user names, user ids, etc... The problem is that whoever designed the ASP code before me allowed people to enter info in any format they want. This poses a problem because now the name can have 1, 2, or sometimes 3 spaces in between the last and first names. And sometimes the middle initial is used with a period following it. This creates problems when I am trying to execute a Select statement since it won't match if there are an unknown number of spaces in the string and throws an error when a period is used.
Is there a SQL query I can execute to change all the user_names into a format such as the following:
LastName, FirstName MiddleInitial
Like I said, it is already almost the same, just has too many spaces and some have periods after the middle initial
I have a members table and have added an extra few thoushand members to it. Now I need to remove the duplicates.
It doesnt matter which duplicate i remove as long as there are unique email addresses.
so here is the format of the table:
id
email
firstname
lastname
datebirth
if i do a:
SELECT COUNT(DISTINCT Email) AS Expr1
FROM Customer
it returns 21345
and
SELECT Count(Email)
FROM Customer
returns 28987
I can get the unique email addresses into another table by going:
SELECT DISTINCT emailaddress INTO DistinctCustomer
FROM Customer
but this will only return unique email addresses. How do i select distinct email address and all other fields into a new table? or just remove duplicates where email address appears more then once?
Thanks in advance
Tom the SQL beginner
i want to build a paratiotin which the key of the paratition will be a datre in this structure :
20080101235959
20080102235959
20080103235959
20080104235959
20080105235959
and so on.....
and i want that when there is a paration that it's "date" is more then 30 days to delete it (and that its data will be deleted too)
how do i do that?
thnaks in advance
peleg
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
Hi How do i remove the primary key for this table
create table table2
(col1 int,
col2 int,
primary key(col2))
Thanks
Vic
Afternoon all
What is the quickest way to remove the last two char from a colunm and leaving the rest of the colunm intact.
cheers