Post reply

create table with a subset of columns from another table

  • January 12, 2011 at 5:18 am

    #388614

    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

    #1272297

    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

    #1272301

    or you can use

    sp_help 'tablename'

  • January 12, 2011 at 5:29 am

    #1272302

    or

    Select column_name from Information_Schema.Columns

    where table_name='YourTable'

  • January 12, 2011 at 6:53 am

    #1272340

    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

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 12, 2011 at 7:09 am

    #1272348

    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

    #1272471

    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

    #1272637

    Can you explain ';with cte as '

    is cte an array here?

    Thanks

  • January 12, 2011 at 2:12 pm

    #1272659

    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