locked
Moving Data from One Table to Another RRS feed

  • Question

  • Dear All

    At present I have a rather large table called Observation which looks a bit like this:

    observationID, observationFor, observationBy, observationTime, observationLocation, observationCriteria1, observationCriteria2, observationCriteria3, ..., observationCriteria10 etc

    Unfortunately it has been like that some time which is clearly incorrect. I would like to move the observationCriteria columns to a related table instead:

    Observation (PK: observationID) -------->> Criteria (PK: criteriaID, FK: observationID)

    Is it possible to write a stored procedure to look at each existing row of data in the Observation table in my database and generate a new row in the Criteria table to move the data over? Or should I be doing this some other way?

    Many thanks in advance

    Daniel

    Monday, August 8, 2011 3:49 PM

Answers

  • Hi Dj

    I think your are looking for script like below , I have also created the DDL and data for our future references....

    --static data
    
    use tempdb
    create table #observation
    (
    	observationID			int
    	,observationFor			varchar(100)
    	,observationBy			varchar(100)
    	,observationTime		datetime
    	,observationLocation	varchar(100)
    	,observationCriteria1	varchar(100)
    	,observationCriteria2	varchar(100)
    	,observationCriteria3	varchar(100)
    	,observationCriteria4	varchar(100)
    	,observationCriteria5	varchar(100)
    	,observationCriteria6	varchar(100)
    	,observationCriteria7	varchar(100)
    	,observationCriteria8	varchar(100)
    	,observationCriteria9	varchar(100)
    	,observationCriteria10	varchar(100)
    )
    
    
    insert into #observation values (1,'abcd1','erf1',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (2,'abcd2','erf2',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (3,'abcd3','erf3',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (4,'abcd4','erf4',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (5,'abcd5','erf5',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (6,'abcd6','erf6',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (7,'abcd7','erf7',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (8,'abcd8','erf8',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (9,'abcd9','erf9',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (0,'abcd0','erf0',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    
    
    --- script that needs to be executed
    
    SELECT * INTO #observation_orig FROM #observation
    
    DROP TABLE #observation
    
    CREATE TABLE #observation
    (
    	observationID			int
    	,observationFor			varchar(100)
    	,observationBy			varchar(100)
    	,observationTime		datetime
    	,observationLocation	varchar(100)
    )
    
    create table #observationCriteria 
    (
    	criteriaId		INT
    	,ObservationId	INT
    	,Criteria		VARCHAR(100)
    )
    
    INSERT INTO #observation
    select distinct observationID,observationFor,observationBy,observationTime,observationLocation FROM #observation_orig
    
    IF OBJECT_ID('tempdb..#criteraIds') is not null
    	drop table #criteraIds
    	
    select CAST(SUBSTRING(column_name,20,LEN(column_name)) as int) criteriaID 
    into #criteraIds 
    from INFORMATION_SCHEMA.COLUMNS 
    where TABLE_NAME = object_name(OBJECT_ID('tempdb..#observation_orig')) 
    and COLUMN_NAME like 'observationCriteria%'
    
    insert into #observationCriteria (criteriaId,ObservationId)
    SELECT DISTINCT C.criteriaID,o.observationID
    FROM #criteraIds C
    CROSS JOIN #observation O
    
    declare @minID int, @maxID int,@counter int,@sql varchar(4000)
    select @minID = MIN(criteriaID) from #criteraIds
    select @maxID = MAX(criteriaID) from #criteraIds
    set @counter = @minID
    WHILE (@counter <= @maxID)
    BEGIN
    	SELECT @sql = 'update OC set criteria = oo.observationCriteria'+CAST(@counter as varchar)
    					+' FROM #observationCriteria oc JOIN #observation_orig oo ON oo.observationId = oc.observationId and oc.criteriaID = ' + CAST(@counter as varchar)
    	exec (@sql)					
    	--print @sql
    	SET @counter = @counter +1
    END
    
    
    select * from #observationCriteria
    

     

     

     


    Nothing is Permanent... even Knowledge....
    My Blog
    • Marked as answer by Kalman Toth Monday, August 15, 2011 4:47 AM
    Monday, August 8, 2011 6:14 PM

All replies

  • Hi:

    Select * from Observation o Left Join Criteria c on o.observationID=c.observationID Where c.observationID IS NULL
    

    Take the dataset to insert your target table.


    Shadowと愉快なコード達
    Monday, August 8, 2011 4:28 PM
  • Hi Dj

    I think your are looking for script like below , I have also created the DDL and data for our future references....

    --static data
    
    use tempdb
    create table #observation
    (
    	observationID			int
    	,observationFor			varchar(100)
    	,observationBy			varchar(100)
    	,observationTime		datetime
    	,observationLocation	varchar(100)
    	,observationCriteria1	varchar(100)
    	,observationCriteria2	varchar(100)
    	,observationCriteria3	varchar(100)
    	,observationCriteria4	varchar(100)
    	,observationCriteria5	varchar(100)
    	,observationCriteria6	varchar(100)
    	,observationCriteria7	varchar(100)
    	,observationCriteria8	varchar(100)
    	,observationCriteria9	varchar(100)
    	,observationCriteria10	varchar(100)
    )
    
    
    insert into #observation values (1,'abcd1','erf1',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (2,'abcd2','erf2',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (3,'abcd3','erf3',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (4,'abcd4','erf4',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (5,'abcd5','erf5',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (6,'abcd6','erf6',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (7,'abcd7','erf7',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (8,'abcd8','erf8',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (9,'abcd9','erf9',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    insert into #observation values (0,'abcd0','erf0',GETDATE(),'NY','1','2','3','4','5','6','7','8','9','0')
    
    
    --- script that needs to be executed
    
    SELECT * INTO #observation_orig FROM #observation
    
    DROP TABLE #observation
    
    CREATE TABLE #observation
    (
    	observationID			int
    	,observationFor			varchar(100)
    	,observationBy			varchar(100)
    	,observationTime		datetime
    	,observationLocation	varchar(100)
    )
    
    create table #observationCriteria 
    (
    	criteriaId		INT
    	,ObservationId	INT
    	,Criteria		VARCHAR(100)
    )
    
    INSERT INTO #observation
    select distinct observationID,observationFor,observationBy,observationTime,observationLocation FROM #observation_orig
    
    IF OBJECT_ID('tempdb..#criteraIds') is not null
    	drop table #criteraIds
    	
    select CAST(SUBSTRING(column_name,20,LEN(column_name)) as int) criteriaID 
    into #criteraIds 
    from INFORMATION_SCHEMA.COLUMNS 
    where TABLE_NAME = object_name(OBJECT_ID('tempdb..#observation_orig')) 
    and COLUMN_NAME like 'observationCriteria%'
    
    insert into #observationCriteria (criteriaId,ObservationId)
    SELECT DISTINCT C.criteriaID,o.observationID
    FROM #criteraIds C
    CROSS JOIN #observation O
    
    declare @minID int, @maxID int,@counter int,@sql varchar(4000)
    select @minID = MIN(criteriaID) from #criteraIds
    select @maxID = MAX(criteriaID) from #criteraIds
    set @counter = @minID
    WHILE (@counter <= @maxID)
    BEGIN
    	SELECT @sql = 'update OC set criteria = oo.observationCriteria'+CAST(@counter as varchar)
    					+' FROM #observationCriteria oc JOIN #observation_orig oo ON oo.observationId = oc.observationId and oc.criteriaID = ' + CAST(@counter as varchar)
    	exec (@sql)					
    	--print @sql
    	SET @counter = @counter +1
    END
    
    
    select * from #observationCriteria
    

     

     

     


    Nothing is Permanent... even Knowledge....
    My Blog
    • Marked as answer by Kalman Toth Monday, August 15, 2011 4:47 AM
    Monday, August 8, 2011 6:14 PM
  • Thank you! I'm finding that very complicated... still trying to get my head around it all. I guess there's no easy solution when your data is in a mess.

    I'm very grateful for you help.

    Daniel

    Monday, August 8, 2011 7:23 PM
  • The below is the explanation of what the script above does....

    1. We will take a backup of your existing data into _orig table.
    2. We will drop your actual table and re-create it with the only columns that are needed
    3. We will create another new table which will hold the Criteria
    4. Now your observation table is populated with all the data that is required to be in it, (observationID).
    5. Now we will populate all the possible combinations of the observationId and criteriaID into the criteria table, leaving the actual criteria value as blank
    6. Now write a dynamic SQL to update the criteria value from the original data table by joining on the observationID.

    I hope the above explanation might aid you in understanding my script....

     


    Nothing is Permanent... even Knowledge....
    My Blog
    Monday, August 8, 2011 7:28 PM