dataLoad script (how to determine if identity)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

dataLoad script (how to determine if identity)

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2004-06-28 : 18:53:43

While ago, Tara Duggan (if you read this, thanks!) posted a great script which would query INFORMATION_SCHEMA.COLUMNS in order to build a text file which can be used to load data from one database to another. It works great, but I would also like to add identify info to the temp table listing if the column is an Identity column or not.


-- get schema info and insert into temp table
INSERT #a 
(
	ColType
	,ColName
) 
SELECT 
	CASE 
		WHEN DATA_TYPE LIKE '%char%' THEN 1 
		WHEN DATA_TYPE LIKE '%date%' THEN 2 
		WHEN DATA_TYPE LIKE '%bit%' THEN 3
		ELSE 0 
	END 
	,COLUMN_NAME 
FROM 
	INFORMATION_SCHEMA.COLUMNS
WHERE 
	TABLE_NAME = @TblName 
ORDER BY
	ORDINAL_POSITION

I have looked through all the INFORMATION_SCHEMA views add don't see any referencences to identity info. I'm sure data info is stored somewhere and I was curious if anyone knew where (and how to join to INFORMATION_SCHEMA.COLUMNS)

Here is the complete script



CREATE Procedure CreateDataLoadScript 
-- this sproc scripts out data from a supplied table into a sql insert script
@TblName varchar(128) 
AS
DECLARE 
@id int 
,@maxid int
,@cmd1 varchar(7000) 
,@cmd2 varchar(7000) 

-- create temp table
CREATE TABLE #a 
(
	id INT identity (1,1)
	,ColType int
	,ColName varchar(128)
) 

-- get schema info and insert into temp table
INSERT #a 
(
	ColType
	,ColName
) 
SELECT 
	CASE 
		WHEN DATA_TYPE LIKE '%char%' THEN 1 
		WHEN DATA_TYPE LIKE '%date%' THEN 2 
		WHEN DATA_TYPE LIKE '%bit%' THEN 3
		ELSE 0 
	END 
	,COLUMN_NAME 
FROM 
	INFORMATION_SCHEMA.COLUMNS
WHERE 
	TABLE_NAME = @TblName 
ORDER BY
	ORDINAL_POSITION 

-- get ordinal positions of table columns
SELECT 
	@id = 0 
	,@maxid = MAX(id) 
FROM
	#a 

SELECT @cmd1 = 'select '' insert ' + @TblName + ' ( ' 
SELECT @cmd2 = ' + '' select '' + ' 
WHILE @id < @maxid 
    BEGIN
	-- get next column id 
	SELECT @id = MIN(id) FROM #a WHERE id > @id 
	-- build column name list
	SELECT 
		@cmd1 = @cmd1 + ColName + ',' 
	FROM 
		#a 
	WHERE
		id = @id 

	-- build column value list
	SELECT 
		@cmd2 = @cmd2 
		+ ' case when ' + ColName + ' is null ' 
		+ ' then ''null'' ' 
		+ ' else ' 
		+ CASE
			WHEN ColType = 1 THEN ''''''''' + ' + ColName + ' + ''''''''' 
			WHEN ColType = 2 THEN ''''''''' + convert(char(9),' + ColName + ',112) + convert(char(12),' + ColName+ ',114) + ''''''''' 
			WHEN ColType = 3 THEN ''''''''' + convert(char(1),' + ColName + ') + '''''''''
			ELSE ''''''''' + convert(varchar(500),' + ColName + ') + '''''''''   --convert into varchar so can append to @cmd2
		END 
		+ ' end + '','' + ' 
	FROM
		#a  
	WHERE
		id = @id 
    END 

-- clean up
SELECT @cmd1 = LEFT(@cmd1,LEN(@cmd1)-1) + ' ) '' ' 
SELECT @cmd2 = LEFT(@cmd2,LEN(@cmd2)-8) + ' from ' + @tblName 

SELECT '/*' + @cmd1 + @cmd2 + '*/' 

PRINT(@cmd1 + @cmd2)
--EXEC(@cmd1 + @cmd2) 
DROP TABLE #a



GO

Nic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-28 : 19:00:13

Nic, you would use COLUMNPROPERTY meta data function.

Here's an example:

SELECT COLUMNPROPERTY(OBJECT_ID('TableName'), 'ColumnName', 'IsIdentity')

0 is false, 1 is true, NULL means invalid input.

Tara

nic
Posting Yak Master

209 Posts

Posted - 2004-06-28 : 19:09:19

Thanks!!

Nic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources