SQL Server Performance

Import Data into a Table from Another Table

Discussion in 'SQL Server 2005 Replication' started by Sam.N, May 30, 2007.

  1. Sam.N New Member

    I have a table that contains some information [FieldName, FieldDesc]. This information was imported from a different database and I want to insert it into a different table and add a Foreign Key to it and a Primary ID.

    I have written the following Stored Procedure to perform the importing and inserting into the database but I think I am making an error somewhere in the code.
    Any help would be much appreciated.


    CREATE PROCEDURE [dbo].[FieldInsert]
    @FieldID int = 0,
    @FieldName nvarchar(255),
    @FDesc nvarchar(255),
    @TableID int

    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE FieldInfoCursor CURSOR FOR
    SELECT [Name], [Description]
    FROM dbo.FFDCSTBP

    OPEN FieldInfoCursor
    FETCH NEXT FROM FieldInfoCursor INTO @FieldName, @FDesc
    WHILE @@FETCH_STATUS = 0

    BEGIN
    INSERT INTO dbo.FieldTest ([FieldID], [Name], [Description], [TableID]) VALUES (@FieldID, @FieldName, @FDesc, 4)
    SET @FieldID = @FieldID + 1
    FETCH NEXT FROM FieldInfoCursor INTO @FieldName, @FDesc
    END

    CLOSE FieldInfoCursor
    DEALLOCATE FieldInfoCursor
    END

  2. Adriaan New Member

    It looks like you are trying to take an object-based approach to T-SQL, which is just not supported.

    Are you trying to copy names of tables and columns, or values from tables and columns?
  3. Sam.N New Member

    I am trying to copy the values from one table to another table....

    the information stored in the table is information about a table and field structure of another database.
  4. Adriaan New Member

    INSERT INTO dbo.FieldTest ([FieldID], [Name], [Description], [TableID])
    SELECT @FieldID, [Name], [Description], @TableID
    FROM dbo.FFDCSTBP

    And I guess you're calling this SP from another one, which loops through a cursor that reads the FieldID and TableID from another table?

    A little guesswork here and there ...

    INSERT INTO dbo.FieldTest ([FieldID], [Name], [Description], [TableID])
    SELECT Y.FieldID, X.[Name], X.[Description], Y.TableID
    FROM dbo.FFDCSTBP X
    INNER JOIN <othertable> Y
    ON X.TableID = Y.TableID
  5. Sam.N New Member

    this will not work.

    I do not need to join data from multiple tables.

    I need to get data from one table (FFDCSTBP) and insert into another table (FieldTest)

    When that data is inserted, I want the procedure to add the Primary ID to the newly added field based.

    I would like the procedure to read the last ID from the table (FieldTest) and increment it by one.

    Your solution assumes that T-SQL doesn't support this functionality. I have created such a procedure before but I can not find my code.
  6. Adriaan New Member

    I assume you need the new ID values to import data from child tables of this parent table, right?

    If you want to do it one row at a time, lookup SCOPE_IDENTITY in Books Online.

    But why not ...
    (1) Insert the data into a staging table, where you add a column for the new ID value (leaving it blank).
    (2) Insert the data from the staging table into the actual table.
    (3) Update the ID column of the staging table with the new ID values from the actual table, joining the two tables on the natural key values.

    When importing child data, just join the source child table to the staging table, again on the natural key values, and read the new ID value from the staging table.

    For any significant amount of data, the set-based approach will be faster than your cusor-based version.
  7. vempatiphani New Member

    Hi, i want to copy tables from one database to another database including data in the tables and permissions on the table? please help me in this topic

    Thanks & Regards,
    Phani.V
  8. MohammedU New Member

    select * INTO DestinationDB.dbo.TableName
    from SourceDB.dbo.TableName

    You have to grant the permissions seperate...


    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  9. Madhivanan Moderator

    quote:Originally posted by vempatiphani

    Hi, i want to copy tables from one database to another database including data in the tables and permissions on the table? please help me in this topic

    Thanks & Regards,
    Phani.V
    1 Always post your question as a new topic
    2 Create a table and then do insert into.........select columns from..........

    Madhivanan

    Failing to plan is Planning to fail
  10. satya Moderator

    for the permissions ... create a script on the source table and apply on the target table.

    quote:Originally posted by vempatiphani

    Hi, i want to copy tables from one database to another database including data in the tables and permissions on the table? please help me in this topic

    Thanks & Regards,
    Phani.V

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  11. cse_tapas New Member

    If you want to Import data into a existing table from another existing table:
    Insert Into [Table Name](Column1,Column2,Column3,.....) select Column1,Column2,Column3,... from [another table name]
    Thanks & Regards,
    Tapas Pati
  12. satya Moderator

    One thing I don't understand what is the use of suggesting same on the old thread by tapas.
  13. cse_tapas New Member

    Hi, You can use this query--
    select * into [anotherdatabseName].dbo.[NewTableName] from [Database Name].dbo.[TableName]
    throw tis query Table create automatically(NewTableName).
    Thanks & Regards
    Tapas Pati
  14. Adriaan New Member

    Tapas,
    Try to avoid using the SELECT ... INTO ... syntax, because it can lock up the source table.
    The proper way is to use the CREATE TABLE syntax, and then INSERT INTO ... SELECT ... FROM ...

Share This Page