Hi,
I need to delete rows from my user tables dependant upon there non
existence from another table:
delete student
where student_id not in (select student_id from tblStudent)
The reasons is convoluted, simplest explanation is that our operational
system allows the change of business keys. This wreaks havoc in the
data warehouse.
So, I'm look for help on how I can delete rows from tables that have a
column STUDENT_ID. I'd like the script to search for the tables, then
perform the delete.
I don't know where information about user tables are stored, nor how to
loop through the results to do the delete.
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:
DELETE FROM sql2005.production.dbo.products WHERE vendor='Foo' AND productId NOT IN ( SELECT productId FROM sql2000.staging.dbo.fooProductList )
The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.
I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.
Does anyone have suggestions on what might cause this sort of behavior?
I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2 select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB] GO /****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
Stepping thru the code with the debugger shows the dataset rows being deleted.
After executing the code, and getting to the page presentation. Then I stop the debug and start the page creation process again ( Page_Load ). The database still has the original deleted dataset rows. Adding rows works, then updating works fine, but deleting rows, does not seem to work.
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); } /* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
I have SQL Server 2005 x64. I found in the studio I can generate allsorts of scripts, including the creation of indexes.I works, however the script contains the CREATE TABLE statements aswell. I want the scripts to recreate the scripts on another filegroup(testing a theory from previous post).Am I missing a switch in the wizard to exclude CREATE TABLE?I did create the script, but then I manually deleted all the CREATETABLE statements, which was a pain.ThanksRob
Hi all :)My apologies if I posted in the wrong groups, but I just jumpedin MS SQL waters, so any guidance will be appreciated.What I'm trying to do is the following process:[1] present operator with a web page (script)[2] once filled with db name and username, script will create..sql file[3] osql.exe will be called with, i presume, -i file.sql andcreate a databaseI have limited SQL knowledge, but I got the information fromEnterprise Manager when I ran 'All Tasks -Generate SQLScript' on how the .sql file should look like.I realized what are the commands that would create a fresh DB(I ran this for newly created DB), and figured my .php scriptshould create such a file.It's fairly basic, and I'm almost sure all of you know howoutputed .sql file looks like, but anyway here it is:Script is called with parameters 'six' as database name and'magarac' as user name:---------------------------------------------------------------IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name= N'six')DROP DATABASE [six]GOCREATE DATABASE [six] ON (NAME = N'six_Data', FILENAME = N'E:Databasepathsix_Data.MDF' , SIZE = 1, MAXSIZE = 20,FILEGROWTH = 10%) LOG ON (NAME = N'six_Log', FILENAME = N'E:Databasepathsix_Log.LDF' , SIZE = 1, MAXSIZE = 20, FILEGROWTH= 10%)COLLATE SQL_Latin1_General_CP1_CI_ASGOexec sp_dboption N'six', N'autoclose', N'false'GO....use [six]GOif not exists (select * from dbo.sysusers where name =N'guest' and hasdbaccess = 1)EXEC sp_grantdbaccess N'guest'GOif not exists (select * from dbo.sysusers where name =N'sinisam')EXEC sp_grantdbaccess N'magarac', N'magarac'GOexec sp_addrolemember N'db_owner', N'magarac'GO---------------------------------------------------------------I managed to get an exact replica of .sql file that EnterpriseManager created. It leads me to believe that this way ofautomated database creation is indeed possible.Really sorry for making you to go through all this text, butafter I get a green light on this from you guys, I'll have abit more problematic question.Is there any reason why this should not be used, or would fail?Thanks in advance :)P.S.Just as a heads-up, next part of my problem is automatedcreation of new MS SQL server login to use with new DB.
I have production SQL Server database that must be moved to a newmachine. There is a fairly complex DTS package on the original serverthat is used to handle the weekly updates to the database.Is there a way to export this DTS package in order to set it up on thenew machine as well?Best Regards,Warren WrightScorex Development Team
Does anyone know where to declare the target directory for scripted output from Enterprise Manager?
Every time I get EM to script Tables I want the output not to go to c:mssql7inn . . but somewhere else. Trouble is I can't find where to set this variable. It won't work in a shortcut - I've tried that, and I can't find an ini file either.
Seems like a simple problem to fix if you just know where to look !
Hi i have an web app demo that allows users to add info and change attributes within a SQL 2005 Express DB. I'd like to restore a clean copy of this database every couple of hours from a .bak file using a Windows scheduled task on the server. Has anyone got a .sql script for database restoration that i could use and call using a .cmd script file? Thanks.
FYI: I've posted this on a couple of forums and haven't gotten any response. I hope someone here can help since this is way past due.
First I'll give a little background on our situation.
Log Shipping and Replication are out, so I am scripting a backup locally, an xcopy to a remote box, and then a restore.
In the early stages of this, I'm trying to do 3 databases. 2 of them work fine alone. It's when I add the 3rd one that I have a problem. I noticed that in the 2nd stored procedure that I probably need to take out the WITH REPLACE if I'm dropping it beforehand as well. I don't have time to test it on this box until later tonight. I don't think that's the issue because it was doing the same thing before I added the drop. I'm overwriting the .txt file so I don't have the exact error that it's giving. I believe it's something similar to "Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation." I believe it also said [SQLSTATE 42000].
Now for the code. Props to Tara and the code she's put online.
Any help would be appreciated and I'll be glad to help answer questions related to what I've got.
CREATE PROC sp_backup_user_dbs3 AS SET nocount ON DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss DECLARE @cmd SYSNAME -- stores the dynamically created DOS command DECLARE @Result INT -- stores the result of the dir DOS command DECLARE @RowCnt INT -- stores @@ROWCOUNT DECLARE @DBName SYSNAME DECLARE @filename VARCHAR(200) -- stores the path and file name of the BAK file DECLARE @loglogical VARCHAR(1000) DECLARE @datalogical VARCHAR(1000) DECLARE @restoreData VARCHAR(255) DECLARE @restoreLog VARCHAR(255) DECLARE @backupFile VARCHAR(255) DECLARE @physicalNameData VARCHAR(255) DECLARE @physicalNameLog VARCHAR(255) DECLARE @physicalNameDataStripped VARCHAR(255) DECLARE @physicalNameLogStripped VARCHAR(255) DECLARE @ExecStr NVARCHAR(4000) DECLARE @strSQL VARCHAR(1000) DECLARE @restoreToDataDir VARCHAR(255) DECLARE @restoreToLogDir VARCHAR(255) DECLARE @path VARCHAR(100) SET @path = 'I:ackupMoveTo14'
--we need to delete all the old backup files from the I:ackupMoveTo14 folder -- Build the del command SELECT @cmd = 'del ' + @path + '*.BAK' + ' /Q /F' --PRINT @cmd EXEC master..xp_cmdshell @cmd, NO_OUTPUT
CREATE TABLE #whichdatabase ( dbname SYSNAME NOT NULL )
INSERT INTO #whichdatabase ( dbname ) SELECT [name] FROM master.dbo.sysdatabases WHERE [name] IN ( 'db1', 'db2') ORDER BY [name] -- Get the database to be backed up SELECT TOP 1 @DBName = dbname FROM #whichdatabase SET @RowCnt = @@ROWCOUNT -- Iterate throught the temp table until no more databases need to be backed up WHILE @RowCnt <> 0 BEGIN SELECT @filename = @Path + '' + @DBName + '.BAK' BEGIN backup log @dbname WITH truncate_only END
-- Backup the database BACKUP database @DBName TO disk = @filename
DELETE FROM #whichdatabase WHERE dbname = @DBName
-- Get the database to be backed up SELECT TOP 1 @DBName = dbname FROM #whichdatabase SET @RowCnt = @@ROWCOUNT -- Let the system rest for 5 seconds before starting on the next backup WAITFOR delay '00:00:05' END
DROP TABLE #whichdatabase SET nocount OFF BEGIN SET @cmd = '' SET @cmd = 'xcopy I:ackupMoveTo14*.BAK \RemoteServer /C /Y' EXEC master.dbo.xp_cmdshell @cmd END BEGIN
EXEC [RemoteServer].master..usp_restoreDbsFromDir2 END RETURN 0 GO
3rd Step(the code for the usp_restoreDbsFromDir2 on the remote server):
-- Get files sorted by date SET @cmd = 'dir ' + @restoreDir + '*.BAK /OD'
CREATE TABLE #Dir (DirInfo VARCHAR(7000) ) -- Stores the dir results CREATE TABLE #BackupFiles (BackupDate varchar(10), BackupFileName nvarchar(1000) ) -- Stores only the data we want from the dir CREATE TABLE #RestoreFileListOnly ( LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), FileGroupName nvarchar(128), [Size] numeric(20,0), [MaxSize] numeric(20,0) )
INSERT INTO #Dir EXEC master.dbo.xp_cmdshell @cmd
INSERT INTO #BackupFiles SELECT SUBSTRING(DirInfo, 1, 10), SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) FROM #Dir WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND DirInfo NOT LIKE '%<DIR>%'
-- Get the newest file SELECT TOP 1 @bkpFile = BackupFileName FROM #BackupFiles ORDER BY BackupDate DESC
SET @rowCnt = @@ROWCOUNT
-- Iterate throught the table until no more databases need to be backed up WHILE @RowCnt <> 0 BEGIN
SET @cmd = @restoreDir + @bkpFile
INSERT INTO #RestoreFileListOnly EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @cmd + '''')
--get the dbname from the bkpFile name --SET @strSQL = CHARINDEX('_db_', @bkpFile) --SET @dbname = LEFT(@bkpFile, @strSQL - 1)
SET @backupDisk = @restoreDir + @bkpFile SELECT @datalogical = LogicalName FROM #RestoreFileListOnly WHERE Type = 'D' SELECT @loglogical = LogicalName FROM #RestoreFileListOnly WHERE Type = 'L' SELECT @PhysicalDataPath = PhysicalName FROM #RestoreFileListOnly WHERE Type = 'D' SELECT @PhysicalLogPath = PhysicalName FROM #RestoreFileListOnly WHERE Type = 'L'
SELECT @strSQL = 'alter database ' + @dbname + ' set offline with rollback immediate' --alter database MyDatabase set offline with rollback immediate --PRINT @strSQL EXEC (@strSQL)
SELECT @strSQL = 'DROP database ' + @dbname --alter database MyDatabase set offline with rollback immediate --PRINT @strSQL EXEC (@strSQL)
Hi All,I am designing a purge process for a db that has grown to almost 200GB.My purge process will remove about 1/3 of the 500 million rows spreadover seven tables. Currently there are about 35 indexes defined onthose seven tables. My question is will there be a performance gain bydropping those indexes, doing my purge, and re-creating the indexes. Iam afraid that leaving those indexes in place will create a lot ofextra overhead in my delete statements by having to maintain theindexes. I know that it could take many hours to rebuild the indexesafterward, but I am planning on doing that anyway. The reason that Iwant to know whether I should drop the indexes ahead of time, is I maynot be able to do the entire purge at once and the tables may need tobe accessed between purges. If this occurs, I will need to have thoseindexes in place.So do I drop the indexes before the purge and re-create them later ordo I leave them in place and re-index them afterward?Thanks In Advancep.h.
Hi, I'm really not very good at SQL sadly, so would really appreciate any help. I'm basically working on a website that has a chatroom. I want it so the chatroom table only holds, say 15 lines of chat, then once a new line of chat is entered the last line in the table is deleted, so the table always has a maximum of just 15 rows. The fields are: MessageID Poster Message DateTime I've tried my best, but just don't know how to do it. I suppose it's something like: SELECT TOP 15 * FROM Chatroom ORDER BY MessageID DESC - this gives me the last 15 rows. Then I need a delete statement to delete the rest?! Sorry, I am very bad at SQL, so any help would be great. This is written in a stored procedure. Can the stored procedure pick up whether there are 15 or more rows in the table, and if so then delete all bar the newest 15 rows of chat? To summarise: I want a stored procedure that checks if 15 or more rows exist, if they do then delete all bar the newest 15 rows. Thanks, Ricky
there are 500000 rows that has projektID=5.. and when i run the query the hardrive is working for a couple of minutes and then stops. and NOTHING has happened. not a single row has been deleted?.. cant the DELETE statement handle that many rows or?. or is there another way i can delete these rows?.
Hii want to delete a row in my database but the problem is, i cant delete it as other table rows is linked to it. I have to delete all the rows thats linked to the row i wanna delete first.Is there a easier way to delete the row and all the rows thats linked to it? i wanna code it to do it.an suggestions?
CAN ANYBODY REPLY FOLLOWING QUESTIONS. I WANT TO DELETE DUPLICATE ROWS IN MY TABLE WITHOUT USING TRANSACTION TABLE. AND ONE MORE QUESTION HOW TO GET YESTERDAY DATE BY USING ISQL WINDOW.
In my table I have following current data. I want to delete duplicate records from table.I am trying following SQL statement, So I am getting desired output which is orange color in current data. I want to keep the desired output records and other all should be deleted. But complection is that in current data values in C5 and C6 are different.
select C1,C2,C3,C4,COUNT(*) from table_Delete group by C1,C2,C3,C4 having COUNT(*) >= 1 ORDER BY COUNT(*) DESC
declare @a1 table (id int not null identity(1,1), phone decimal(18,0), adress nvarchar(100)) insert @a1 (phone,adress)
[Code] ....
id phone address ----------- --------------------------------------- ---------------------------------------------------------------------------------------------------- 1 111 new york 2 111 new york 3 111 new york 4 222 maxico 5 222 mexico
select phone,count(phone) as say from @a1 group by phone having count(phone)>1
phone say --------------------------------------- ----------- 111 3 222 2
How can I remove duplicate phone. For example
after delete select*from @a1 select*from @a1
id phone address ----------- --------------------------------------- ---------------------------------------------------------------------------------------------------- 1 111 new york 4 222 maxico
I wrote this to show for example in my real table have 50000 rows and 1751 duplicate rows.
