May 6, 2010 at 12:42 am
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
May 6, 2010 at 12:53 am
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.
May 6, 2010 at 1:13 am
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
May 6, 2010 at 4:12 am
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;-)
May 6, 2010 at 4:13 am
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!
May 6, 2010 at 6:35 am
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
May 6, 2010 at 8:33 am
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!!
May 6, 2010 at 8:57 am
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;-)
May 6, 2010 at 12:24 pm
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
May 7, 2010 at 5:19 am
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.
May 7, 2010 at 5:40 am
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;-)
May 7, 2010 at 6:06 am
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!
May 7, 2010 at 7:59 am
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
May 25, 2010 at 6:40 am
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
May 27, 2010 at 7:34 am
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