| Groups | Blog | Home |
sql server dts : Dynamic Code
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_list
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. [quoted text, click to view] "Derek Hart" wrote:
> 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_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 [quoted text, click to view] "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: > >> 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_list >> >> >>
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:
[quoted text, click to view] > 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/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |