Brett Kaiser (x002548) Blog

EDIT 2007/09/06:  I've modified the sproc to change the dates to be formatted to 121 and stripped out all trailing spaces for char's

So, we don't have DBArtisan, but I am very happy for my copy of ERWin.  Don't know what I'd do with out it.

So we have some requirements where they want to create insert statements to load a production table.  I said, why not just bcp the data out in native format and create an osql script for the production DBA's to insert the data, or a sproc perhaps.

There are many better ways in my opinion, but I do like a challenge, so I wrote the following.  It's not a very mature sproc...no error handling, doesn't handle images or text (how would you anyway for inserts?), ect

Just supply the table_name to the sproc

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_Generate_Inserts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_Generate_Inserts]
GO

CREATE PROC isp_Generate_Inserts
 @TABLE_NAME sysname
AS

SET NOCOUNT ON

/*
EXEC isp_Generate_Inserts 'BusinessGroup'
EXEC isp_Generate_Inserts 'MEPType'
EXEC isp_Generate_Inserts 'Person'
EXEC isp_Generate_Inserts 'Profile'
EXEC isp_Generate_Inserts 'Status'
EXEC isp_Generate_Inserts 'SubBusinessGroup'
EXEC isp_Generate_Inserts 'XREF'
EXEC isp_Generate_Inserts 'Operator'
EXEC isp_Generate_Inserts 'FORMREF'
EXEC isp_Generate_Inserts 'MEPTERRITORY'
EXEC isp_Generate_Inserts 'MEPTICKLERSTATUS'
*/

DECLARE @INSERT varchar(8000), @COLLIST varchar(8000)
--, @TABLE_NAME sysname
, @SELECT varchar(8000), @cmd varchar(8000), @x int

  SELECT @COLLIST = COALESCE(@COLLIST + ', ','') + COLUMN_NAME
    FROM INFORMATION_SCHEMA.Columns
   WHERE TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @INSERT = 'INSERT INTO ' + @TABLE_NAME + '('+ @COLLIST + ')'

-- SELECT @INSERT

SELECT @SELECT = COALESCE(@SELECT + '+'',''+ ','') +
           CASE WHEN DATA_TYPE
  IN ('datetime','smalldatetime')
  THEN + ''''+ +''''+''''+''''+'+' + 'COALESCE(CONVERT(varchar(25),' + COLUMN_NAME + ',121),'''')' + '+' + ''''+''''+''''+'''' 
  WHEN DATA_TYPE
  NOT IN ('int','bigint','smallint','tinyint','deciaml','numeric','money')
  THEN + ''''+ ''''+''''+''''+'+COALESCE(REPLACE(RTRIM(' + COLUMN_NAME + ')' + ','''''''','''''''''''')' + ','''')+' + ''''+''''+''''+''''
  ELSE + 'COALESCE(RTRIM(CONVERT(varchar(25),' + COLUMN_NAME + ')),'''''''''''')'
    END
    FROM INFORMATION_SCHEMA.Columns
   WHERE TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @SELECT = 'SELECT ' + @SELECT + ' AS DATA FROM ' + @TABLE_NAME

-- SELECT @SELECT

SET @cmd = 'CREATE VIEW XXX AS ' + @SELECT

EXEC(@cmd)

CREATE TABLE myTable99(RowId int IDENTITY(1,1), Data varchar(8000))

INSERT INTO myTable99(Data) SELECT DATA FROM XXX

SELECT 0 AS RowId, @INSERT AS DATA INTO myTemp99

SET @cmd = 'CREATE VIEW YYY AS '
+'SELECT RowId, DATA FROM myTemp99 '
+'UNION ALL '
+'SELECT RowId, '+ '''' + 'SELECT ' + '''' + ' + DATA+ ' + '''' + ' UNION ALL ' + '''' + ' AS DATA FROM myTable99 WHERE RowId < (SELECT COUNT(*) FROM myTable99)'
+'UNION ALL '
+'SELECT RowId, '+ '''' + 'SELECT ' + '''' + ' + DATA AS DATA FROM myTable99 WHERE RowId = (SELECT COUNT(*) FROM myTable99) '

-- SELECT @cmd

EXEC(@cmd)

SET @cmd = 'bcp "SELECT DATA FROM MEP.dbo.YYY ORDER BY RowId" QUERYOUT D:\MEP\Scripts\INS_'+@TABLE_NAME+'.Dat -T -c -S<servername>'

-- SELECT @cmd

EXEC master..xp_cmdshell @cmd

 DROP VIEW XXX, YYY
 DROP TABLE myTable99, myTemp99
 SET NOCOUNT OFF

EXEC master..xp_cmdshell 'Dir D:\MEP\Scripts\*.*'

GO