January 12, 2011 at 5:18 am
Table A has
(
col1,
col2,
col3,
col4,
col5,
col6,
logdate1,
logdate2,
logdate3,
.....
logdate24
)
I want to split Table A in to Table B and Table C.
Table B containing
(
col1 primary key,
col2,
col3,
col4,
col5,
col6
logdate1)
and
Table C containing
(
col1 foreign key
logdate2,
logdate3,
.....
logdate24
)
Is there a easier way to get the column list for table C after creating table B manually?
Such as create table C ( Get columnlist not in table B from Table A?) or another approach perhaps?
January 12, 2011 at 5:24 am
this
select c.Name, c.colid from
syscolumns c
inner join sysobjects o
on c.id = o.id and o.xtype = 'u'
where o.name = 'Your_table_name'
order by c.colid
January 12, 2011 at 5:28 am
or you can use
sp_help 'tablename'
January 12, 2011 at 5:29 am
or
Select column_name from Information_Schema.Columns
where table_name='YourTable'
January 12, 2011 at 6:53 am
All of these solutions would require you to write dynamic sql to create that third table, and it doesn't help with the data types.
This isn't the type of thing that is normally done through a program, unless it's being done as part of a regular process. If this is a one-time thing, just do it manually. Script out the first table as a create statement, then cut the columns you need to create the second table. What's left (in addition to the PK) is what is used for the third table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 12, 2011 at 7:09 am
this will give the data type
Select column_name,DATA_TYPE from Information_Schema.Columns
where table_name='your table'
January 12, 2011 at 10:11 am
something i whipped up:
create table A (col1 int, col2 int, col3 int, col4 int)
create table B (col1 int, col2 int)
declare @field_list varchar(max)
--set @field_list = ''
;with cte as
(select column_name, data_type from information_schema.columns
where table_name = 'A'
except
select column_name, data_type from information_schema.columns
where table_name = 'B'
)
select @field_list = coalesce(@field_list + ',','') + column_name + ' ' + data_type
from cte
print 'create table C (' + @field_list + ')'
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
January 12, 2011 at 1:48 pm
Can you explain ';with cte as '
is cte an array here?
Thanks
January 12, 2011 at 2:12 pm
This should help:
http://msdn.microsoft.com/en-us/library/ms190766.aspx
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply