Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Unanswered: Execute a query from a string

    I've got a string that contains my Insert query

    How can I execute it ?


    declare @sql char(500)
    Select @sql = '"Insert Into T74ACCO Select * From tempdb..##Temp tp Where tp.' + @Key + ' not In (Select ' + @Key + ' From T74ACCO)"'


    Thank you in advance
    BK,BM or Snail

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dynamic SQL too?

    Code:
    DECLARE @sql char(500)
    SELECT @sql = 'Insert Into T74ACCO Select * From tempdb..##Temp tp Where tp.' 
    	+ @Key + ' not In (Select ' 
    	+ @Key + ' From T74ACCO)'
    First I would ALWAYS list all of the columns that I'll be inserting into....

    Second, I would NEVER use SELECT * in code

    Third, I use dynamic sql a sparingingly as possible....

    Fourth, Global temp tables? I'd think I'd make a permanent table instead


    And why would a column name change if the table doesn't?


    Did it work?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    this is the total dynamic query :


    declare @Key char(50), @sql char(2000)

    Select Top 1 * Into ##Temp From T74ACCO;

    Bulk Insert tempdb..##Temp
    From 'D:\Dvlt\Mercure MSDE\Mac\Base\Download\TempInsert\FOL-T74ACMO.CTT'
    With (FieldTerminator='',RowTerminator='\n',CodePage= 'ACP',TabLock);

    select @Key=sc.name
    from sysindexes sy
    join sysobjects so on so.id=sy.id and so.xtype='U'
    join sysusers su on su.uid=so.uid
    join sysindexkeys si on si.id=so.id and si.indid=sy.indid
    join syscolumns sc on sc.id=so.id and sc.colid=si.colid
    join sysobjects sop on sop.parent_obj=so.id and sop.xtype='PK' and sop.name=sy.name
    where sy.indid not in(0,255) and so.name ='T74ACCO'

    Select @sql= '"Insert Into T74ACCO Select * From tempdb..##Temp tp Where tp.' + dbo.Trim(@Key) + ' not In (Select ' + dbo.Trim(@Key) + ' From T74ACCO)"'



    there are 2 variables :
    the path of the file to load and
    the name of the name where it must be loaded

    only the lines of the file that don't exist in the table must be loaded
    that's why I use the temp table

    and "Select *" is Ok 'cause when we change the structure of the database
    we've got to send a new set up for the new fonctionalities of the program
    to the 2000 users in France
    (structure doesn't change in between setups)

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Forgot

    How can I run @sql (insert query) ?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    EXEC(@sql)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    EXEC sp_executesql @sql

    is the same ?

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Karolyn
    EXEC sp_executesql @sql

    is the same ?
    BOL:

    Using sp_executesql is recommended over using the EXECUTE statement to execute a string. Not only does the support for parameter substitution make sp_executesql more versatile than EXECUTE, it also makes sp_executesql more efficient because it generates execution plans that are more likely to be reused by SQL Server.

  8. #8
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    thks Snail

    you've seen the use I made for your Select-Key-Query ?

  9. #9
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Karolyn
    thks Snail

    you've seen the use I made for your Select-Key-Query ?
    Sure

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What he said....

    But I still don't buy all the bs they publish...

    Like they have to put in that it's more versatile as their leading argument...yeah right...

    secondarily they mention that it may see an performance boost...

    suffice it to say, dynamic sql is just a plain poor option...but like I said if you have no choice....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Brett Kaiser
    What he said....

    But I still don't buy all the bs they publish...

    Like they have to put in that it's more versatile as their leading argument...yeah right...

    secondarily they mention that it may see an performance boost...

    suffice it to say, dynamic sql is just a plain poor option...but like I said if you have no choice....
    About reusing an execution plan (for sp_executesql) it is true - I was checking this by system monitor.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...if you say so....but how can it if she'll be change the column names in her query, unless it remains statis, and if true, what's the point of dynamic....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    don't start on performance OR UDF OR indexes...

  14. #14
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    her [trying deseperatly changing the subject]
    how was the snow at ski ?

  15. #15
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Brett Kaiser
    OK...if you say so....but how can it if she'll be change the column names in her query, unless it remains statis, and if true, what's the point of dynamic....
    I am only comparing exec and sp_executesql - nothing more....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •