select case Insert, Update

  • Could anybody give an idea where lam going wrong.

    i have checked the insert and update and they work on there own, and I have tried the case statement and that works if i take out the insert/update.

    select field1='21',field2=

    case when field2 = 'I' then '1'

    begin

    (INSERT INTO BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21

    (Record_Identifier,

    Change_Type,

    Pro_Order,

    UPRN)

    Select Field1,Field2,Field3,Field4

    From MASTER_TABLE))

    --Where Field1 = '21' AND Field2 = 'I' and field3 = @RecordIDToHandle

    end

    when field2 = 'U' then

    begin

    update BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21

    SET Record_Identifier = field1,

    Change_Type = field2,

    Pro_Order = field3,

    UPRN = field4

    from master_table

    end

    else

    '3'

    end

    from master_table

  • Can you tell us what exactly you want to do with the case statement

    in the first case statement you already said as 'Then 1' and wrote insert statements.

    Please check the syntax of the case statement.

  • I have two tables, one called BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21 and one called Master_table. the Master_table has data as shown below:

    Field1 ¦ Field2 ¦ Field3 ¦ Field4

    -----------------------------------------

    21 ¦ I ¦ 1 ¦ 10024414482

    24 ¦ I ¦ 2 ¦ 10024414482

    21 ¦ U ¦ 3 ¦ 10004678137

    24 ¦ U ¦ 4 ¦ 10004678137

    24 ¦ I ¦ 5 ¦ 10004678137

    21 ¦ U ¦ 6 ¦ 10004678205

    if the field1 = 21 and field2 = I (insert) or U(update) then these alter the data in

    BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21.This process has to be done row by row ( which isn't the best solution, I Know) sequentially by Field3 ie 1,2,3.

    the "1" was left in for testing

  • clucasi (5/6/2010)


    Could anybody give an idea where lam going wrong.

    i have checked the insert and update and they work on there own, and I have tried the case statement and that works if i take out the insert/update.

    select field1='21',field2=

    case when field2 = 'I' then '1'

    begin

    (INSERT INTO BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21

    (Record_Identifier,

    Change_Type,

    Pro_Order,

    UPRN)

    Select Field1,Field2,Field3,Field4

    From MASTER_TABLE))

    --Where Field1 = '21' AND Field2 = 'I' and field3 = @RecordIDToHandle

    end

    when field2 = 'U' then

    begin

    update BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21

    SET Record_Identifier = field1,

    Change_Type = field2,

    Pro_Order = field3,

    UPRN = field4

    from master_table

    end

    else

    '3'

    end

    from master_table

    this query wont work ; actually this CASE doesnt work.

    you need to work like this

    select @field2 = field2 from master_table where field1 = 21

    --fetching one by one records from above statement

    while loop

    if @field2 = 'I'

    insert statement

    else if @field2 = 'U'

    update statement

    set counter

    end loop

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • This must be done in 2 phases (i mean, 2 different queries)!

    1. Find out the entries that have "I", use LEFT JOIN and insert them into your target table.

    2. For the rows with "U", use INNER JOIN and update the targettable with values from source table.

    Please provide us :

    1. Table Structures - CREATE TABLE scripts

    2. Constraints or Indexes in the table, - CREATE SCRIPTS

    3. SAMPLE DATA - INSERT INTO TABLE scripts

    4. Desired output - some visual representation of this.

    We wil dart back to you with optimized, fast-running , tested code . If you dont know how to cook data for the 4 above-mentioned points, please go through the following article:

    CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

    Cheers!

  • the master table is

    USE [DEV_Test]

    GO

    /****** Object: Table [dbo].[MASTER_TABLE] Script Date: 05/06/2010 11:51:15 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[MASTER_TABLE](

    [Field1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field3] [int] NULL,

    [Field4] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field5] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field6] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field7] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field8] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field9] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field10] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field11] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field12] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field13] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field14] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field15] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field16] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field17] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field18] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field19] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field20] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field21] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field22] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field23] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field24] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field25] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field26] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field27] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field28] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field29] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Field30] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    the table for the BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21

    USE [DEV_Test]

    GO

    /****** Object: Table [dbo].[BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21] Script Date: 05/06/2010 11:53:18 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21](

    [RECORD_IDENTIFIER] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CHANGE_TYPE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PRO_ORDER] [float] NULL,

    [UPRN] [float] NULL,

    [LOGICAL_STATUS] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [BLPU_STATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [BLPU_STATE_DATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [BLPU_CLASS] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PARENT_UPRN] [float] NULL,

    [X_COORDINATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Y_COORDINATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [RPA] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [LOCAL_CUSDODIAN_CODE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [START_DATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [END_DATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [LAST_UPDATE_DATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ENTRY_DATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ORGANISATION] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [WARD_CODE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PARISH_CODE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CUSTODIAN_ONE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CUSTODIAN_TWO] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CAN_KEY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    USE [DEV_Test]

    GO

    /****** Object: Table [dbo].[LAND_AND_PROPERTY_IDENTIFIER_RECORD24] Script Date: 05/06/2010 13:14:28 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[LAND_AND_PROPERTY_IDENTIFIER_RECORD24](

    [RECORD_IDENTIFIER] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CHANGE_TYPE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PRO_ORDER] [float] NULL,

    [UPRN] [float] NULL,

    [LPI_KEY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [LANGUAGE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [LOGICAL_STATUS] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [START_DATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [END_DATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ENTRY_DATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [LAST_UPDATE_DATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [SAO_START_NUMBER] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [SAO_START_SUFFIX] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [SAO_END_NUMBER] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [SAO_END_SUFFIX] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [SAO_TEXT] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PAO_START_NUMBER] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PAO_START_SUFFIX] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PAO_END_NUMBER] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PAO_END_SUFFIX] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PAO_TEXT] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [USRN] [float] NULL,

    [LEVEL] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [POSTALLY_ADDRESSABLE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [POSTCODE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [POST_TOWN] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [OFFICIAL_FLAG] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CUSTODIAN_ONE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CUSTODIAN_TWO] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CAN_KEY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    SET IDENTITY_INSERT #MASTER_TABLE ON

    INSERT INTO #MASTER_TABLE

    (FIELD1,FIELD2,FIELD3,FIELD4)

    Select '21','I','1','10024414482' UNION ALL

    Select '24','I','2','10024414482' UNION ALL

    Select '21','U','3','10004678137' UNION ALL

    Select '24','U','4','10004678137' UNION ALL

    Select '24','I','5','10004678137' UNION ALL

    Select '21','U','6','10004678205' UNION ALL

    Select '24','U','7','10004678205' UNION ALL

    Select '21','U','8','10004692828' UNION ALL

    Select '21','I','9','10024414483' UNION ALL

    Select '24','I','10','10024414483' UNION ALL

    Select '21','U','11','10024414483' UNION ALL

    Select '21','U','12','10024414479' UNION ALL

    Select '21','U','13','10024414479' UNION ALL

    Select '24','U','14','77044341' UNION ALL

    Select '24','I','15','77044341' UNION ALL

    Select '21','I','16','10090237312' UNION ALL

    Select '24','I','17','10090237312' UNION ALL

    Select '21','U','18','1.00E+11' UNION ALL

    Select '21','U','19','10090237312' UNION ALL

    Select '21','I','20','10090237313' UNION ALL

    Select '24','I','21','10090237313' UNION ALL

    Select '21','U','22','77013163' UNION ALL

    Select '21','U','23','10090237313' UNION ALL

    Select '21','I','24','10090237314' UNION ALL

    Select '24','I','25','10090237314' UNION ALL

    Select '21','U','26','10023045971' UNION ALL

    Select '21','I','27','10090237315' UNION ALL

    Select '24','I','28','10090237315' UNION ALL

    Select '21','U','29','77034208' UNION ALL

    Select '21','U','30','10090237315' UNION ALL

    Select '21','I','31','10090237316' UNION ALL

    Select '24','I','32','10090237316' UNION ALL

    Select '21','I','33','10090237317' UNION ALL

    Select '24','I','34','10090237317' UNION ALL

    Select '21','U','35','10024148940' UNION ALL

    Select '21','U','36','10090237317' UNION ALL

    Select '21','U','37','10090237316' UNION ALL

    Select '21','U','38','77122547' UNION ALL

    Select '24','U','39','77122547' UNION ALL

    Select '21','I','40','10090237318' UNION ALL

    Select '24','I','41','10090237318' UNION ALL

    Select '21','I','42','10090237319' UNION ALL

    Select '24','I','43','10090237319' UNION ALL

    Select '21','U','44','10090237319' UNION ALL

    Select '24','U','45','10090237319' UNION ALL

    Select '24','D','46','10090237319' UNION ALL

    Select '21','D','47','10090237319' UNION ALL

    Select '21','U','48','10090237318' UNION ALL

    Select '24','U','49','10090237318' UNION ALL

    Select '24','D','50','10090237318' UNION ALL

    Select '21','D','51','10090237318' UNION ALL

    Select '21','I','52','10090237320' UNION ALL

    Select '24','I','53','10090237320' UNION ALL

    Select '21','I','54','10090237321' UNION ALL

    Select '24','I','55','10090237321' UNION ALL

    Select '21','U','56','10090237320' UNION ALL

    Select '21','U','57','10090237321' UNION ALL

    Select '21','U','58','10090237320' UNION ALL

    Select '24','U','59','10090237320' UNION ALL

    Select '21','U','60','10090237321' UNION ALL

    Select '24','U','61','10090237321' UNION ALL

    Select '21','U','62','10090237320' UNION ALL

    Select '24','U','63','10090237320' UNION ALL

    Select '24','D','64','10090237320' UNION ALL

    Select '21','D','65','10090237320' UNION ALL

    Select '21','U','66','10090237321' UNION ALL

    Select '24','U','67','10090237321' UNION ALL

    Select '24','D','68','10090237321' UNION ALL

    Select '21','D','69','10090237321' UNION ALL

    Select '24','U','70','10023060936' UNION ALL

    Select '24','D','71','10023060936' UNION ALL

    Select '21','D','72','10023060936' UNION ALL

    Select '21','I','73','10090237322' UNION ALL

    Select '24','I','74','10090237322' UNION ALL

    Select '21','I','75','10090237323' UNION ALL

    Select '24','I','76','10090237323' UNION ALL

    Select '21','U','77','10023055912' UNION ALL

    Select '21','I','78','10090237324' UNION ALL

    Select '24','I','79','10090237324' UNION ALL

    Select '21','U','80','10023049670' UNION ALL

    Select '21','U','81','10090237324' UNION ALL

    Select '24','I','82','10090237324' UNION ALL

    Select '21','I','83','10090237325' UNION ALL

    Select '24','I','84','10090237325' UNION ALL

    Select '21','U','85','10070862082' UNION ALL

    Select '21','U','86','10023058792' UNION ALL

    Select '21','U','87','10023059285' UNION ALL

    Select '21','U','88','10023059486' UNION ALL

    Select '21','U','89','10023054501' UNION ALL

    Select '21','U','90','10023060433' UNION ALL

    Select '21','U','91','10023060433' UNION ALL

    Select '21','U','92','77124488' UNION ALL

    Select '21','U','93','10023055169' UNION ALL

    Select '21','U','94','77108229' UNION ALL

    Select '21','I','95','10090237326' UNION ALL

    Select '24','I','96','10090237326' UNION ALL

    Select '21','I','97','10090237327' UNION ALL

    Select '24','I','98','10090237327'

    SET IDENTITY_INSERT #MASTER_TABLE OFF

    I have included 3 tables 21 into BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21

    24 goes into LAND_AND_PROPERTY_IDENTIFIER_RECORD24

    and master table

  • I don't exactly understand your requirement, but based on your CASE statement in the first post and some bits-and-pieces i understood from other posts, i have coded one query here. I don't know if that's what you are after, but for now keep this!

    NOTE: This uses the CURSOR method, and i am pretty sure there are many efficient methods to do this. And i smell the no-c.u.r.s.o.r-ever camp is just around the corner to scrutinize this code :-D!

    Here is one method!

    Assumptions in this method:

    1. You want to modify the table corresponding to FIELD1 in MASTER_TABLE , say when FIELD1 = 21 , then modify data in BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21, with the values from MASTER_TABLE corresponding to FIELD1 = 21

    2. With the same assumption above, if FIELD2 = I , then INSERT data corresponding to FIELD1 = 21 into BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21

    3. With the same assumption above, if FIELD2 = U , then UPDATE data corresponding to RECORD_IDENTIFIER = FIELD1 and PRO_ORDER = FIELD3 in BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21

    Carrying forward these assumptions, here is the code that will do the above said:

    DECLARE @TABLE_NAME VARCHAR(500)

    DECLARE @FIELD1 NVARCHAR(255)

    DECLARE @FIELD2 NVARCHAR(255)

    DECLARE @FIELD3 INT

    DECLARE @FIELD4 NVARCHAR(50)

    DECLARE @INSERT_QUERY VARCHAR(4000)

    DECLARE @INSERT_COLS VARCHAR(500)

    SET @INSERT_COLS = ' (RECORD_IDENTIFIER,CHANGE_TYPE,PRO_ORDER ,UPRN) '

    DECLARE @UPDATE_QUERY VARCHAR(4000)

    SET @INSERT_QUERY = 'INSERT INTO '

    SET @UPDATE_QUERY = 'UPDATE '

    DECLARE LOCAL_CUR CURSOR LOCAL

    FOR

    SELECT 'BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE'+CAST(FIELD1 AS VARCHAR) TABLE_NAME,

    FIELD1, FIELD2,FIELD3,FIELD4

    FROM MASTER_TABLE

    ORDER BY FIELD1, FIELD3

    OPEN LOCAL_CUR

    FETCH NEXT FROM LOCAL_CUR INTO @TABLE_NAME ,@FIELD1, @FIELD2 , @FIELD3 , @FIELD4

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @INSERT_QUERY = 'INSERT INTO '

    SET @UPDATE_QUERY = 'UPDATE '

    IF @FIELD2 = 'I'

    BEGIN

    SET @INSERT_QUERY = @INSERT_QUERY + @TABLE_NAME + @INSERT_COLS +

    ' SELECT '''+ @FIELD1+''', ''' +@FIELD2+''', ' +CAST(@FIELD3 AS VARCHAR)+', ''' +@FIELD4+''''

    PRINT @INSERT_QUERY

    -- EXEX (@INSERT_QUERY)

    END

    IF @FIELD2 = 'U'

    BEGIN

    SET @UPDATE_QUERY = @UPDATE_QUERY + @TABLE_NAME + +

    ' SET RECORD_IDENTIFIER = '''+ @FIELD1+''',CHANGE_TYPE = ''' +

    @FIELD2+''',PRO_ORDER = ' +CAST(@FIELD3 AS VARCHAR)+',UPRN = ''' +@FIELD4+''''+

    ' WHERE RECORD_IDENTIFIER = '''+ @FIELD1+''' AND PRO_ORDER = '+CAST(@FIELD3 AS VARCHAR)+''

    PRINT @UPDATE_QUERY

    -- EXEX (@UPDATE_QUERY)

    END

    SET @INSERT_QUERY = ''

    SET @UPDATE_QUERY = ''

    FETCH NEXT FROM LOCAL_CUR INTO @TABLE_NAME ,@FIELD1, @FIELD2 , @FIELD3 , @FIELD4

    END

    CLOSE LOCAL_CUR

    DEALLOCATE LOCAL_CUR

    The above code will print the query that will be executed. Once you are satisfied that the printed result is what you want, you can remove the "commented" part in the above code so that the query will perform it's task!

    Hope this helps and please revert back to us if it helped! If not, then only you can help us by posting your requirement in a way that is crystal-clear!

    Cheers!!

  • ColdCoffee (5/6/2010)


    NOTE: This uses the CURSOR method, and i am pretty sure there are many efficient methods to do this. And i smell the no-c.u.r.s.o.r-ever camp is just around the corner to scrutinize this code :-D!

    How about iteration with the help of WHILE loop 🙂 ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (5/6/2010)


    ColdCoffee (5/6/2010)


    NOTE: This uses the CURSOR method, and i am pretty sure there are many efficient methods to do this. And i smell the no-c.u.r.s.o.r-ever camp is just around the corner to scrutinize this code :-D!

    How about iteration with the help of WHILE loop 🙂 ?

    Oh yeah, super fast, express bullet code :-P:-D

  • Thanks for the solution,

    This query has to work on other tables as well ie

    Street Descriptortype 15 when id(15), Street record-Type11 when id(11).

    and it also has to delete records.

    when the id the table is 24 the table name is LAND_AND_PROPERTY_IDENTIFIER_RECORD24 so the query will need to be modified.

    The master table will have about 187000 records in, what kind of time frame would we expect the query to take.

  • clucasi (5/7/2010)


    what kind of time frame would we expect the query to take.

    depends on various factors like

    hardware config, network io, vol of data in tables ,indexes, how much query is optimal, how much sql server is busy 🙂

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • clucasi (5/7/2010)


    Thanks for the solution,

    You're Welcome, clucasi! Glad that my code helped you!

    This query has to work on other tables as well ie

    Street Descriptortype 15 when id(15), Street record-Type11 when id(11).

    and it also has to delete records.

    I did not understand this requirement one-bit. Please elaborate this buddy!

    when the id the table is 24 the table name is LAND_AND_PROPERTY_IDENTIFIER_RECORD24 so the query will need to be modified.

    The code i have given will do this iteself. Whaever be the table number is, the code will insert/update the data from source table to the table with that number! Say when the table number is 21, it will update LAND_AND_PROPERTY_IDENTIFIER_RECORD21 and if the table number is 24, it will update LAND_AND_PROPERTY_IDENTIFIER_RECORD24. Now worries for you in this with my code!

    The master table will have about 187000 records in, what kind of time frame would we expect the query to take.

    This totally depends on many factors as Bhuvnesh has stringed! And also, as the code is a cursor based one, it will from its side slow up the process!

    Cheers!

  • Sorry iI wasn't clear, I will give a more complete overview of what I want to do.

    There are 5 tables

    Basic_Land_And_Property_Unit_Record_Type21

    Land_And Property Identifier_Record24

    Street_Descriptor_Record15

    Street_Record_Type11

    Master_Table

    Each Tables holds different amounts of data

    The master_table has 30 fields.

    field1 holds the table reference, which are 11, 15, 21, 24 (these reference the tables 11 = street_Record_Type11)

    field2 holds the action that needs to happen on the table (I=insert, U=update & D=delete)

    filed3 holds the sequence No. The records have to be processed in this order (Row by Row)

    field4 to filed30 holds data that may be inserted or updated in the different tables

    The master_table is imported each month and it has about 187000 records to be action.

    Table 11,15,21,24 has about 1000000 rows of data in each.

    The code you supplied was great but is going to have to be altered to account for the different tables.

    This procedure is going to used within BIDs.

    cheers

  • hi

    i am having trouble getting the below statement to work

    IF @FIELD2 = 'I' and @FIELD1='24'

    BEGIN

    SET @INSERT_QUERY = @INSERT_QUERY + 'LAND_AND_PROPERTY_IDENTIFIER_RECORD24' + @INSERT_COLS4 +

    ' SELECT '''+ @FIELD1+''', ''' +@FIELD2+''', ' +CAST(@FIELD3 AS VARCHAR)+', ''' +@FIELD4+''', ''' +@FIELD5+''', ''' +@FIELD6+''', ''' +@FIELD7+''', ''' +@FIELD8+''', '''+@FIELD9+''', ''' +@FIELD10+''', ''' +@FIELD11+''', ''' +@FIELD12+''', ''' +@FIELD13+''', ''' +@FIELD14+''', ''' +@FIELD15+''', ''' +@FIELD16+''', ''' +@FIELD17+''', ''' +@FIELD18+''', ''' +@FIELD19+''', ''' +@FIELD20+''', ''' +@FIELD21+''', ''' +@FIELD22+''', ''' +@FIELD23+''', ''' +@FIELD24+''', ''' +@FIELD25+''', ''' +@FIELD26+''', ''' +@FIELD27+'''' +

    ', ''' +@FIELD28+''''

    PRINT @INSERT_QUERY

    EXEC (@INSERT_QUERY)

    END

    if I run the procedure the code above doesn't seem to execute but i take out +@FIELD28+ it runs fine.

    any ideas

  • if I run the procedure the code above doesn't seem to execute but i take out +@FIELD28+ it runs fine.

    Just a guess, but if @FIELD28 IS NULL, concatenating it will convert that entire statement to NULL.

    You should either explicitly set all your varialbles = '' or use COALESCE when you concatenate.

Viewing 15 posts - 1 through 14 (of 14 total)

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