May 9, 2018 at 11:39 am
I am working with 2 tables. 1st table (old table) contains data from 01/01/16 to 07/31/2017. The other table (new table) contains data from 08/01/2017 to 12/31/2017. I need to merge the data so we only have 1 table. Both tables have Emailactivitykey which is an Identity column and has a primary key on it. The min value of Emailactivitykey column in both tables is 1. What I would like to do is to find a way to insert records from new table to old table? What's the best way to accomplish it?
This is how I have tried it but no luckset identity_insert EmailActivity05082018 on
insert into EmailActivity05082018 ([leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
,[EmailActivityKey]
select [leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
,[EmailActivityKey]
FROM [EmailActivity]
2nd approach
set identity_insert EmailActivity05082018 on
insert into EmailActivity05082018 ([leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
--[EmailActivityKey] tried excluding the column so it can generate a value based on the last emailactivitykey
select [leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
,[EmailActivityKey]
FROM [EmailActivity]
3rd approach
set identity_insert EmailActivity05082018 on
insert into EmailActivity05082018 ([leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
select [leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
FROM [EmailActivity]
May 9, 2018 at 11:52 am
Do you need to retain the old ID? If not, then simply exclude it from your list of columns in your INSERT and the new table will give the email a new ID. If you do, what do you need to do with the duplicates (as both tables have the same IDs)?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 9, 2018 at 11:55 am
Are you trying to preserve the Emailactivitykey in both tables? If so that will not work if there is overlap between the two tables. Otherwise it looks like your third approach should work fine without the identity insert on.
May 9, 2018 at 11:57 am
Unless I create a temp table and then insert data. It is time consuming but should work so it should be something like thiscreate table EmailActivity05092018
([leadId] int
,[activityDate] date
,[DateAdded] date
,[CampaignID] int
,[EmailActivityKey] identity (max value from the old table, 1 ) primary key
And then run this set identity_insert EmailActivity05082018 on
insert into EmailActivity05082018 ([leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
,[EmailActivityKey]
select [leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
,[EmailActivityKey]
FROM [EmailActivity05092018 ]
May 9, 2018 at 1:41 pm
/*
Assumptions
# [EmailActivityKey] is an INT value (as it is an identity)
*/
----------------------------------------------------------
-- Option1 - Use a negative value
set identity_insert EmailActivity05082018 on;
insert into EmailActivity05082018 ([leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
,[EmailActivityKey]
select [leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
, -[EmailActivityKey] -- NOTE the "-" in front of the field name to make the number negative.
FROM [EmailActivity];
set identity_insert EmailActivity05082018 off;
----------------------------------------------------------
-- Option2 - Add an offset
declare @maxEmailActivityKey int;
select @maxEmailActivityKey = max([EmailActivityKey])
from EmailActivity05082018;
set identity_insert EmailActivity05082018 on;
insert into EmailActivity05082018 ([leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
,[EmailActivityKey]
select [leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
, [EmailActivityKey] + @maxEmailActivityKey
FROM [EmailActivity]
set identity_insert EmailActivity05082018 off;
May 9, 2018 at 2:14 pm
DesNorton - Wednesday, May 9, 2018 1:41 PM/*
Assumptions
# [EmailActivityKey] is an INT value (as it is an identity)
*/----------------------------------------------------------
-- Option1 - Use a negative value
set identity_insert EmailActivity05082018 on;insert into EmailActivity05082018 ([leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
,[EmailActivityKey]
select [leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
, -[EmailActivityKey] -- NOTE the "-" in front of the field name to make the number negative.
FROM [EmailActivity];set identity_insert EmailActivity05082018 off;
----------------------------------------------------------
-- Option2 - Add an offset
declare @maxEmailActivityKey int;select @maxEmailActivityKey = max([EmailActivityKey])
from EmailActivity05082018;set identity_insert EmailActivity05082018 on;
insert into EmailActivity05082018 ([leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
,[EmailActivityKey]
select [leadId]
,[activityDate]
,[DateAdded]
,[CampaignID]
, [EmailActivityKey] + @maxEmailActivityKey
FROM [EmailActivity]set identity_insert EmailActivity05082018 off;
I just saw your message but I ended up creating a 3rd table and moved the data to the 1st table and now all the data exists in 1 table.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply