Query on Insert (RESOLVED) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query on Insert (RESOLVED)

Author

Topic

hutty
Starting Member

37 Posts

Posted - 2006-08-16 : 17:24:59

I'm running an aspnet program using a detailview. I'm updating a table that has 8 columns. However, I'm only passing 7 values back to the table using the "Insert" command. I would like to populate the 8th value based on one of the 7 values being sent back to the database. The calculation will consist of querying another table to find a matching value.

The query will be somewhat complicated though. The query value will consist of a nine digit number. And based on digits 1,2 and 7 I will like to return a value from another table that matches those digits.

Any ideas? Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 17:27:47

Please provide an example as it's hard to figure out what you need. Show us some sample data of both tables and what you want to insert into the 8th column.

Tara Kizer

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-16 : 18:13:41

Sounds like you might want a trigger on the table to update the column on insert.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hutty
Starting Member

37 Posts

Posted - 2006-08-16 : 18:39:36

Table 1 Table 2
Center Center
Alias Description (10xxxx4xx, 18xxxxxxx, etc)
Agency Comment
User
Date
Type
Budget
Attribute

I'm updating the database using Table 1 with the exception of the "Type". I want "Type" to be calculated based on what's submitted in Center, which is a nine-digit number. Say 100520410 is the Center. Looking at the first two digits, and digit 7 I will like to return 10xxxx4xx from table 2 "Description". Same hold true if "Center" was 400613101. Query would lookup the first two digits, and digit 7 and return value from table 2.

I hope that helps. Thanks.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-16 : 21:44:07

I'm assuming that you're inserting one row at a time. So perhaps something like this will work?

INSERT INTO Table1 (Center, Alias, Agency, User, Date, Type, Budget, Attribute)
SELECT @Center, @Alias, @Agency, @User, @Date, Table2.Description, @Budget, @Attribute
FROM Table2
WHERE Description/10000000 = @Center/10000000
AND (Description/100) - (Description/1000)*10 = (@Center/100) - (@Center/1000)*10

(I could've used substrings, but I thought it would be more fun to use mathematical operations)

hutty
Starting Member

37 Posts

Posted - 2006-08-17 : 15:43:20

Ok. I wasn't too clear above. Table 2 has 3 columms, Funds, Description and Comments. In the Funds column I have about 50 items. All having a generic value similar to 10xxx4xxx, 17xxxxxxx,23xxxxxxx, etc. The "x'are part of value. So when INSERT command is executed on TABLE 1 a 9 digit value for CENTER will be posted. From that 9 digit number all I need are digits 1,2 & 7 to query TABLE 2 "FUNDS" column and return the corresponding Descripton column.

I would like to do it all in a detailview before the INSERT command, but don't think that's possible.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 15:51:19

I'm lost.

Tara Kizer

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-17 : 16:06:21

quote:
Originally posted by hutty

....From that 9 digit number all I need are digits 1,2 & 7 ......

Check the following:

Declare @i int
Set @i = 103456489

Select SUBSTRING (Convert (varchar(9),@i) , 1 , 1 ),  SUBSTRING (Convert (varchar(9),@i) , 2 , 1 ), SUBSTRING (Convert (varchar(9),@i) , 7 , 1 ) 

Set @i = 173456789
Select SUBSTRING (Convert (varchar(9),@i) , 1 , 1 ),  SUBSTRING (Convert (varchar(9),@i) , 2 , 1 ), SUBSTRING (Convert (varchar(9),@i) , 7 , 1 ) 

Set @i = 233456789
Select SUBSTRING (Convert (varchar(9),@i) , 1 , 1 ),  SUBSTRING (Convert (varchar(9),@i) , 2 , 1 ), SUBSTRING (Convert (varchar(9),@i) , 7 , 1 )

Srinika

hutty
Starting Member

37 Posts

Posted - 2006-08-17 : 16:56:46

Sorry for the confusion. There are many fund types. A fund type is determined by the first two digits of a nine digit number. In some cases the combination of the first two digits and digit 7 will determine fund type. A user will input a nine digit number. Base on what user input I would like to retrieve the corresponding fund type based on the input. So, if a user inputs 230051300 into Table 1 I know it's a 23 Fund type and will like to retrieve value from table 2 for 23 Fund type. In table two, I have the first two digits follow by "x" to make a nine digit value, 23xxxxxxx. I hope that help clear things up.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 16:59:22

How about posting the table structure, some sample rows, and the expected results after the query runs? We're just going to go back and forth with this until we have enough information.

Tara Kizer

hutty
Starting Member

37 Posts

Posted - 2006-08-17 : 17:43:28

Table 1 Table 2

Center Alias Attribute Date Request Fund Fund Description Comment

400025200 Govt BSSR 8/16/2006 Hutty 10xxx4xx 400FUND AAAA
100513400 NonGovt CR 8/15/2006 hutty 10xxxxxx 10FUND BBBB
100620000 Agency RD 8/16/2006 Hutty 23xxxxxx 23FUND CCCC

When the INSERT command is executed it will populate Table 1 with the data above. All except for the Fund column.
FUND in Table 1 can be determined by Center. The query will look at Center's first two digits(40) and 7th digit (2) on the
line 5. Then take those attiributes and retrieve column "Description" from Table 2. In this case, 400FUND is return and
is populated in column F. Cell a,6 will query will return 10FUND from Description column. This is because the 7th digit
is not a "4". Cell a,7 query will result in 23FUND being populated in column F. And so forth.

I originally started out with simple detailview on a webform. Works fine with the user inputting the FUND column.
However, there's room for error going that route. So, I would like to populate database automatically based on
the nine digits the user inputs. I tried to upload file, but couldn't figure it out. Table 2 consist of columns FUND, Description and Comment.The only column that is blank is column F, Table 1 Fund. I Did this in excel.I hope that helps. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 17:47:45

Could you put code tags around the data for formatting plus put the tables on different lines?

Tara Kizer

hutty
Starting Member

37 Posts

Posted - 2006-08-17 : 17:55:08

[code] Table 1 Table 2

Center Alias Attribute Date Submitted Requested By Fund Fund Description Comment

400025200 Govt BSSR 8/16/2006 Hutty 10xxx4xx 400FUND AAAA
100513400 NonGovt CR 8/15/2006 hutty 10xxxxxx 10FUND BBBB
100620000 Agency RD 8/16/2006 Hutty 23xxxxxx 23FUND CCCC

When the INSERT command is executed it will populate Table 1 with the data above. All except for the Fund column.
FUND in Table 1 can be determined by Center. The query will look at Center's first two digits(40) and 7th digit (2) on the
line 5. Then take those attiributes and retrieve column "Description" from Table 2. In this case, 400FUND is return and
is populated in column F. Cell a,6 will query will return 10FUND from Description column. This is because the 7th digit
is not a "4". Cell a,7 query will result in 23FUND being populated in column F. And so forth.

I originally started out with simple detailview on a webform. Works fine with the user inputting the FUND column.
However, there's room for error going that route. So, I would like to populate database automatically based on
the nine digits the user inputs. I hope that helps. Thanks.
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 18:04:31

I'm not understanding. What do you mean by on the line 5? What is cell a,6? Where are these cells? How did you determine that 400FUND is returned when the first two digits are 40 in Table1 and 7th digit is 2. Why isn't it searching for 402? Where is column F?

Is someone else able to figure this out? This is just way too unclear for me.

Tara Kizer

hutty
Starting Member

37 Posts

Posted - 2006-08-17 : 18:11:59

I had originally did it excel where cell a,6 would equal 100513400. I'm looking to query on the very column on the left called Center. Taking certain digits from Center and finding a match in Table 2 column Fund. Retrieve Description from table column 2 based on match in table 2 column Fund and return that value back to Table 1 Fund.

Ok. If I'm not clear I'll go ahead and drop thread. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 18:16:50

Is this even a SQL Server question?

Tara Kizer

hutty
Starting Member

37 Posts

Posted - 2006-08-17 : 18:22:46

I thought it was. How can you query from table 2 column A based on a value in table 1 column A. Probably a complex query. Thanks for your help. I'll look for a workaround.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 18:29:30

You can do that via a join.

SELECT ... (columns go here)
FROM Table2 t2
INNER JOIN Table1 t1
ON t2.ColumnA = t1.ColumnA

Tara Kizer

hutty
Starting Member

37 Posts

Posted - 2006-08-17 : 18:32:52

But there's the complicated part. Column 1 has a value 100052300. I need to look in Table 2 for a corresponding value that begins 10. One exception would be to find a value that begins with 10 and the 7 digit to equal a 4.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 18:35:27

You can use substring then:

SELECT ... (columns go here)
FROM Table2 t2
INNER JOIN Table1 t1
ON SUBSTRING(t2.ColumnA, 1, 2) = t1.ColumnA

Tara Kizer

hutty
Starting Member

37 Posts

Posted - 2006-08-17 : 20:42:25

I couldn't return any values. A quick way to see it to create two tables. Table 1 will have two columns, A & B. Same for Table 2.
In Table 1, column A input the following values, 100520400, 400613100, 100519000. In Table 2, column A input 10xxxxxxx, 40xxxxxxx,10xxxx4xx. Column B of table 2 you can put anything.

The query return value will populate Table 1, column B. So, base on your query Table 1 will look like this:

Column A Column B
100620400 10xxxx4xx
100519000 10xxxxxxx
400613100 40xxxxxxx

So, I guess there would some automation to populate Table 1, column B everytime column A is populated.

Thanks again.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources