none
SQLCMD Insert Command

    Question

  • Hi,

    I need to run a SQLCMD which will Insert a value in one of the columns of a table based on a condition as mentioned below.

    SELECT id, name, ticket FROM table Where name like '%TICKET:23456%' Out-File C:\FILE.csv -Encoding ASCII

    Based on the id exported in the csv file, I would like to run a SQL INsert statement on the same table

    INSERT INTO table ( column1, someInt ) where id ='xyz' Values (23456)

    I am not able to construct the Insert statement and how it would read the id from the csv file and insert into the table.

    I am not sure if it is necessary to export it to a csv file, or if we can directly run the insert statement.

    I would appreciate any help on this matter.

    Thanks

    Saturday, September 07, 2013 10:44 PM

Answers

  • Hi PowerShellNovice,

    First, I want to inform that we need to provide the same columns in the INSERT statement as values specified in the VALUES clause. Regarding to the following commands:

    INSERT INTO tableName( column1, someInt ) where id ='xyz' Values (23456)

    If you want to insert 23456 into column1 (data type: int) where id ='xyz', please modify your codes as following:

     update tableName set  column1 = 23456 where id ='xyz'

    Additionally, you can refer to the following example:

    declare @tempTb table
    (
     col1 varchar(10),
     col2 varchar(10),
     col3 varchar(10)
    )
    insert into @tempTb(col1,col2,col3) values('xy','abc',null),('abc','xy',null);
    select * from @tempTb;
    declare @NewValue varchar(10);
    set @NewValue = 'New Data';
    update @tempTb
    set col3 = @NewValue
    where col1 like '%xy%'
    select * from @tempTb;

    Allen Li
    TechNet Community Support

    Monday, September 09, 2013 7:20 AM
    Moderator

All replies

  • how it would read the id from the csv file and insert into the table.

    You need to import the csv file into the database first, like a staging table.

    You can use bcp, BULK INSERT or SSIS:

    http://www.sqlusa.com/bestpractices/ssis-wizard/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    • Proposed as answer by Saeid Hasani Sunday, September 08, 2013 4:16 AM
    Sunday, September 08, 2013 1:57 AM
    Moderator
    • Edited by RSingh() Sunday, September 08, 2013 2:40 AM .
    Sunday, September 08, 2013 2:36 AM
  • Hi,

    I don't have to use an intermediate file. Both the tables are the same. Can you provide more details on the Where Statement. Also a bit of details on the INSERT statement.

    I only need to Insert data to one of the columns after doing a test.

    EG

    col 1, col2, col3

    xy, abc,

    If col 1 like xy, then insert data into col 3.

    Thanks,

    Sunday, September 08, 2013 5:48 PM
  • Hi,

    I don't have to use an intermediate file. Both the tables are the same. Can you provide more details on the Where Statement. Also a bit of details on the INSERT statement.

    I only need to Insert data to one of the columns after doing a test.

    EG

    col 1, col2, col3

    xy, abc,

    If col 1 like xy, then inser data into col 3.

    Thanks,

    Sunday, September 08, 2013 5:49 PM
  • Hi PowerShellNovice,

    First, I want to inform that we need to provide the same columns in the INSERT statement as values specified in the VALUES clause. Regarding to the following commands:

    INSERT INTO tableName( column1, someInt ) where id ='xyz' Values (23456)

    If you want to insert 23456 into column1 (data type: int) where id ='xyz', please modify your codes as following:

     update tableName set  column1 = 23456 where id ='xyz'

    Additionally, you can refer to the following example:

    declare @tempTb table
    (
     col1 varchar(10),
     col2 varchar(10),
     col3 varchar(10)
    )
    insert into @tempTb(col1,col2,col3) values('xy','abc',null),('abc','xy',null);
    select * from @tempTb;
    declare @NewValue varchar(10);
    set @NewValue = 'New Data';
    update @tempTb
    set col3 = @NewValue
    where col1 like '%xy%'
    select * from @tempTb;

    Allen Li
    TechNet Community Support

    Monday, September 09, 2013 7:20 AM
    Moderator