SQL Server Performance

Copy Data within same Database

Discussion in 'SQL Server DTS-Related Questions' started by captnstiles, Dec 19, 2006.

  1. captnstiles New Member

    I have an issue:
    1) trying to copy data only from table1 (Col2) into table2 witout having to recontruct the complete table.
    both tables have the same column name and structure and I only need the data in that column from table 1 to put into table 2. Tried using temp tables to extract out and still no luck, keep getting error message pretaining to fields missing.
  2. Chappy New Member

    I dont understand the problem really

    create table newtable (id int)
    insert into newtable (id) select oldid from oldtable

    Wouldnt this work?
  3. MohammedU New Member

    Post sample script to see what you are trying to do and what error you are getting?


    Mohammed U.
  4. captnstiles New Member

    select
    AgentId

    into #temp from dbo.Agent

    select * from #temp

    later
    set ansi_warnings off
    insert into dbo.License
    select
    AgentId

    from #temp

    Server: Msg 213, Level 16, State 4, Line 2
    Insert Error: Column name or number of supplied values does not match table definition.
  5. Chappy New Member

    specify a field list for License when you insert into it
  6. madhuottapalam New Member

    insert into dbo.License(AgentID)select AgentId from #temp

    BTW
    there is no need of temp table here... u could have directly done that....
    ie
    insert into dbo.License(AgentID)select AgentId from dbo.Agent




  7. captnstiles New Member

    set ansi_warnings off
    insert into dbo.License(AgentID)select AgentId from dbo.Agent

    Server: Msg 515, Level 16, State 2, Line 3
    Cannot insert the value NULL into column 'RemindMe', table 'Test_Org.dbo.License'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
  8. ranjitjain New Member

    Hi,
    Here License table has got more than one non-null column.
    So while running any INSERT you need to specify all the column names and then if you want you can even insert some dummy values like.

    For eg:

    Insert License(Agentid,RemindMe)
    SELECT agentid, 0 from agent

    if this fail with same error message for other columns, specify that column in insert the same way.
    But Can you explain what are you trying to achive?

Share This Page