SQL - Copy Data Within Same Table

Question

I'm not that great with SQL Server, but I'm trying to do some behind the scenes work to create some functionality that our EMR system lacks - copying forms (and all their data) between patients.

In SQL Server 2008 R2 I have three tables that deal with these forms that have been filled out:

**Table 1**
encounter_id  patient_id  date        time          etc  etc  etc  etc
1234          112233      2014-01-02  14:25:01:00   a    b    c    d

**Table 2**
encounter_id  page  recorded_on  recorded_by  etc   etc
1234          1     2014-01-02   134          asdf  asdf
1234          2     2014-01-02   134          jkl;  jkl;

**Table 3**
encounter_id  page  keyname  keyvalue
1234          1     key1     aaa
1234          1     key2     bbb
1234          1     key3     ccc
1234          1     key4     ddd
1234          2     key5     eee
1234          2     key6     fff
1234          2     key7     ggg

As you can see, they all match together with the encounter_id, which is linked to the patient_id (In the first table).

What I'm trying to be able to do is copy all the rows in these three tables for a particular encounter_id back into the same table they come from, but with a different (system generated) encounter_id for a patient_id that I would specify. In essence, copying the form from one patient to another.

Any help on this is greatly appreciated.

Answer 1

Made a little fiddle as an example, here (link)

The solution is perhaps needlessly complex but it offers a good variety of other useful stuff as well, I just wanted to test how to build that dynamically. The script does print out the commands, making it relatively easy to remove the TSQL and just produce the plain-SQL to do as you wish.

What it does, is that it requires an encounter_id, which it will then use to dynamically fetch the columns (with the assumption that encounter_id is the PK for TABLE_1) to insert a new record in TABLE_1, store the inserted.encounter_id value, and use that value to fetch and copy the matching rows from TABLE_2 and TABLE_3.

Basically, as long as the structure is correct (TABLE_1 PK is encounter_id which is an identity type), you should be able to just change the table names referenced in the script and it should work directly regardless of which types of columns (and how many of them) your particular tables have.

The beef of the script is this:

/* Script begins here */
DECLARE @ENCOUNTER_ID INT, @NEWID INT, @SQL VARCHAR(MAX), @COLUMNS VARCHAR(MAX)
IF OBJECT_ID('tempdb..##NEW_ID') IS NOT NULL
    DROP TABLE ##NEW_ID
CREATE TABLE ##NEW_ID (ID INT)

/* !!! SET YOUR DESIRED encounter_id RECORDS TO BE COPIED, HERE !!! */
  SET @ENCOUNTER_ID = 1234

IF EXISTS (SELECT TOP 1 1 FROM TABLE_1 WHERE encounter_id = @ENCOUNTER_ID)
BEGIN
    SELECT @COLUMNS = COALESCE(@COLUMNS+', ', 'SELECT ')+name
    FROM sys.columns WHERE OBJECT_NAME(object_id) = 'TABLE_1' AND name <> 'encounter_id'

    SET @COLUMNS = 'INSERT INTO TABLE_1 OUTPUT inserted.encounter_id INTO ##NEW_ID '+@COLUMNS+' FROM TABLE_1 WHERE encounter_id = '+CAST(@ENCOUNTER_ID AS VARCHAR(25))

    EXEC(@COLUMNS)
    PRINT(@COLUMNS)

    SELECT TOP 1 @NEWID = ID, @COLUMNS = NULL FROM ##NEW_ID

    SELECT @COLUMNS = COALESCE(@COLUMNS+', ', '')+name
    FROM sys.columns WHERE OBJECT_NAME(object_id) = 'TABLE_2'
    SET @COLUMNS = 'INSERT INTO TABLE_2 ('+@COLUMNS+') SELECT '+REPLACE(@COLUMNS,'encounter_id',''+CAST(@NEWID AS VARCHAR(25))+'')
        +' FROM TABLE_2 WHERE encounter_id = '+CAST(@ENCOUNTER_ID AS VARCHAR(25))

    EXEC(@COLUMNS)
    PRINT(@COLUMNS)

    SET @COLUMNS = NULL

    SELECT @COLUMNS = COALESCE(@COLUMNS+', ', '')+name
    FROM sys.columns WHERE OBJECT_NAME(object_id) = 'TABLE_3'
    SET @COLUMNS = 'INSERT INTO TABLE_3 ('+@COLUMNS+') SELECT '+REPLACE(@COLUMNS,'encounter_id',''+CAST(@NEWID AS VARCHAR(25))+'')
        +' FROM TABLE_3 WHERE encounter_id = '+CAST(@ENCOUNTER_ID AS VARCHAR(25))

    EXEC(@COLUMNS)
    PRINT(@COLUMNS)

    IF OBJECT_ID('tempdb..##NEW_ID') IS NOT NULL
        DROP TABLE ##NEW_ID
END

Answer 2

I always like creating sample tables in [tempdb] so that the syntax is correct. I created tables [t1], [t2], and [t3]. There are primary and foreign keys.

If you have a well developed schema, ERD (entity relationship diagram) http://en.wikipedia.org/wiki/Entity-relationship_diagram , these relationships should be in place.

-- Playing around
use tempdb
go


--
-- Table 1
--

-- Remove if it exists
if object_id('t1') > 0
drop table t1
go

-- Create the first table
create table t1
(
encounter_id int,
patient_id int, 
the_date date,
the_time time,
constraint pk_t1 primary key (encounter_id)
);
go

-- Add one row
insert into t1 values (1234, 112233, '2014-01-02', '14:25:01:00');
go

-- Show the data
select * from t1
go


--
-- Table 2
--

-- Remove if it exists
if object_id('t2') > 0
drop table t2
go

-- Create the second table
create table t2
(
encounter_id int,
the_page int,
recorded_on date,
recorded_by int,
constraint pk_t2 primary key (encounter_id, the_page)
);
go

-- Add two rows
insert into t2 values 
(1234, 1, '2014-01-02', 134),
(1234, 2, '2014-01-02', 134);    
go  

-- Show the data
select * from t2
go

--
-- Table 3
--

-- Remove if it exists
if object_id('t3') > 0
drop table t3
go

-- Create the third table
create table t3
(
encounter_id int,
the_page int,
key_name1 varchar(16),
key_value1 varchar(16),
constraint pk_t3 primary key (encounter_id, the_page, key_name1)
);
go

-- Add seven rows
insert into t3 values 
(1234, 1, 'key1', 'aaa'),
(1234, 1, 'key2', 'bbb'),
(1234, 1, 'key3', 'ccc'),
(1234, 1, 'key4', 'ddd'),
(1234, 2, 'key5', 'eee'),
(1234, 2, 'key6', 'fff'),
(1234, 2, 'key7', 'ggg');
go

-- Show the data
select * from t3
go

--
-- Foreign Keys
--

alter table t2 with check
add constraint fk_t2 foreign key (encounter_id)
references t1 (encounter_id);

alter table t3 with check
add constraint fk_t3 foreign key (encounter_id, the_page)
references t2 (encounter_id, the_page);

Here comes the fun part, a stored procedure to duplicate the data.

--
-- Procedure to duplicate one record
--

-- Remove if it exists
if object_id('usp_Duplicate_Data') > 0
drop procedure t1
go

-- Create the procedure
create procedure usp_Duplicate_Data @OldId int, @NewId int
as
begin

  -- Duplicate table 1's data
  insert into t1 
  select 
    @NewId,
    patient_id, 
    the_date,
    the_time
  from t1
  where encounter_id = @OldId;

  -- Duplicate table 2's data
  insert into t2 
  select 
    @NewId,
    the_page, 
    recorded_on,
    recorded_by
  from t2
  where encounter_id = @OldId;

  -- Duplicate table 3's data
  insert into t3 
  select 
    @NewId,
    the_page, 
    key_name1,
    key_value1
  from t3
  where encounter_id = @OldId;

end

Last but not least, we have to call the stored procedure to make sure it works.

-- Sample call
exec usp_Duplicate_Data 1234, 7777

In summary, I did not add any error checking or accounted for a range of Id's. I leave these tasks for you to learn.

Answer 3

Psuedo code, not tested:

DECLARE @patient_id INT, @date datetime, @time ??
SET @patient_id = 112244 --your patient id

INSERT INTO [**Table 1**] (patient_id, date, time, etc, etc, etc, etc)
VALUES (@patient_id, @date, @time, 'a', 'b', 'c', 'd')

DECLARE @encounter_id int
SET @encounter_id = SCOPE_IDENTITY  -- or select @encounter_id = encounter_id from [**Table 1**] where patientId = @patient_id

INSERT INTO [**Table 2**] (encounter_id, page, recorded_on, recorded_by, etc, etc2)
SELECT @encounter_id, page, recorded_on, recorded_by, etc, etc2 
FROM [**Table 2**]
WHERE encounter_id = 1234

INSERT INTO [**Table 3**] (encounter_id, page, keyname, keyvalue)
SELECT @encounter_id, page, keyname, keyvalue
FROM [**Table 3**]
WHERE encounter_id = 1234

Answer 4

declare @oldEncounterID int
set @oldEncounterID = 1234

declare @newEncounterID int
set @newEncounterID = 2345

insert into table1(encounter_id, patient_id, date, time, etc)
select newEncounterID, patient_id, date, time, etc
from table1 where encounter_id = oldEncounterID

and so on... problem with this approach you must know in advantage what all the columns are, and if they change you may change the columns accordingly

Another approach:

declare @oldEncounterID int
set @oldEncounterID = 1234

declare @newEncounterID int
set @newEncounterID = 2345
select * into #table1 from table1 where encounter_id = oldEncounterID
update #table1 set encounter_id = newEncounterID
insert into table1 select * from #table1

and so on... this second approach however may need a little adjustment if there is an identity column then you'll have to set identity_insert to on