adding new data to a table

Question

Post reply

adding new data to a table

ronan.healy

SSCrazy

Points: 2232
More actions
May 22, 2014 at 8:45 am

#293656

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

david.gugg SSCertifiable Points: 6100 More actions · Answer 1

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.

Be still, and know that I am God - Psalm 46:10

ronan.healy SSCrazy Points: 2232 More actions · Answer 2

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

ronan.healy SSCrazy Points: 2232 More actions · Answer 3

it come out like this

PK_GLSectionSectionNumber

6TestNULL

1Assets1

2Liabilities2

3Revenue3

4Expense4

5Capital5

is there a way to auto incremnet the scention number number

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

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.

david.gugg SSCertifiable Points: 6100 More actions · Answer 5

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?

Be still, and know that I am God - Psalm 46:10

ronan.healy SSCrazy Points: 2232 More actions · Answer 6

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

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

ronan.healy SSCrazy Points: 2232 More actions · Answer 8

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

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

MMartin1 One Orange Chip Points: 27799 More actions · Answer 10

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 )

----------------------------------------------------