Create Script dynamically
-
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.
-
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
-
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.
-
How Can I generate " create table" script if table does not exist in db2 ?
-
The select into statement will create table for you.
-
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