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
 SQL Server Development (2000)
 Building structures from results???

Author  Topic 

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2004-06-15 : 09:25:30
My problem is that I need to be able to create structures in SQL for all the tables selected in the following cursor...

declare @TabName varchar(100), @Cmd varchar(1000)

select TABLE_NAME into #tmpA from master.dbo.SYSREMOTE_TABLES ( N'LinkedSrv')
WHERE TABLE_NAME like 'VAL%' and TABLE_SCHEMA = 'Own'

declare Curnames insensitive cursor for select TABLE_NAME from #tmpA

Open Curnames

Fetch Next From Curnames InTo @TabName

While @@FETCH_STATUS = 0
	begin
		set @Cmd = 'select * from LinkedSrv..Own.' + @TabName
	
		exec(@Cmd)

		Fetch Next From Curnames InTo @TabName
	end

close Curnames
deallocate Curnames

This selects all the data out of the tables, but does anyone know how I would be able to build tables from the results??? I am currenly returning over 150 tables and will need to do this on two seperate servers, so really don't want to build them all manually...

Thanks in advance...

gpl
Posting Yak Master

195 Posts
Posted - 2004-06-15 : 11:12:59
assuming that you can extract the names/types/sizes, you need to build a dynamic string containing the SQL to create the table, then execute it
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2004-06-15 : 11:21:21
Ok, got a little further with this to the point of it's working, but I can't get the character size out from anywhere, so have set it to 255 on all fields for now...

declare @TabName varchar(100), @Cmd varchar(1000), @Cmd2 varchar(2000)

select TABLE_NAME into #tmpA from master.dbo.SYSREMOTE_TABLES ( N'LinkedSrv')
WHERE TABLE_NAME like 'VAL%' and TABLE_SCHEMA = 'Own'

declare Curnames insensitive cursor for select TABLE_NAME from #tmpA

Open Curnames

Fetch Next From Curnames InTo @TabName

While @@FETCH_STATUS = 0
	begin
		set nocount on
		set @Cmd = 'insert into dbo.' + @TabName + ' select * from LinkedSrv..Own.' + @TabName
		
		select COLUMN_NAME + ' ' + 
			case 	when DATA_TYPE = 129 then 'Varchar(255),'
				when DATA_TYPE = 131 then 'decimal(' + 	convert(varchar(10),NUMERIC_PRECISION) + ',' + convert(varchar(10),NUMERIC_SCALE) + '),'
				when DATA_TYPE = 135 then 'datetime,'
				when DATA_TYPE = 139 then 'int,'
			end as Data_type into #tmpB
		from master.dbo.SYSREMOTE_COLUMNS ( N'LinkedSrv') 
		WHERE TABLE_NAME like @TabName and TABLE_SCHEMA = 'Own'

		set @Cmd2 = 'Create table dbo.' + @TabName + ' ( '

		select @Cmd2 = @Cmd2 + Data_Type from #tmpB

		set @Cmd2 = substring(@Cmd2,1,len(@Cmd2)-1) + ')'
	
		exec(@Cmd2)
		exec(@Cmd)

		drop table #tmpB

		Fetch Next From Curnames InTo @TabName
	end

close Curnames
deallocate Curnames

drop table #tmpA

I know I only have these 4 datatypes in the tables, so not too fussed about that, I would just like to know how to get the char length...
There are two fields in SYSREMOTE_COLUMNS, CHARACTER_MAXIMUM_LENGTH & CHARACTER_OCTET_LENGTH, which I would have thought would have had the length, the problem is, these give an overflow error when I try to select straight from them...

Any ideas??
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-06-15 : 11:28:30
Rick,

Why can't you script out the other db?

Then bcp the data out and then in?



Brett

8-)
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2004-06-15 : 11:56:36
Its not SQL for a start and we don't have the right permissions...

I wish I could...
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-06-15 : 12:08:16
quote:
Originally posted by RickD

I can't get the character size out from anywhere

Would a MAX(DATALENGTH(EachColumn)) be a better default?

I thought there was something in ADO that indicated max column width [of a recordset], but.

I presume you can't use
set @Cmd = 'select * into dbo.' + @TabName + ' from LinkedSrv..Own.' + @TabName
rather than having to pre-create the table?

Kristen
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2004-06-16 : 04:32:23
quote:
Originally posted by Kristen
Would a MAX(DATALENGTH(EachColumn)) be a better default?

I thought there was something in ADO that indicated max column width [of a recordset], but.

I'm not selecting from the table until after it is created on SQL server... Could do max(datalength()) but this would add loads of overhead as I would need to select from each table before I have created the tables as well as after...

Not using ADO, just SQL...

quote:
Originally posted by Kristen
I presume you can't use
set @Cmd = 'select * into dbo.' + @TabName + ' from LinkedSrv..Own.' + @TabName
rather than having to pre-create the table?

Kristen

This wouldn't work as it would give me an invalid object name error as the table on the select into doesn't exist and is not going to be a temp table...
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-06-16 : 05:46:55
If ADO has the Max Length stuff (and I don't know for sure that it does) might it be worth using an OBJECT from SQL just to interogate it?

Appreciate that you don't want to use a TEMP table to gather all the data, but you could chuck a "WHERE 1=0" on the end to get just the structure and no rows:

SELECT   *
INTO     MyTempTable
FROM     MySourceTable
WHERE    1=0

and then interogate the Temp Table to make your CREATE TABLE stuff?

But no point unless it gives you accurate Max Widths on VARCHAR snd suchlike

However, biggest problem I perceive with this is you have to have some middle-glue running, I forget which, MTS maybe?

Kristen
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2004-06-16 : 07:16:28
Ok, thanks for the help, got it working finally...


declare @TabName varchar(100), @Cmd varchar(1000), @Cmd2 varchar(2000), @Getcharlen varchar(1000), @id int

select TABLE_NAME into #tmpA from master.dbo.SYSREMOTE_TABLES ( N'LinkedSrv')
WHERE TABLE_NAME like 'VAL%' and TABLE_SCHEMA = 'Own'

set nocount on
declare Curnames insensitive cursor for select TABLE_NAME from #tmpA

Open Curnames

Fetch Next From Curnames InTo @TabName

While @@FETCH_STATUS = 0
	begin
		set @Cmd = 'insert into dbo.' + @TabName + ' select * from LinkedSrv..Own.' + @TabName

		set @Getcharlen = 'select * into ##tmpRDGetLen from LinkedSrv..Own.' + @TabName + ' where 1=0'
		
		exec(@Getcharlen)

		set @id = (select id from tempdb..sysobjects where name like '##tmpRDGetLen%')
		
		select '[' + COLUMN_NAME + '] ' + 
			case 	when DATA_TYPE = 129 then 'Varchar(' + convert(varchar(4),sc.length) + '),'
				when DATA_TYPE = 131 then 'decimal(' + 	convert(varchar(10),NUMERIC_PRECISION) + ',' + convert(varchar(10),NUMERIC_SCALE) + '),'
				when DATA_TYPE = 135 then 'datetime,'
				when DATA_TYPE = 139 then 'int,'
			end as Data_type into #tmpB
		from master.dbo.SYSREMOTE_COLUMNS ( N'LinkedSrv') ss
		inner join tempdb..syscolumns sc on sc.[name] =  ss.COLUMN_NAME and sc.[id] = @id
		WHERE TABLE_NAME like @TabName and TABLE_SCHEMA = 'Own'
		ORDER BY ORDINAL_POSITION

		set @Cmd2 = 'Create table dbo.' + @TabName + ' ( '

		select @Cmd2 = @Cmd2 + Data_Type from #tmpB

		set @Cmd2 = substring(@Cmd2,1,len(@Cmd2)-1) + ')'

		exec(@Cmd2)
		exec(@Cmd)

		drop table #tmpB
		drop table ##tmpRDGetLen

		Fetch Next From Curnames InTo @TabName
	end

close Curnames
deallocate Curnames
set nocount off

drop table #tmpA
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-06-16 : 12:17:08
Well done that man!

Kristen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site