building an insert statement for tables

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  June 20, 2007 at 9:01 am

  #153643

  Guys,

  Is there anyway I can build a 'DML - INSERT' statement for LOV tables (_LOV) using a sql script by querying

  information_schema.columns.

  I will need to build the insert statements for the tables returned by the following query.

  select name from sysobjects where name like '%_LOV' and xtype = 'u'

  any suggestions/inputs will help

  Thanks

• VT-176769

  SSChasing Mays

  Points: 637

  More actions

  June 20, 2007 at 11:39 am

  #714185

  Hi

   

  Check this

  http://vyaskn.tripod.com/code/generate_inserts.txt

  regards

  VT

• Graeme100

  SSCrazy Eights

  Points: 8054

  More actions

  June 21, 2007 at 5:01 am

  #714300

  Hi,

  I've used this (bit messy but works), any good

  Graeme

  SET NOCOUNT ON

  /*Declare Variables*/

  DECLARE @TabName varchar(50),@str varchar(8000),@Select varchar(8000),@colName Varchar(30), @Ord int,@Debug int

  SET @Debug = 1

  /*Set Cursor for table names*/

  DECLARE tab_cursor CURSOR FOR

  SELECT Table_Name FROM INFORMATION_SCHEMA.Tables

  WHERE Table_name LIKE 'tbl%'

  ORDER BY Table_name

  OPEN tab_cursor

  FETCH NEXT FROM tab_cursor

  INTO @TabName

  WHILE @@FETCH_STATUS = 0

  BEGIN /*SET cursor for columns of each table*/

      DECLARE Col_cursor CURSOR FOR

   SELECT Column_Name , Ordinal_position

   FROM INFORMATION_SCHEMA.Columns

   WHERE Table_name = @TabName

   ORDER BY Ordinal_Position

   /*Set initial string for Insert and Select*/

     OPEN Col_cursor

     FETCH NEXT FROM Col_cursor INTO @ColName, @ord

   SELECT @STR = 'TRUNCATE TABLE DATABASE_1.dbo.'+ @TabName + CHAR(13)+'INSERT INTO DATABASE_1<A href="mailtoATABASE_1.dbo.'+@TabName+'('">.dbo.'+@TabName+'('

   SELECT @Select = 'SELECT '

     WHILE @@FETCH_STATUS = 0

     BEGIN /*Creates Insert and Select statements*/

   If @Ord = 1

          SELECT @STR = @STR + @ColName

           ELSE

          SELECT @STR = @STR + ','+ @ColName

   If @Ord = 1

          SELECT @Select = @Select + @ColName

           ELSE

          SELECT @Select = @Select + ','+ @ColName

     FETCH NEXT FROM Col_cursor INTO @ColName , @ord 

     END /*tidy up scripts*/

   SELECT @STR = @STR + ')'

   SELECT @Select = @Select + CHAR(13) +  'FROM DATABASE.dbo.' + @TabName + CHAR(13) + 'WHERE VerifyDate IS NOT NULL'

   SELECT @Select = @SELECT + CHAR(13) +  'GO'

   SELECT @Select = @Select + CHAR(13) +  'SELECT CAST(Count(*) AS Varchar) + '' Lines Inserted'' FROM DATABASE<A href="mailtoATABASE.dbo.'+@TabName">.dbo.'+@TabName

   SELECT @Select = @SELECT + CHAR(13) +  'GO'

   SELECT @Select = @SELECT + CHAR(13) +  'PRINT ''INSERT to ' + @TabName + ' Complete'''

   SELECT @Select = @SELECT + CHAR(13) +  'GO'

   SELECT @Select = @SELECT + CHAR(13) +  'PRINT ''END'''

   SELECT @Select = @SELECT + CHAR(13) +  'GO'

   SELECT @Select = @SELECT + CHAR(13)

   --EXEC(@str)

   --EXEC(@Select)

    Print @STR

    Print @Select

     CLOSE Col_cursor

     DEALLOCATE Col_cursor

     FETCH NEXT FROM tab_cursor

     INTO @tabname

  END

  CLOSE tab_cursor

  DEALLOCATE tab_cursor

  GO