Dynamic Code

I have the following code which will give me all the tables, but I will need
to dynamically generate the select list and explicitly name the fields -
here is the code I received in another post. I would be grateful if somebody
could give me the information of how I could create the list of fields in
the source table (OldDB) to dynamically insert into the destination table
(NewDB) - I know that there may be fields in the NewDB that are not in the
OldDB, but not vice versa. And I know defaults will handle any fields that
do not make it into the select list. How can this be done? By the way I
have to have this scripted onto the production machines that I do not have
direct access to.
declare @.sql varchar(8000)
declare @.table_name varchar(256)
SELECT name FROM sysobjects where xtype = 'u' and name <>
'dtproperties'
DECLARE table_list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'u' and name <>
'dtproperties'
OPEN table_list
FETCH NEXT FROM table_list INTO
@.table_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'Insert into NewDB..' + @.table_name + ' ' + '(Select * From
OldDatabase..' + @.table_name + ' ) '
--print @.sql
--EXEC (@.SQL)
FETCH NEXT FROM table_list INTO
@.table_name
END
DEALLOCATE table_listA couple of questions and a few comments.
1. How big is your database? Would a restore/robocopy/litespeed or even DTS
solution not be better seen as code is inherently going to invite error.
2. Why do you have to dynamically instantiate the field names. Are the table
structures not the same in oldDB?
3. You could try a nested loop using your table name variable and iterating
through the following, you will still have to determine the size, collations
and defaults for the table. Its messy but with a bit of work you could do it
.
create table tblMaster_Data_Types(
intData_Type_Id int,
vcData_Type_Desc varchar(50),
chActive char(1))
insert into tblMaster_Data_Types values(1, 'TINYINT', 'Y')
insert into tblMaster_Data_Types values(2, 'FLOAT', 'Y')
insert into tblMaster_Data_Types values(3, 'SMALLINT', 'Y')
insert into tblMaster_Data_Types values(4, 'INT', 'Y')
insert into tblMaster_Data_Types values(5, 'BIGINT', 'Y')
insert into tblMaster_Data_Types values(6, 'DECIMAL', 'Y')
Select SYS_OBJ.NAME,SYS_COL.NAME--,
DATA_TYP.vcData_Type_Desc,
SYS_USR.NAME
From OldDB..sysobjects SYS_OBJ,
OldDB..sysUsers SYS_USR,
OldDB..SYSCOLUMNS SYS_COL,
OldDB..SYSTYPES SYS_TYP--,
OldDB..tblMaster_Data_Types DATA_TYP
Where SYS_OBJ.type = 'U'
AND SYS_OBJ.UID = SYS_USR.UID
AND SYS_OBJ.ID = SYS_COL.ID
AND SYS_TYP.TYPE = SYS_COL.TYPE
AND DATA_TYP.vcData_Type_Desc Collate QL_LATIN1_GENERAL_CP1_CI_AS =
SYS_TYP.NAME collate SQL_LATIN1_GENERAL_CP1_CI_AS
AND DATA_TYP.chActive = 'Y'
ORDER BY SYS_OBJ.NAME
4. Get access to the relevant database.
"Derek Hart" wrote:

> I have the following code which will give me all the tables, but I will ne
ed
> to dynamically generate the select list and explicitly name the fields -
> here is the code I received in another post. I would be grateful if somebo
dy
> could give me the information of how I could create the list of fields in
> the source table (OldDB) to dynamically insert into the destination table
> (NewDB) - I know that there may be fields in the NewDB that are not in the
> OldDB, but not vice versa. And I know defaults will handle any fields tha
t
> do not make it into the select list. How can this be done? By the way I
> have to have this scripted onto the production machines that I do not have
> direct access to.
> declare @.sql varchar(8000)
> declare @.table_name varchar(256)
> SELECT name FROM sysobjects where xtype = 'u' and name <>
> 'dtproperties'
> DECLARE table_list CURSOR FOR
> SELECT name FROM sysobjects where xtype = 'u' and name <>
> 'dtproperties'
> OPEN table_list
> FETCH NEXT FROM table_list INTO
> @.table_name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>
> SET @.sql = 'Insert into NewDB..' + @.table_name + ' ' + '(Select * From
> OldDatabase..' + @.table_name + ' ) '
> --print @.sql
> --EXEC (@.SQL)
>
> FETCH NEXT FROM table_list INTO
> @.table_name
> END
> DEALLOCATE table_list
>
>|||I am going to build these sql statements dynamically using VB.NET.
Is there a way to read a system table to determine if a specific table has
an identity column? I want to run SET IDENTITY_INSERT myTable ON but this
statement errors if the table does not have an Identity field.
Derek Hart
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8C65AFB3-517B-4D28-B40B-8DCBE3B12D62@.microsoft.com...
>A couple of questions and a few comments.
> 1. How big is your database? Would a restore/robocopy/litespeed or even
> DTS
> solution not be better seen as code is inherently going to invite error.
> 2. Why do you have to dynamically instantiate the field names. Are the
> table
> structures not the same in oldDB?
> 3. You could try a nested loop using your table name variable and
> iterating
> through the following, you will still have to determine the size,
> collations
> and defaults for the table. Its messy but with a bit of work you could do
> it.
> create table tblMaster_Data_Types(
> intData_Type_Id int,
> vcData_Type_Desc varchar(50),
> chActive char(1))
> insert into tblMaster_Data_Types values(1, 'TINYINT', 'Y')
> insert into tblMaster_Data_Types values(2, 'FLOAT', 'Y')
> insert into tblMaster_Data_Types values(3, 'SMALLINT', 'Y')
> insert into tblMaster_Data_Types values(4, 'INT', 'Y')
> insert into tblMaster_Data_Types values(5, 'BIGINT', 'Y')
> insert into tblMaster_Data_Types values(6, 'DECIMAL', 'Y')
> Select SYS_OBJ.NAME,SYS_COL.NAME--,
> DATA_TYP.vcData_Type_Desc,
> SYS_USR.NAME
> From OldDB..sysobjects SYS_OBJ,
> OldDB..sysUsers SYS_USR,
> OldDB..SYSCOLUMNS SYS_COL,
> OldDB..SYSTYPES SYS_TYP--,
> OldDB..tblMaster_Data_Types DATA_TYP
> Where SYS_OBJ.type = 'U'
> AND SYS_OBJ.UID = SYS_USR.UID
> AND SYS_OBJ.ID = SYS_COL.ID
> AND SYS_TYP.TYPE = SYS_COL.TYPE
> AND DATA_TYP.vcData_Type_Desc Collate QL_LATIN1_GENERAL_CP1_CI_AS
> =
> SYS_TYP.NAME collate SQL_LATIN1_GENERAL_CP1_CI_AS
> AND DATA_TYP.chActive = 'Y'
> ORDER BY SYS_OBJ.NAME
>
> 4. Get access to the relevant database.
> "Derek Hart" wrote:
>|||The stored procedure sp_columns will display a type of "int identity"
as opposed to "int" in the column Type_name. use it as sp_columns
'tablename'
This way you could do some cursor jiggery pokery and match on type_name
like 'identity%'
Cheers
Will|||The stored procedure sp_columns will display a type of "int identity"
as opposed to "int" in the column Type_name. use it as sp_columns
'tablename'
This way you could do some cursor jiggery pokery and match on type_name
like '%identity'
Cheers
Will|||Derek Hart (derekmhart@.yahoo.com) writes:
> I am going to build these sql statements dynamically using VB.NET.
> Is there a way to read a system table to determine if a specific table has
> an identity column? I want to run SET IDENTITY_INSERT myTable ON but this
> statement errors if the table does not have an Identity field.
objectproperty(id, 'TableHasIdentity')
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Within the cursor, you may build a cursor having the column list for
the table you are sitting on in the outer cursor.
SELECT column_name from information_schema.columns where table_name =
@.table_name. Build the string dynamically from that. You may run into
identity insert problems if using them in the db. Use SET
IDENTITY_INSERT table_name ON before the insert if then set it OFF
before continuing on to the outer cursor to the next table. The
following finds identity columns.
select table_name + '.' + column_name, table_name, column_name --,
ordinal_position, data_type
from information_schema.columns
where
--table_schema = 'dbo'
--and
columnproperty(object_id(table_name), column_name,'IsIdentity') = 1
order by table_name