none
Delete entry from a table using SQLCMD from command line

    Question

  • I am trying to run a batch file and include the following SQL command to delete a user account on multiple servers (without having to logon to SQL or the Application itself):

    sqlcmd -d DatabaseName -Y 35 -Q "set nocount on; declare @UserName varchar; delete from dbo.UserTable where @UserName = 'first.last''"

    It was not running the query at all until I finally declared the @UserName variable (have tried to set it to both varchar and char) and now it does not throw an error message about not declaring the variable and looks like it's running, however, the user still remains in the table. I have very limited SQL knowledge- does anyone have any advice? Thanks in advance!

    Thursday, October 11, 2012 9:25 PM

Answers

  • Hi Lindsey66,

    With the following command:

    declare @UserName varchar; delete from dbo.UserTable where @UserName = 'first.last''

    The condition

    @UserName = 'first.last''

    is false, so it won’t delete any records from the database. As Rick suggested, we can use a stored procedure or cascade delete to delete all the related records at once, for example:

    create table UserInfo
    (
    	ID int primary key,
    	UserName varchar(64)
    )
    
    create table UserAddress
    (
    	ID int primary key,
    	Address1 varchar(100),
    	UserID int foreign key references UserInfo(ID)
    )
    
    -- Method 1: on delete cascade
    insert into UserInfo values (1,'first.last');
    
    insert into UserAddress values (1,'a_Address1',1);
    
    alter table UserAddress add foreign key (UserID) references UserInfo(ID) on delete cascade
    
    delete from UserInfo where UserName = 'first.last'
    
    select * from UserInfo;
    select * from UserAddress;
    
    -- Method 2: Stored Procedure
    insert into UserInfo values (1,'first.last');
    
    insert into UserAddress values (1,'a_Address1',1);
    
    create proc DeleteUserInfoRecord
    (
    	UserName varchar(64)
    )
    as
    declare @UserID int;
    select @UserID=ID from UserInfo where UserName = @UserName;
    delete from UserAddress where UserID = @UserID;
    delete from UserInfo where UserName = @UserName;
    go
    
    exec DeleteUserInfoRecord 'first.last'
    
    select * from UserInfo;
    select * from UserAddress;
    


    Allen Li

    TechNet Community Support

    Wednesday, October 17, 2012 2:09 AM
    Moderator

All replies

  • Hi, when you declare a varchar variable, you need to supply the length or it will default to storing a single character.  In your example, you will want to update declare @UserName varchar to include a length sufficient to store the longest potential 'first.last' value.

    Example showing varchar vs varchar(100).  Run this in SSMS to see the difference.

    declare @UserName varchar
    select @UserName = 'first.last'
    select @UserName  -- returns 'f'
    
    declare @UserName2 varchar(100)
    select @UserName2 = 'first.last'
    select @UserName2  -- returns 'first.last'
    
    Thanks,
    Sam Lester (MSFT)

    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Deepak DBA Friday, October 12, 2012 3:43 AM
    Thursday, October 11, 2012 9:45 PM
    Moderator
  • Hi and thank you so much for your reply! I definitely see what you are talking about by needing to declare the length. So I have set the variable as how it was actually listed in the column of the table itself:

    sqlcmd -d DatabaseName -Y 35 -Q "set nocount on; declare @UserName nvarchar(64); delete from dbo.UserTable where @UserName = 'first.last''"

    It seemed to run again but the entry is still not gone! My question is that do I need to include every entry of that user to delete? for example, @First, @Last, @Userid, etc? If I only include one entry would that not be enough to delete the user?

    Thanks again for your help!

    Friday, October 12, 2012 12:50 PM
  • Actually I tried to run this in SQL command studio:

    DELETE FROM  dbo.UserTable
    WHERE UserName='first.last'

    Now it's letting me see the error message- apparently there is a FK in another table which is why it can't be deleted. Is there a way to query the user to find the "UserID", then join both tables together, then delete the entry from both tables? I would just try to delete by the UserID, however, the UserID is different for each user accross the servers.

    Friday, October 12, 2012 1:54 PM
  • The usual reason for designating a column such as UserID as the primary key of the table, is because you can easily get two users with the same first and last names.

    You can find out the UserID with a query such as SELECT UserID FROM dbo.UserTable WHERE UserName = 'first.last'; but that might return more than one row. So, I'm thinking that you most likely already know the UserID, somehow. That is, customers gave it when they connected or something. It would be more reliable to get the UserID directly from an authoritative source.

    Also, the CREATE TABLE statement (or ALTER TABLE statement for an existing table) has a clause named CASCADE that is intended for your situation. It must be used carefully to avoid mistakenly deleting rows. See http://msdn.microsoft.com/en-us/library/ms188066.aspx for more info.

    Personally I would consider creating a stored procedure in the database that validates the users actions, and can block the action if the user is deleting an unexpected number of rows from either table. Then the sqlcmd code just executes the stored procedure.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, October 12, 2012 3:10 PM
    Answerer
  • Hi Lindsey66,

    With the following command:

    declare @UserName varchar; delete from dbo.UserTable where @UserName = 'first.last''

    The condition

    @UserName = 'first.last''

    is false, so it won’t delete any records from the database. As Rick suggested, we can use a stored procedure or cascade delete to delete all the related records at once, for example:

    create table UserInfo
    (
    	ID int primary key,
    	UserName varchar(64)
    )
    
    create table UserAddress
    (
    	ID int primary key,
    	Address1 varchar(100),
    	UserID int foreign key references UserInfo(ID)
    )
    
    -- Method 1: on delete cascade
    insert into UserInfo values (1,'first.last');
    
    insert into UserAddress values (1,'a_Address1',1);
    
    alter table UserAddress add foreign key (UserID) references UserInfo(ID) on delete cascade
    
    delete from UserInfo where UserName = 'first.last'
    
    select * from UserInfo;
    select * from UserAddress;
    
    -- Method 2: Stored Procedure
    insert into UserInfo values (1,'first.last');
    
    insert into UserAddress values (1,'a_Address1',1);
    
    create proc DeleteUserInfoRecord
    (
    	UserName varchar(64)
    )
    as
    declare @UserID int;
    select @UserID=ID from UserInfo where UserName = @UserName;
    delete from UserAddress where UserID = @UserID;
    delete from UserInfo where UserName = @UserName;
    go
    
    exec DeleteUserInfoRecord 'first.last'
    
    select * from UserInfo;
    select * from UserAddress;
    


    Allen Li

    TechNet Community Support

    Wednesday, October 17, 2012 2:09 AM
    Moderator
  • Thank you to all who replied with help to my problem! Allen, I will try out your script and let you know how it goes. THANK YOU!
    Thursday, October 18, 2012 8:27 PM