October 25, 2018 at 7:53 am
So I have the source table TdatasegTest
I have the target table TdataSeg
If I needed a simple insert I would use this code
INSERT INTO Tdataseg
SELECT *
FROM TdatasegTest
WHERE YEAR(Periodkey) = '2018' and Partitionkey = '7' and Catkey = '1';
The issue is I need each row of the INSERT to be changed for these two fields
Partitionkey changed to 123 and CatKey changed to 4
all other fields are a direct insert
Can I do this using this same basic INSERT function?
the fields in both tables are as follows:
,[DATAKEY]
,
[PARTITIONKEY] (is 7 in source table needs to be 123 in target table)
,[CATKEY] (is 1 in source table needs to be 4 in target table)
,[PERIODKEY]
,[DATAVIEW]
,[CURKEY]
,[CALCACCTTYPE]
,[CHANGESIGN]
,[JOURNALID]
,[AMOUNT]
,[AMOUNTX]
,[ACCOUNT]
,[ACCOUNTX]
,[ACCOUNTR]
,[ACCOUNTF]
,[ENTITY]
,[ENTITYX]
,[ENTITYR]
,[ENTITYF]
October 25, 2018 at 8:08 am
You're not "changing the value", you're using completely different expressions for those two columns, which means that you can't use "SELECT *". I think you're overlooking the obvious solution, because you're thinking about it as "changing the value."
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 25, 2018 at 8:25 am
ok not sure I understand
Maybe more information will help
the source table has about 2 millions rows of data with many different partitionkey and catkey combinations
I only want to insert into the target table a very small subset of the source table, about 500,000 rows
So I only want Partitionkey 7 with catkey 1 out of the source table.i.e. about 500,000 rows
but when I INSERT the 500,000 rows into the target table I need to change the Partitionkey to 123 and the catkey to 4
does that make more sense?
October 25, 2018 at 8:28 am
randyetheridge - Thursday, October 25, 2018 8:25 AMok not sure I understand
Maybe more information will help
the source table has about 2 millions rows of data with many different partitionkey and catkey combinations
I only want to insert into the target table a very small subset of the source table, about 500,000 rowsSo I only want Partitionkey 7 with catkey 1 out of the source table.i.e. about 500,000 rows
but when I INSERT the 500,000 rows into the target table I need to change the Partitionkey to 123 and the catkey to 4
does that make more sense?
Your initial post made sense, the problem is you're trying to use SELECT *. Don't use SELECT * and declare your columns and expressions instead and the problem solves itself.
If I want to take data from TableB and insert it into TableA, but change the value of a column 2, I won't use SELECT * and use some logic that automagically changes the value, i declare my columns:
INSERT INTO TABLEA
SELECT ColumnA,
'Some Other Value' AS ColumnB,
ColumnC,
ColumnD
FROM TableB;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 25, 2018 at 8:29 am
INSERT INTO Tdataseg
SELECT *
FROM TdatasegTest
WHERE YEAR(Periodkey) = '2018' and Partitionkey = '7' and Catkey = '1';
The issue is I need each row of the INSERT to be changed for these two fields
Partitionkey changed to 123 and CatKey changed to 4
all other fields are a direct insert
Can I do this using this same basic INSERT function?
the fields in both tables are as follows:
,[DATAKEY]
,[PARTITIONKEY] (is 7 in source table needs to be 123 in target table)
,[CATKEY] (is 1 in source table needs to be 4 in target table)
,[PERIODKEY]
Sure. Just can't use Select *
INSERT TdataSeg
DATAKEY, PartitionKey, CatKey, PeriodKey, etc. . . .
SELECT DataKey, 123,4,PeriodKey, etc. . . .
FROM TdatdaSegTest
WHERE Year(PeriodKey) = 2018 -- Year returns an int
and PartitionKey = '7'
and CatKey = '1'
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 25, 2018 at 8:44 am
ok got it now. So I cannot use wild card Select. must declare each column. thanks
October 25, 2018 at 9:05 am
randyetheridge - Thursday, October 25, 2018 8:44 AMok got it now. So I cannot use wild card Select. must declare each column. thanks
Yes, you need to explicitly list the column names in the select replacing the column names with the new values where needed. Also, you should not use * in your queries, even where you want to return all columns.
October 25, 2018 at 10:05 am
ok so this is the correct code?
INSERT INTO Tdataseg
Select [DATAKEY]
,'123'
,'4'
,[PERIODKEY]
,[DATAVIEW]
,[CURKEY]
,[CALCACCTTYPE]
,[CHANGESIGN]
,[JOURNALID]
,[AMOUNT]
,[AMOUNTX]
,[DESC1]
,[DESC2]
,[ACCOUNT]
,[ACCOUNTX]
,[ACCOUNTR]
,[ACCOUNTF]
,[ENTITY]
,[ENTITYX]
,[ENTITYR]
,[ENTITYF]
,[ICP]
,[ICPX]
,[ICPR]
,[ICPF]
,[UD1]
,[UD1X]
,[UD1R]
,[UD1F]
,[UD2]
,[UD2X]
,[UD2R]
,[UD2F]
,[UD3]
,[UD3X]
,[UD3R]
,[UD3F]
,[UD4]
,[UD4X]
,[UD4R]
,[UD4F]
,[UD5]
,[UD5X]
,[UD5R]
,[UD5F]
,[UD6]
,[UD6X]
,[UD6R]
,[UD6F]
,[UD7]
,[UD7X]
,[UD7R]
,[UD7F]
,[UD8]
,[UD8X]
,[UD8R]
,[UD8F]
,[ARCHIVEID]
,[HASMEMOITEM]
,[STATICDATAKEY]
,[UD9]
,[UD9X]
,[UD9R]
,[UD9F]
,[UD10]
,[UD10X]
,[UD10R]
,[UD10F]
,[UD11]
,[UD11X]
,[UD11R]
,[UD11F]
,[UD12]
,[UD12X]
,[UD12R]
,[UD12F]
,[UD13]
,[UD13X]
,[UD13R]
,[UD13F]
,[UD14]
,[UD14X]
,[UD14R]
,[UD14F]
,[UD15]
,[UD15X]
,[UD15R]
,[UD15F]
,[UD16]
,[UD16X]
,[UD16R]
,[UD16F]
,[UD17]
,[UD17X]
,[UD17R]
,[UD17F]
,[UD18]
,[UD18X]
,[UD18R]
,[UD18F]
,[UD19]
,[UD19X]
,[UD19R]
,[UD19F]
,[UD20]
,[UD20X]
,[UD20R]
,[UD20F]
,[ATTR1]
,[ATTR2]
,[ATTR3]
,[ATTR4]
,[ATTR5]
,[ATTR6]
,[ATTR7]
,[ATTR8]
,[ATTR9]
,[ATTR10]
,[ATTR11]
,[ATTR12]
,[ATTR13]
,[ATTR14]
,[CODE_COMBINATION_ID]
,[AMOUNT_YTD]
,[AMOUNT_PTD]
,[LOADID]
,[RULE_ID]
,[STAT_BALANCE_FLAG]
,[VALID_FLAG]
,[DATA]
,[DATAX]
,[DATAR]
,[DATAF]
FROM [HYP_FDMEE].[dbo].[TDATASEGTEST}
WHERE YEAR(Periodkey) = '2018' and Partitionkey = '7' and Catkey = '1';
October 25, 2018 at 10:24 am
insert into [Tdataseg](
[DATAKEY]
, [Partitionkey]
, [Catkey]
, [PERIODKEY]
, [DATAVIEW]
, [CURKEY]
, [CALCACCTTYPE]
, [CHANGESIGN]
, [JOURNALID]
, [AMOUNT]
, [AMOUNTX]
, [DESC1]
, [DESC2]
, [ACCOUNT]
, [ACCOUNTX]
, [ACCOUNTR]
, [ACCOUNTF]
, [ENTITY]
, [ENTITYX]
, [ENTITYR]
, [ENTITYF]
, [ICP]
, [ICPX]
, [ICPR]
, [ICPF]
, [UD1]
, [UD1X]
, [UD1R]
, [UD1F]
, [UD2]
, [UD2X]
, [UD2R]
, [UD2F]
, [UD3]
, [UD3X]
, [UD3R]
, [UD3F]
, [UD4]
, [UD4X]
, [UD4R]
, [UD4F]
, [UD5]
, [UD5X]
, [UD5R]
, [UD5F]
, [UD6]
, [UD6X]
, [UD6R]
, [UD6F]
, [UD7]
, [UD7X]
, [UD7R]
, [UD7F]
, [UD8]
, [UD8X]
, [UD8R]
, [UD8F]
, [ARCHIVEID]
, [HASMEMOITEM]
, [STATICDATAKEY]
, [UD9]
, [UD9X]
, [UD9R]
, [UD9F]
, [UD10]
, [UD10X]
, [UD10R]
, [UD10F]
, [UD11]
, [UD11X]
, [UD11R]
, [UD11F]
, [UD12]
, [UD12X]
, [UD12R]
, [UD12F]
, [UD13]
, [UD13X]
, [UD13R]
, [UD13F]
, [UD14]
, [UD14X]
, [UD14R]
, [UD14F]
, [UD15]
, [UD15X]
, [UD15R]
, [UD15F]
, [UD16]
, [UD16X]
, [UD16R]
, [UD16F]
, [UD17]
, [UD17X]
, [UD17R]
, [UD17F]
, [UD18]
, [UD18X]
, [UD18R]
, [UD18F]
, [UD19]
, [UD19X]
, [UD19R]
, [UD19F]
, [UD20]
, [UD20X]
, [UD20R]
, [UD20F]
, [ATTR1]
, [ATTR2]
, [ATTR3]
, [ATTR4]
, [ATTR5]
, [ATTR6]
, [ATTR7]
, [ATTR8]
, [ATTR9]
, [ATTR10]
, [ATTR11]
, [ATTR12]
, [ATTR13]
, [ATTR14]
, [CODE_COMBINATION_ID]
, [AMOUNT_YTD]
, [AMOUNT_PTD]
, [LOADID]
, [RULE_ID]
, [STAT_BALANCE_FLAG]
, [VALID_FLAG]
, [DATA]
, [DATAX]
, [DATAR]
, [DATAF]
)
select
[DATAKEY]
, '123'
, '4'
, [PERIODKEY]
, [DATAVIEW]
, [CURKEY]
, [CALCACCTTYPE]
, [CHANGESIGN]
, [JOURNALID]
, [AMOUNT]
, [AMOUNTX]
, [DESC1]
, [DESC2]
, [ACCOUNT]
, [ACCOUNTX]
, [ACCOUNTR]
, [ACCOUNTF]
, [ENTITY]
, [ENTITYX]
, [ENTITYR]
, [ENTITYF]
, [ICP]
, [ICPX]
, [ICPR]
, [ICPF]
, [UD1]
, [UD1X]
, [UD1R]
, [UD1F]
, [UD2]
, [UD2X]
, [UD2R]
, [UD2F]
, [UD3]
, [UD3X]
, [UD3R]
, [UD3F]
, [UD4]
, [UD4X]
, [UD4R]
, [UD4F]
, [UD5]
, [UD5X]
, [UD5R]
, [UD5F]
, [UD6]
, [UD6X]
, [UD6R]
, [UD6F]
, [UD7]
, [UD7X]
, [UD7R]
, [UD7F]
, [UD8]
, [UD8X]
, [UD8R]
, [UD8F]
, [ARCHIVEID]
, [HASMEMOITEM]
, [STATICDATAKEY]
, [UD9]
, [UD9X]
, [UD9R]
, [UD9F]
, [UD10]
, [UD10X]
, [UD10R]
, [UD10F]
, [UD11]
, [UD11X]
, [UD11R]
, [UD11F]
, [UD12]
, [UD12X]
, [UD12R]
, [UD12F]
, [UD13]
, [UD13X]
, [UD13R]
, [UD13F]
, [UD14]
, [UD14X]
, [UD14R]
, [UD14F]
, [UD15]
, [UD15X]
, [UD15R]
, [UD15F]
, [UD16]
, [UD16X]
, [UD16R]
, [UD16F]
, [UD17]
, [UD17X]
, [UD17R]
, [UD17F]
, [UD18]
, [UD18X]
, [UD18R]
, [UD18F]
, [UD19]
, [UD19X]
, [UD19R]
, [UD19F]
, [UD20]
, [UD20X]
, [UD20R]
, [UD20F]
, [ATTR1]
, [ATTR2]
, [ATTR3]
, [ATTR4]
, [ATTR5]
, [ATTR6]
, [ATTR7]
, [ATTR8]
, [ATTR9]
, [ATTR10]
, [ATTR11]
, [ATTR12]
, [ATTR13]
, [ATTR14]
, [CODE_COMBINATION_ID]
, [AMOUNT_YTD]
, [AMOUNT_PTD]
, [LOADID]
, [RULE_ID]
, [STAT_BALANCE_FLAG]
, [VALID_FLAG]
, [DATA]
, [DATAX]
, [DATAR]
, [DATAF]
from
[HYP_FDMEE].[dbo].[TDATASEGTEST]
where
year([Periodkey]) = '2018'
and [Partitionkey] = '7'
and [Catkey] = '1';October 25, 2018 at 10:37 am
thank you so much for taking the time to clean up my code. I appreciate your time. this is a big help to me.
October 25, 2018 at 10:12 pm
I have to wonder why you are changing the column values when copying the data to a new table. It definitely will not help with auditing if you need to trace back the source. Would a lookup table be of help? What I mean is, what if next time your partition key is 8, how would I know what value that maps to?
----------------------------------------------------
October 29, 2018 at 6:45 am
If you don't already have it buy Red Gate's SQL Prompt. It will automatically insert fields for you when you type the first part of a SQL statement. VERY convenient! It can also auto-format text if you use the ^K^Y command.
Definitely worth the money.
October 29, 2018 at 7:24 am
great question about why I changed the value. and the future effect on audit. we are in fact a sox compliant company, so great question
first this was on my test server.
second the source data was a copy of a file from production. I was testing a YTD report. I needed Jan - Jul 2018 data I no longer had the Jan - Jul data in an easy to use format to load into test. However in production the data was in partitionkey 7. In test the data is in partitionkey 123. So I had August - Oct data in test but all in partitionkey 123. I needed the Jan - Jul data in test in partitionkey 123 so I could test the YTD report for end user sign off.
when we load the data in production it will load using partitionkey 7 so we will be fine in production.
October 29, 2018 at 7:25 am
I will look into SQL prompt this week, thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply