Results 1 to 8 of 8

Thread: SQL Copy Table

  1. #1
    Join Date
    Jan 2002
    Location
    London
    Posts
    73

    Unanswered: SQL Copy Table

    Hi i do not have that much experience on SQL. I have a table on one server called tblproducts and i would like to copy the table onto another table which is linked all the time or updates every couple of hours.

    I am not sure how i go about this, need some guidence.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    INSERT 
      INTO target_table
         ( columns )
    SELECT columns
      FROM source_table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    31
    If the table doesn't exist on target server, either create the table structure and use the method posted by r937 OR use SELECT - INTO if the table doesnt exist on target db.

    See this - SQL SELECT INTO Statement

    The other way to do is by using import-export wizard.

  4. #4
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Use this

    Insert into Test (col1,col2)
    select (col1,col2) from test1

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    once again, jassi, you have neglected to read the entire thread

    how is your solution different from post #2 ??

    please, stop doing this!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937 View Post
    how is your solution different from post #2 ??
    ...by being syntactically incorrect?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2011
    Posts
    71
    hello all ,I have an idea to make 2 insert for 2 linked objects concurrently, at the same time ,Also we can make trigger for one table to insert to another ,But last soluton will consume more resources ,Let's look some code

    The following sets up the situation I had, using table variables.

    DECLARE @Object_Table TABLE
    (
    Id INT NOT NULL PRIMARY KEY
    )

    DECLARE @Link_Table TABLE
    (
    ObjectId INT NOT NULL,
    DataId INT NOT NULL
    )

    DECLARE @Data_Table TABLE
    (
    Id INT NOT NULL Identity(1,1),
    Data VARCHAR(50) NOT NULL
    )

    -- create two objects '1' and '2'
    INSERT INTO @Object_Table (Id) VALUES (1)
    INSERT INTO @Object_Table (Id) VALUES (2)

    -- create some data
    INSERT INTO @Data_Table (Data) VALUES ('Data One')
    INSERT INTO @Data_Table (Data) VALUES ('Data Two')

    -- link all data to first object
    INSERT INTO @Link_Table (ObjectId, DataId)
    SELECT Objects.Id, Data.Id
    FROM @Object_Table AS Objects, @Data_Table AS Data
    WHERE Objects.Id = 1
    Thanks to another answer that pointed me towards the OUTPUT clause I can demonstrate a solution:

    -- now I want to copy the data from from object 1 to object 2 without looping
    INSERT INTO @Data_Table (Data)
    OUTPUT 2, INSERTED.Id INTO @Link_Table (ObjectId, DataId)
    SELECT Data.Data
    FROM @Data_Table AS Data INNER JOIN @Link_Table AS Link ON Data.Id = Link.DataId
    INNER JOIN @Object_Table AS Objects ON Link.ObjectId = Objects.Id
    WHERE Objects.Id = 1
    It turns out however that it is not that simple in real life because of the following error
    the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship

    I can still OUTPUT INTO a temp table and then finish with normal insert. So I can avoid my loop but I cannot avoid the temp table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •