Technical Article

Don't forget the Title

,

A wonderful example of how to generate inserts was given in the forum etiquette article. Of course to me, that was still too much typing. So I decided that I should write one script and just change the table name and let SQL generate the script to generate the script. Well since I was already sitting back and letting SQL do the work, I figured it should keep working. Initially I thought, why not store the output in a temp table or something and generate the insert statement like that. This seemed feasable but as I was getting ready to create more line generation I realized that the column name was going to be output whatever I chose to call it. So why not use it!? No one ever said I couldn't call a column [Fred] or [Geroge] or [INSERT INTO MyTable]. So I did 🙂

You will notice the output still carries the trailing 'UNION ALL' but you can certainly stop selecting when you get to that point. The underline for the column name being dashes is merely a comment so it can be sucked right up and run to do your inserts if you didn't feel like deleting it.

Another little trick I added here is the way I built up the column list. Notice the @Delim variable. Initially it is set to ''. Each time through the select it is set to ',' and used. This allows it to be blank the first time through and a comma all the other times. Because of that it can be added to the beginning of the string being built up and will not leave a trailing comma.

SET NOCOUNT ON
DECLARE @TableName sysname
SET @TableName = 'MyTable'

DECLARE @ColList varchar(4000)
SET @ColList ='('
DECLARE @delim varchar(1)
SET @Delim = ''

SELECT @ColList = @ColList + @Delim + QUOTENAME(COLUMN_NAME)
 ,@Delim = ',' 
FROM INFORMATION_SCHEMA.Columns 
WHERE Table_Name = @TableName
ORDER BY ORDINAL_POSITION

SELECT @ColList=@ColList+')'

SELECT 
 CASE ORDINAL_POSITION 
 WHEN 1 THEN 'SELECT '' SELECT ''+' 
 ELSE ' +'',''+' END 
 +CASE WHEN DATA_TYPE IN ('Money') 
 THEN 'CAST('+COLUMN_NAME+', as VARCHAR)'
 ELSE 'QUOTENAME('+COLUMN_NAME+','''''''')' END 
 +CASE 
 WHEN Ordinal_position = 
 (SELECT MAX(ORDINAL_POSITION) 
 FROM INFORMATION_SCHEMA.Columns 
 WHERE Table_Name = @TableName) 
 THEN '+ '' UNION ALL'' AS "INSERT INTO '+ @TableName + ' ' +@ColList +'"' 
 + char(10)+'FROM '+ @TableName 
 ELSE ' ' END
FROM INFORMATION_SCHEMA.Columns 
WHERE Table_Name = @Tablename
ORDER BY ORDINAL_POSITION

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.5 (2)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.5 (2)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2003-01-13

18,597 reads