May 22, 2014 at 8:45 am
hi
i have two tables.
table 1 is
PK_GLSectionSectionNumber
1Assets1
2Liabilities2
3Revenue3
4Expense4
5Capital5
and my second table
PK_GLSectionGroupFinancial AccountDescriptionOpening BalanceDebitsCreditsClosing BalanceFund Legal EntityFundStructure CodePeriod End DateAccountName
3487TestTestTest Test1111NULLIL0101/01/2014Test-Test-Test-Test
what i want to do is take the text in the section colmn look up table 1 if its not there add it to table with new section number
anyone any ideas whats the best way to do this
May 22, 2014 at 8:57 am
I believe this would do the trick:
INSERT INTO table1
( SECTION )
SELECT SECTION
FROM table2
WHERE NOT EXISTS ( SELECT *
FROM table1
WHERE table1.SECTION = table2.SECTION )
If you need this upon every insert into table2, you can use a trigger on inserts.
May 22, 2014 at 9:01 am
thanks for that. will try that.
just 1 thing would that still work if say i had 10 different pieces of data in the second table that i wanted to update into table 1
May 22, 2014 at 9:05 am
it come out like this
PK_GLSectionSectionNumber
6TestNULL
1Assets1
2Liabilities2
3Revenue3
4Expense4
5Capital5
is there a way to auto incremnet the scention number number
May 22, 2014 at 9:16 am
ronan.healy (5/22/2014)
thanks for that. will try that.just 1 thing would that still work if say i had 10 different pieces of data in the second table that i wanted to update into table 1
Best way to find out is to try it. You might also consider a MERGE statement, and compare performance.
John
Edit: Actually, if you're using SQL Server 2005, MERGE won't be available.
May 22, 2014 at 9:30 am
ronan.healy (5/22/2014)
it come out like this
PK_GLSectionSectionNumber
6TestNULL
1Assets1
2Liabilities2
3Revenue3
4Expense4
5Capital5
is there a way to auto incremnet the scention number number
If it should be autoincremented, can it be the identity field? What is the difference between the PK_GL and the SectionNumber?
May 22, 2014 at 9:35 am
i cant have 2 isdentity columns
there isnt alot of difference all right in thsi table. but will have to do something similar for another table and the id def wont match the numbers going forward
like another table numbers will start at 200 but the pk_id will start at 1
anyway around it do you know
May 22, 2014 at 9:38 am
Just make the column a computed column as PK + 199? I suspect that your requirement is a little more complicated than that, but it's difficult if you keep drip-feeding us information instead of telling us exactly what you need to do.
John
May 22, 2014 at 9:46 am
sorry i didnt think i was drip feeding i just thought if i got it working for 1 table i would just get it working for them all.
basically i am creating account codes. i have a list of accounts and account codes but going forward there might be new one that are in files that we wont know about so if that happens i want it to auto generate the number.
the number depends on certain word like the frist part section if it has none of the first 5 work in that table i add it with a number. same for another 2 tables. when all the tables are update i then get my number out if.
ill worry about the last part once i have my tables creating the numebrs
May 22, 2014 at 9:50 am
I'm sorry, but I still don't understand. Please will you post table DDL in the form of CREATE TABLE statements, sample data in the form of INSERT statements and expected results based on the sample data?
Thanks
John
July 15, 2014 at 8:17 pm
I have to agree it is helpful to post the DDL create statements for the tables and include sample data in a consumable format
the number depends on certain word
This is where I am lost. If you have a table you let the table autogenerate a number. I assume this is the primary key column. Are you trying to have a key defined by a alphanumeric string?
I think the insert statement mentioned gives you something good to work with. Though depending on how the data is stored you may have to use DISTINCT as in
INSERT INTO table1
( SECTION )
SELECT Distinct SECTION
FROM table2
WHERE NOT EXISTS ( SELECT *
FROM table1
WHERE table1.SECTION = table2.SECTION )
----------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply