Results 1 to 7 of 7

Thread: Select Into

  1. #1
    Join Date
    Nov 2003
    Location
    London
    Posts
    169

    Unanswered: Select Into

    Hi,

    I am trying to do a select into statement and it aint working, the syntax is as follows:

    USE Downloads
    SELECT * INTO Downloads.dbo.MainDataStatic
    FROM Pelican.Downloads.dbo.MainDataStatic p

    When I run this in QA it brings up the following error:

    Server: Msg 2714, Level 16, State 6, Line 2
    There is already an object named 'MainDataStatic' in the database.

    What am I doing wrong?

  2. #2
    Join Date
    Feb 2004
    Posts
    1

    Cool

    Hi there,
    apparently there's already an object in your database with that name. This could be either a table, view or SP.

    Try this query in your database :

    select * from sysobjects where name='MainDataStatic' and se what it returns

  3. #3
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hello,

    Yep there is and its a table, what I want to do is update the data.

    Does select into try to create a new table then?

    in that case how do I select the data on server 'Pelican' in table MainDataStatic and then append into my new server called 'Dodo'. I only want the data that is not already in the table on the new server.

  4. #4
    Join Date
    Jan 2004
    Posts
    2

    Thumbs up

    Try this...

    USE Downloads
    INSERT dbo.MainDataStatic m
    SELECT *
    FROM Pelican.Downloads.dbo.MainDataStatic p
    WHERE NOT EXISTS
    (select * from dbo.MainDataStatic m2
    where m.[primary_key1] = m2.[primary_key1] and
    m.[primary_key2] = m2.[primary_key2] )

    The "NOT EXISTS" should stop duplicate data getting re-inserted to the new table if you run the query more than once. You'll need to substitute the [primary_key1], [primary_key2] part for the primary key columns in your MainDataStatic table.

  5. #5
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hi,

    Have you checked this? It does not work, you cannoty use an alias on an insert statement.

    Also what is this:

    WHERE NOT EXISTS
    (select * from dbo.MainDataStatic m2
    where m.[id] = m2.[id]

    This compares the same table??????????

  6. #6
    Join Date
    Jan 2004
    Posts
    2
    You're right of course what I sent was syntactically incorrect. Apologies for posting a howler Serves me right for typing some SQL into Notepad without trying it out!

    What I meant was this..

    USE Downloads
    INSERT dbo.MainDataStatic
    SELECT *
    FROM Pelican.Downloads.dbo.MainDataStatic p
    WHERE NOT EXISTS
    (select * from dbo.MainDataStatic m
    where m.[primary_key1] = p.[primary_key1] and
    m.[primary_key2] = p.[primary_key2] )

    As for joining a table to itself, self-joins do have uses and are perfectly valid syntax.

  7. #7
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    No worries mate, I do the same thing quite often aswell

    Ok got it to work (I think)

    I used this in the end:

    USE Downloads
    --TRUNCATE TABLE MainDataStatic
    INSERT dbo.MainDataStatic
    SELECT *
    FROM Pelican.Downloads.dbo.MainDataStatic p
    WHERE id NOT IN
    (select id from dbo.MainDataStatic)

    Thanks for you're help

Posting Permissions

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