Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Location
    Australia
    Posts
    26

    Unanswered: copying tables between databases

    Hi!

    I have built a website and now my client wants to copy the data from another table [that i was i unaware of] into one of mine. This would be ok except the other table has one field that is text where mine is a number. The actual data contained is exactly the same and the person before me really should have used a number field but it's too late for that.

    I have modified my table but now i cannot inner join fields of different data types.

    SELECT [table1].*,[table2].* FROM [table1] INNER JOIN [table2] ON [table1].feild = [table2].field

    type mismatch!!!!

    How can i copy the data from one table if the datatypes are different but the data is identical?

    thanks for your help.

    Rim

  2. #2
    Join Date
    Jul 2002
    Posts
    22
    Hi,
    Your choices are:
    1. Change the field either to number in client table or to text in your table.

    2. Use Val function on client’s table (mind that his table must be populated by numerical values only!)

    SELECT [table1].*, [table2].* FROM [table1] INNER JOIN [table2] ON [table1]. field = Val([table2].field )

    Where Table1 is your table and Table2 is client’s one
    Please mind that after doing that you will be unable to see those tables in design view anymore.

    3. Create additional query2:

    SELECT Val([Table2].field) AS field_new , Table2.* FROM Table2

    Use it in your first query:

    SELECT [table1].*, [query2].* FROM [table1] INNER JOIN [query2] ON [table1]. field = [query2].field_new


    Good luck.

  3. #3
    Join Date
    May 2002
    Location
    Australia
    Posts
    26
    Thankyou! that is very helpfull. I am on my way to solving this problem i think!

    I know VBscript reasonalbly well but there is one thing i don't understand.

    I am creating the sql statement as a string and then connecting though an OBDC from an asp page. like this:

    sql = "Select * FROM table WHERE field = x"

    set conn = Server.CreateObject("ADODB.Connection")
    conn.open "odbcname"

    set cmd = Server.CreateObject("ADODB.Command")
    cmd.CommandText = sql
    set cmd.ActiveConnection = conn
    set rs = Server.CreateObject("ADODB.Recordset")
    set rs = cmd.Execute

    How do insert the val function in there?
    sql = "Select * FROM table WHERE field = ...?

    Thanks for your help.

  4. #4
    Join Date
    May 2002
    Location
    Australia
    Posts
    26
    got it!

    thanks for your help!

  5. #5
    Join Date
    Jul 2002
    Posts
    22
    hehe, u were faster than me

    anyway if your ASP is sitting on SQL server then u could try this:

    SELECT TABLE1.numer1, TABLE2.number2
    FROM dbo.TABLE1 INNER JOIN
    dbo.TABLE2 ON CONVERT(varchar(50), dbo.TABLE1.numer1)
    = dbo.TABLE2.number2

Posting Permissions

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