Post reply

Create Script dynamically

  • May 23, 2003 at 11:55 am

    #82191

    Here is What I want to do:

    I have 2 databases-db1,db2.

    Right now db1, and db2 are exactly the same databases.

    I have script that inserts data from few tables (based on some criteria) from DB1 into DB2 and deletes that data from DB1.

    That script is basically a dynamic SQL inside cursor. Cursor loops thru all the tables in DB1.

    Now the problem is when I create a new table in DB1, my script will fail as it can't find that table in DB2. So I want to check if table exists in DB2, then insert data , otherwise create table in DB2 and then insert data. How can I generate the create table script dynamically?

    Can someone tell me from where does the SQL genearate the script?

    Thanks in advance.

  • May 23, 2003 at 12:25 pm

    #458404

    Just an example, Someone may have better solution.

    use northwind

    go

    declare @cmd sysname

    declare @tblname sysname

    declare @ret int

    select @tblname = 'products'

    select @ret = count(*) from pubs.dbo.sysobjects where name = @tblname and xtype = 'U'

    if @ret = 0

    begin

    select @cmd = "select * into pubs.dbo." + @tblname + " from " + @tblname

    exec (@cmd)

    end

  • May 23, 2003 at 5:38 pm

    #458428

    I think your 'application' needs to be able to determine when there is a new table via creation date or an exists if servers can be linked.

    Plan b: Generate an if not exists statement for all all tables with constraint,indexes, boots and all.

  • May 27, 2003 at 11:03 am

    #458691

    How Can I generate " create table" script if table does not exist in db2 ?

  • May 27, 2003 at 11:20 am

    #458692

    The select into statement will create table for you.

  • May 27, 2003 at 11:46 am

    #458696

    thanks much. that hepled.

Viewing 6 posts - 1 through 5 (of 5 total)

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