Generic BCP script

  • Can anyone point me at a script that will generically build up a script to use in BCP or SQLCMD? It will be just a select "column list" from "table" where "filter" type thing.

    Or would SSIS be better?

    Many thanks

  • So the script you want isn't a generic bcp format with its possible parameters

    Like this

    bcp AdventureWorks.Sales.Currency out "Currency Types.dat" -T -c

    You want a generic select...from...where so you can then put this into the bcp parameter file name???

  • Yes,

    Sorry I wan't clear. I want a script to build a SELECT statement, which I can then pass to a bcp command.

  • Again not sure if I follow...a 'shell' SELECT script is this

    SELECT [column1]

    ,[column2]

    ,[column3]

    ,[column4]

    ,[column5]

    ,[column6]

    ,[column7]

    FROM [dbname].[dbo].[tablename]

    Is this what you want, something very generic so you can populate later?

  • You could do something like this:

    declare @cmd nvarchar(4000),

    @server nvarchar(50),

    @database nvarchar(50),

    @table nvarchar (50),

    @username nvarchar(50),

    @Password nvarchar(50),

    @Columns nvarchar(500),

    @filter nvarchar(500)

    SET @server = N'MYDBSERVER'

    SET @database = N'DBName'

    SET @table = N'TableName'

    --If you do not supply a user name a trusted connection is used

    SET @username = N'test'

    SET @Password = N'test'

    SET @Columns = N'*'

    SET @cmd = N'SQLCMD -S ' + @server + ' -d '+ @database

    --If a filter is not specified it is not used in the query.

    --FILTER SHOULD BE LIKE MYCOLUMN NAME = VALUE AND MYCOLMN2 = VALUE

    SET @filter = N'MYCOLUMN = ''SOMETEXT'''

    IF @username IS NOT NULL OR LEN(@username) > 0

    BEGIN

    SET @cmd = @cmd + ' -U ' + @username + ' -P ' + @password + ''

    END

    ELSE

    BEGIN

    SET @cmd = @cmd + ' -E ' --TRUSTED CONNECTION

    END

    SET @cmd = @cmd + ' -q "SELECT ' + @columns + ' FROM ' + @table

    IF @filter IS NOT NULL OR LEN(@filter) > 0

    BEGIN

    SET @cmd = @cmd + ' WHERE ' + @filter + '"'

    END

    ELSE

    BEGIN

    SET @cmd = @cmd + '"'

    END

    EXEC Master..xp_CmdShell @cmd

  • ...keep in mind that unless you plan on picking and choosing columns, you don't need to create a select statement.

    In other words - if you want to just export a table, use

    BCP MyDatabaseName.MyUser.TableName OUT c:\myTextFileForOutput -T -c -S MyServerName

    No query needed.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • thank you all for your help. I have been working on a generic script, and trying to incorporate the suggestions given, but I think I am getting in a bit of a mess between bcp and SQLCMD.

    Which one should I use, and please could anyone clarify the correct syntax. Many thanks!

    This is what I am doing:

    declare @sql nvarchar(max),

    @colname as nvarchar(1000),

    @cmd nvarchar(4000),

    @server nvarchar(50),

    @database nvarchar(50),

    @filter nvarchar(500),

    @Targetfolder nvarchar(500),

    @TargetFileName nvarchar(500),

    @tablename sysname

    SET @server = N'server'

    SET @database = N'database'

    set @tablename=''

    set @TargetFolder = ''

    set @targetFileName = ''

    SET @cmd = N'SQLCMD -S ' + @server + ' -d '+ @database

    SET @filter = N'MYCOLUMN = ''SOMETEXT'''

    SET @cmd = @cmd + ' -q '

    declare names cursor for

    select 'dbo.' + name from sysobjects where xtype='u'

    and name = @tablename

    open names

    fetch next from names into @tablename

    while @@fetch_status = 0

    begin

    select @sql = 'SELECT DETAILREC FROM (

    SELECT -1 AS SORTKEY,

    ''H''+

    '',''+ convert (varchar(8) , GETDATE(), 112)+

    '',''

    +right(''0''+convert(varchar(20),datepart(hh,getdate())),2)

    +right(''0''+convert(varchar(20),datepart(mi,getdate())),2)

    +right(''0''+convert(varchar(20),datepart(ss,getdate())),2)

    AS DETAILREC

    UNION '

    declare cols cursor for

    select case when colorder=mincol then 'SELECT 100 AS SORTKEY,

    ''D'' +

    ' else '' end +

    ' '','' + '+

    case xtype

    when 52 then ' CONVERT( VARCHAR (20),ISNULL(' -- int

    when 56 then ' CONVERT( VARCHAR (20),ISNULL(' -- int

    when 167 then '''"'' + rtrim(' -- varchar

    when 231 then '''"'' + rtrim(' -- nvarchar

    when 175 then '''"'' + rtrim(' -- char

    when 58 then '''"'' + convert(varchar(20),' --

    smalldatetime

    when 61 then '''"'' + convert(varchar(20),' --

    datetime

    when 104 then '' -- bit

    else '' end + rtrim(name) +

    case xtype

    when 52 then ','''' ) )' -- int

    when 56 then ','''' ) )' -- int

    when 167 then ') + ''"''' -- varchar

    when 231 then ') + ''"''' -- nvarchar

    when 175 then ') + ''"''' -- char

    when 58 then ',112)+''"''' -- smalldatetime

    when 61 then ',112)+''"''' -- datetime

    when 104 then '' -- bit

    else '' end

    + case when colorder=maxcol then '' else ' + ' end +

    ' -- ' + rtrim(name) + char(10) + char(13)

    + case when colorder=maxcol then '

    AS DETAILREC' else ' + ' end

    from syscolumns AS SYSCOL CROSS JOIN ( SELECT MAX(COLORDER) AS MAXCOL

    FROM SYSCOLUMNS B

    WHERE B.ID=object_id(@tablename) ) C

    CROSS JOIN ( SELECT MIN(COLORDER) AS MINCOL FROM SYSCOLUMNS B

    WHERE B.ID=object_id(@tablename) ) D

    where id=object_id(@tablename)

    order by colorder

    open cols

    fetch next from cols into @colname

    while @@fetch_status = 0

    begin

    set @sql = @sql + @colname

    fetch next from cols into @colname

    end

    close cols

    Deallocate cols

    set @sql = @sql + ' from ' + @tablename

    - --print @sql

    select @sql = @sql + '

    UNION SELECT 999999999 AS SORTKEY,

    ''F,''+ CONVERT(VARCHAR(10), ( SELECT COUNT(*) FROM ' +

    '

    '+ @tablename + ' ) ) AS DETAILREC

    ) AS RESULTS ORDER BY SORTKEY

    go'

    fetch next from names into @tablename

    end

    close names

    Deallocate names

    IF @filter IS NOT NULL OR LEN(@filter) > 0

    BEGIN

    SET @cmd = @cmd + ' WHERE ' + @filter + '"'

    END

    ELSE

    BEGIN

    SET @cmd = @cmd + '"'

    END

    SET @cmd = @cmd + @sql + ' ' + @Targetfolder + '\' + @TargetFileName

    PRINT @cmd

    - --EXEC Master..xp_CmdShell @cmd

  • Are you trying to run a query or output the query results to a file?

  • I am trying t output the query to a text file

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply