AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATED

  • Comments posted to this topic are about the item AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATED

  • Shouldn't it be CHAR(13) + CHAR(10) (i.e. CR + LF)?

    http://en.wikipedia.org/wiki/Newline

  • if i execute the [dbo].[_SPLOG_AutoGenStatements] with different tables and option i get allways

    this error msg.

    Meldung 208, Ebene 16, Status 0, Prozedur _SPLOG_AutoGenStatements, Zeile 177

    unknown Objectname '#sp'.

    is there something missing in your script? ... creation of this "#sp" temp table?

    i have nothing found .. pls send me a reply.

    thx

    Sincerely Lars

  • RE: CR/LF/NewLine (Cade Roux)

    Actually, with Transact-SQL, it's even easier, just embed the newline in your string. Try this in your Management Studio/Query Analyzer

    SELECT 'This is a string

    with some

    embedded new line characters'

    AS Message

    (have output to text to see it properly).

    Unlike most programming languages, SQL doesn't require strings to be terminated on the same line, and the newlines that are embedded in the string are preserved. (I know C-based languages have better multi-line support for strings, but I don't think any of them treat the embedded new lines the way that SQL does).

    /Ryan

  • ryan.price (11/23/2008)


    RE: CR/LF/NewLine (Cade Roux)

    Actually, with Transact-SQL, it's even easier, just embed the newline in your string.

    Yeah, I use that occasionally. I would prefer if there was a \r escape sequence or something, though. (I usually DECLARE @CRLF and SET @CRLF = CHAR(13) + CHAR(10) when I need that)

  • I went through a similar exercise not long ago...

    I haven't actually run the code from this article, since I'm quite happy with mine :P, but looked through it and have a couple of general comments/suggestions (more to do with the SQL than the auto-generation logic)

    1) lose all the CHAR(10) + CHAR(13) stuff, and use embedded linefeeds in your constants (see my other reply). it'll make it much easier to read and maintain (once you can get your head around the fact that you can actually do it - it took me a while).

    2) if you're using this on SQL 2005 or later, you can switch all your varchar(8000)s for varchar(MAX)s, and the 8000 character restriction will go.

    3) you should probably declare your '@Tablename' as nvarchar(128) or sysname - which is what SQL stores them as. Even if 50 already seems extremely long for a table name...

    4) nchars and nvarchars have a length that is half the 'datalength' that you get from sys.columns (I may have missed that in your code).

    5) rather than use a multi-line CASE..WHEN..WHEN..WHEN construct, you could use an 'IN' statement instead e.g.: select case when 'a' in ('a','b','c') then 'yes' else 'no' end.

    /Ryan

    P.S. just for interest' sake, I'll attach the SQL 2005+ code for a view and udf that I use for generating the 'metadata' in my equivalent sproc. In my database, I've left the view there permanently, but there's no reason why it couldn't be used as a temp table-populating query inside a sproc.

  • lars (11/23/2008)


    if i execute the [dbo].[_SPLOG_AutoGenStatements] with different tables and option i get allways

    this error msg.

    Meldung 208, Ebene 16, Status 0, Prozedur _SPLOG_AutoGenStatements, Zeile 177

    unknown Objectname '#sp'.

    is there something missing in your script? ... creation of this "#sp" temp table?

    i have nothing found .. pls send me a reply.

    thx

    Sincerely Lars

    Thought I'd better try the procedure before I rate the article.... and I got the same problem....

  • perhaps your particular login does not have permissions top create temp tables. instead of using #sp create a table perhaps to accomodate the contents of #sp and modify the code appropiately...

    something like this:

    CREATE TABLE sp

    (

    spid BIGINT IDENTITY(1, 1),

    sptext TEXT NOT NULL,

    spname VARCHAR(100) NOT NULL

    )

    then use the find and replace function to replace #sp with sp

  • Thanks for the suggestions. i am actually not that experienced in sql and your suggestions have helped me advance my skill. thanks. also please see the post about the temporary table. as a matter of interest the code that creates the temp table is in _SPLOG_SPConstructor

    CREATE TABLE #sp

    (

    spid BIGINT IDENTITY(1, 1),

    sptext TEXT NOT NULL,

    spname VARCHAR(100) NOT NULL

    )

  • -- Description:Builds an insert-select statement from two table object_ids for a pair of temporary tables

    CREATE

    Function [dbo].[gen_InsertSelect] (@From_Object_Id int, @Into_Object_Id int) returns nvarchar(max)

    AS

    BEGIN

    DECLARE @SQL nvarchar(max);

    DECLARE @Into_TableName nvarchar(max)

    DECLARE @From_TableName nvarchar(max)

    DECLARE @Cols TABLE ([Name] nvarchar(100) NULL, [Id] int NULL)

    ;

    SELECT @From_TableName=t.name FROM tempdb.sys.tables t WHERE t.object_id = @From_Object_id;

    SELECT @Into_TableName=t.name FROM tempdb.sys.tables t WHERE t.object_id = @Into_Object_id;

    ----------------------------------------------------------------------------------------------------------------------------------------

    -- Get list of common column names

    INSERT INTO @Cols SELECT i.[Name], i.[Column_Id]

    FROM tempdb.sys.columns i JOIN tempdb.sys.columns f

    ON i.object_id = @Into_Object_Id and f.object_id = @From_Object_Id and i.Name=f.Name

    ;

    -- convert list of names into a comma seperated string

    SET @SQL = substring((SELECT ( ', ['+[NAME]+']' ) FROM @ColS b ORDER BY [Id] FOR XML PATH( '' )), 3, 4000 )

    ;

    IF len(@SQL)>0 SET @SQL ='INSERT INTO '+@Into_TableName+' ('+@SQL+') SELECT '+@SQL+' FROM '+@From_TableName

    ;

    RETURN @SQL

    ;

    END

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

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