Professional Web Applications Themes

REAL!! BUT SIMPLE SQL COMMAND - Microsoft SQL / MS SQL Server

I have two tables: TableA, TableB. The structure of TableA is exactly same as of TableB. TableB is empty. TableA has 1234 records. The structure of these tables are as under: Field1 int Field2 int Field3 int Field4 chat(3) ............. ............. ............. Field89 TIMESTAMP I want to copy all the records from TableA to TableB using SQL commad. So enter the following SQL command insert into tableB select * from tableA This gives me error message Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp ...

  1. #1

    Default REAL!! BUT SIMPLE SQL COMMAND

    I have two tables: TableA, TableB. The structure of TableA is exactly same
    as of TableB. TableB is empty. TableA has 1234 records. The structure of
    these tables are as under:

    Field1 int
    Field2 int
    Field3 int
    Field4 chat(3)
    .............
    .............
    .............
    Field89 TIMESTAMP

    I want to copy all the records from TableA to TableB using SQL commad.

    So enter the following SQL command

    insert into tableB
    select * from tableA

    This gives me error message
    Cannot insert a non-null value into a timestamp column. Use INSERT with a
    column list or with a default of NULL for the timestamp column.

    What should I do? Should I have to type all the fields in the select list
    (except timestamp field). I have to enter so many fields?????

    Is there any other short way? I am talking about SQL command not through
    DTS.

    Sender









    Sender Guest

  2. #2

    Default Re: REAL!! BUT SIMPLE SQL COMMAND

    > Field89 TIMESTAMP

    You're aware that TIMESTAMP isn't a datatype that has anything to do with
    DATE/TIME, right?


    Aaron Guest

  3. #3

    Default Re: REAL!! BUT SIMPLE SQL COMMAND

    If you need to preserve the Timestamp value, create the Timestamp column in
    the target table as binary (VARBINARY(8)), then you can insert the value.
    Otherwise, list the required columns (it's good practice to do this in
    production code anyway) and SQL will assign a new value to the Timestamp
    column on INSERT.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Re: REAL!! BUT SIMPLE SQL COMMAND

    > Hope you get my question. I want to copy all the fields from TableA to 

    I don't think you can, unless you follow David's suggestion and make it a
    different datatype. I agree with his assessment about using column lists as
    opposed to SELECT * ... and if you really have 89 columns, you should
    probably think about investigating a better relational design, before
    thinking about shortcuts to avoid typing out all the names.


    Aaron Guest

  5. #5

    Default Re: REAL!! BUT SIMPLE SQL COMMAND

    The conclusio is that I can't do it without changing the datatype. Regarding
    relational design I can't do anything because the database is already there
    (not designed by me). It a database of a Microsoft software. Many of the
    tables has even more than 89 fields like some has 112 fields.

    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... [/ref]
    in 
    >
    > I don't think you can, unless you follow David's suggestion and make it a
    > different datatype. I agree with his assessment about using column lists[/ref]
    as 


    Sender Guest

  6. #6

    Default Re: REAL!! BUT SIMPLE SQL COMMAND

    As already stated you need to spell out all the columns but if you have SQL
    2000 you can easily do this with the Object Browser of Query yzer. It
    will give you a list of all the columns so you can just delete what you
    don't want.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Sender" <com> wrote in message
    news:phx.gbl... 


    Andrew Guest

  7. #7

    Default Re: REAL!! BUT SIMPLE SQL COMMAND

    Got a real newbie question for you: How do you get the column names
    from the object browser to the statement?


    On Wed, 30 Jul 2003 12:47:12 -0700, "sender" <com> wrote:
     
    >but if you have SQL 
    >Query yzer. It 
    >delete what you [/ref]
    >TableA is exactly same [/ref]
    >The structure of [/ref]
    >using SQL commad. [/ref]
    >Use INSERT with a [/ref]
    >column. [/ref]
    >in the select list [/ref]
    >fields????? [/ref]
    >command not through 
    >>
    >>
    >>.
    >>[/ref][/ref]

    Random Guest

  8. #8

    Default Re: REAL!! BUT SIMPLE SQL COMMAND

    Just drag the column name from the object browser to your QA session window.

    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql
    ..
    "Random" <Randomnwhere> wrote in message news:com...
    Got a real newbie question for you: How do you get the column names
    from the object browser to the statement?


    On Wed, 30 Jul 2003 12:47:12 -0700, "sender" <com> wrote:
     
    >but if you have SQL 
    >Query yzer. It 
    >delete what you [/ref]
    >TableA is exactly same [/ref]
    >The structure of [/ref]
    >using SQL commad. [/ref]
    >Use INSERT with a [/ref]
    >column. [/ref]
    >in the select list [/ref]
    >fields????? [/ref]
    >command not through 
    >>
    >>
    >>.
    >>[/ref][/ref]

    Tom Guest

  9. #9

    Default Re: REAL!! BUT SIMPLE SQL COMMAND

    Right click on the table and it will give you a choice of creating an Insert
    / Select etc for that particular table. It also gives choices as to where
    you want it such as the clipboard or a new window and such.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Random" <Randomnwhere> wrote in message
    news:com... 
    > >but if you have SQL 
    > >Query yzer. It 
    > >delete what you 
    > >TableA is exactly same 
    > >The structure of 
    > >using SQL commad. 
    > >Use INSERT with a 
    > >column. 
    > >in the select list 
    > >fields????? 
    > >command not through [/ref]
    >[/ref]


    Andrew Guest

Similar Threads

  1. regexp matching- real simple!
    By Nandita Mullapudi in forum PERL Beginners
    Replies: 5
    Last Post: November 14th, 01:29 AM
  2. regexp matching- real simple!-GOT IT!
    By Nandita Mullapudi in forum PERL Beginners
    Replies: 0
    Last Post: November 13th, 11:35 PM
  3. Some real simple questions regarding sockets
    By Nimmi in forum UNIX Programming
    Replies: 10
    Last Post: October 25th, 12:37 AM
  4. real simple instructions
    By Ray at in forum ASP
    Replies: 2
    Last Post: September 27th, 05:38 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139