Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    24

    Unhappy Unanswered: Data Transpose - need help

    Hi all,
    Help me out, i am trying to get all values in Table A and insert into table B, i though of writing cursor. see endof the message



    Table A:
    CREATE TABLE [dbo].[M_SCANNEDSURVEY_AP] (
    [M_CustomerSurveyID] [bigint] NULL ,
    [SurveyID] [bigint] NULL ,
    [LoadStatus] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [1] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [3] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [4] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [5] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [6] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [7] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [8] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [9] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [10] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [11] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [12] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DateSubmitted] [datetime] NULL
    ) ON [PRIMARY]
    GO



    Table B:
    CREATE TABLE [dbo].[M_RESPONSE] (
    [M_CustomerSurveyID] [bigint] NOT NULL ,
    [SurveyID] [bigint] NOT NULL ,
    [SeqNumber] [bigint] NOT NULL ,
    [Response] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DateSubmitted] [datetime] NULL
    ) ON [PRIMARY]
    GO

    I want to insert everything in table A to table B
    M_CustomerSurveyID -> M_CustomerSurveyID
    SurveyID -> SurveyID
    I will hardcode insert 1,2,3,4,5,6,7,8,9,10,11,12 for sequence number
    values of 1,2,3,4,5,6,7,8,9,10,11,12 -> Response
    DateSubmitted -> DateSubmitted



    Declare @Count_Scan INT

    BEGIN
    -- Get the count of scanned data in scanned data table with LoadStatus = N
    SELECT @Count_Scan = COUNT(*) from M_SCANNEDSURVEY_APTEST where
    LoadStatus = 'N'

    IF @Count_Scan > 0
    BEGIN
    DECLARE ScanData_Cursor CURSOR FOR
    SELECT * FROM M_SCANNEDSURVEY_AP WHERE LoadStatus = 'N'
    OPEN ScanData_Cursor
    FETCH NEXT FROM ScanData_Cursor
    While (@@Fetch_Status <> -1)
    Begin
    If (@@Fetch_Status = -2)
    Begin
    FETCH NEXT FROM ScanData_Cursor
    Continue
    End
    CLOSE ScanData_Cursor
    DEALLOCATE ScanData_Cursor
    END

    SET @CountSuccess = 'Y'
    END

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    Use your script which created TableA to create TableB. Export data from TableA and import it to TableB.

  3. #3
    Join Date
    Dec 2002
    Posts
    24
    I want to do it automatically once in a day using dts, how can i insert the data into table B

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    INSERT INTO TableB(Field1, Field2, Field3, ...)
    SELECT Field1, Field2, Field3, ... FROM TableA

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And never use SELECT * in code....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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