Post reply

INSERT WITH SELECT

  • July 26, 2005 at 2:45 pm

    #86962

    Why does this not work?

    INSERT

    INTO dbo_Versuchsart VALUES (SELECT * FROM AuswahlVersuchsart)

    Here the table definition:

    CREATE

    TABLE [Versuchsart] (

    [VersuchsartID] [int] IDENTITY (5, 1) NOT NULL ,

    [SchaetzungID] [int] NOT NULL ,

    [Nr] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,

    [Bezeichnung] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,

    [Vorschrift] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,

    [Lastenheft] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,

    [Beschreibung] [text] COLLATE Latin1_General_CI_AS NULL ,

    [Optional] [int] NOT NULL CONSTRAINT [DF__Versuchsa__Optio__5812160E] DEFAULT (0),

    [Kombiniert] [int] NOT NULL CONSTRAINT [DF__Versuchsa__Kombi__59063A47] DEFAULT (0),

    [Zeilenart] [int] NOT NULL CONSTRAINT [DF__Versuchsa__Zeile__59FA5E80] DEFAULT (0),

    [AcVersuchsartID] [int] NOT NULL ,

    [AcSchaetzungID] [int] NOT NULL ,

    PRIMARY KEY CLUSTERED

    (

    [VersuchsartID]

    )

    ON [PRIMARY]

    )

    ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    (Do ask SQL toad where the idiotic constraints are coming from...)

  • July 26, 2005 at 2:50 pm

    #577316

    INSERT INTO dbo.[Versuchsart] (

    [SchaetzungID]  ,

    [Nr],

    [Bezeichnung]  ,

    [Vorschrift] ,

    [Lastenheft] ,

    [Beschreibung]  ,

    [Optional] ,

    [Kombiniert] ,

    [Zeilenart] ,

    [AcVersuchsartID]  ,

    [AcSchaetzungID]

    )

    select

    [SchaetzungID]  ,

    [Nr],

    [Bezeichnung]  ,

    [Vorschrift] ,

    [Lastenheft] ,

    [Beschreibung]  ,

    [Optional] ,

    [Kombiniert] ,

    [Zeilenart] ,

    [AcVersuchsartID]  ,

    [AcSchaetzungID]

    From

    AuswahlVersuchsart


    * Noel

  • July 26, 2005 at 2:57 pm

    #577318

    Okay, this works, but it is a too long string for VBA in Access. Got an idea?

  • July 26, 2005 at 8:51 pm

    #577373

    change your query to:

    INSERT INTO dbo_Versuchsart

    SELECT * FROM AuswahlVersuchsart







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 27, 2005 at 10:34 am

    #577629

    I doubt that!!! VBA can handle very very ... did I said very large strings

    Can you post your vba Code?

     


    * Noel

  • July 27, 2005 at 10:36 am

    #577630

    Nope!!

    You should not do that it is considered a bad practice and you are giving to "chance" the order of the columns. To be really in control you need to specify the filed list in both the select and the insert statement

     


    * Noel

  • July 27, 2005 at 10:39 am

    #577632

    I think it depends Noel - I have full control over my db at work and KNOW it's never going to change - it might be torn down and rebuilt (whole new story that....) but not redesigned!

    Martin was looking for brevity - & I gave him that!

    btw - wrong forum but I'm really curious - what communist country and for how long ?!

     







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 27, 2005 at 1:27 pm

    #577698

    Cuba, 30 yrs


    * Noel

  • July 27, 2005 at 2:10 pm

    #577713

    WOW! Who'd have thought?!?! I always thought you were french (your name

    Habla Español then ?! am still reeling under the shock of this revelation!







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 27, 2005 at 2:21 pm

    #577715

    Yep, You never know

     


    * Noel

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply