how to control data types

i have sp that insert fileName and fileType

how can i forbidden in the sp other types then word doc and images?

Code Snippet

if not exists(select Number

from dbo.Freezrelease

where FileName1 like @.FileName1 and

FileType like @.FileType and

FileSize = @.FileSize)

/*{*/ begin

if @.FileSize<500

/*{*/ begin

insert into dbo.Freezrelease(FileName1,FileType,FileSize)

values(@.FileName1,@.FileType,@.FileSize)

select @.@.identity

/*}*/ end

else

select -1

/*}*/end

else

select 0

So you are just inserting the file names, not the bits? If so, just add a line:

if @.fileType not in ('doc','jpg','...etc')

begin

raiserror ('Filetype must be doc, jpg, or etc')

return -100

end

And the procedure will stop there after raising an error

|||

You could add a CHECK constraint to the table that limits the values in the FileName field to only values ending with '.doc', '.docx'*, or '.jpg'. (And of course, any other image file extensions required...)

Using this method, it would not be necessary to 'control' every stored procedure or application query. Programmers and users will not be able to thwart your determination that a 'proper' FileName be supplied. The CHECK constraint would 'police' the inserts/updates for you.

* .docx for Word 2007

Code Snippet

CREATE TABLE #MyTable
( RowID int IDENTITY,
FileName varchar(50)
CHECK ( FileName LIKE '%.doc%' OR FileName LIKE '%.jpg' )
)
GO

-- These succeed
INSERT INTO #MyTable VALUES ( 'MyFile.doc' )
INSERT INTO #MyTable VALUES ( 'MyPic.jpg' )
INSERT INTO #MyTable VALUES ( 'MyFile.docx' )

-- This fails
INSERT INTO #MyTable VALUES ( 'Myfile.txt' )

-- This fails
INSERT INTO #MyTable VALUES ( 'Myfile' )

SELECT *
FROM #MyTable

RowID FileName
-- --
1 MyFile.doc
2 MyPic.jpg
3 MyFile.docx

DROP TABLE #MyTable

|||

What Arnie says is true too, though I would prefer having a fileType column to hold the extension also, for all of the basic normalization reasons:

1. You can add information about types of files easily and join to it without the substring

2. Listing files by type will not require a substring

3. Searching for files by type can be indexed

Really it is all about the need to avoid dealing with parts of a column value.

how to continue on error

How can I cause my insert statement to skip over (without failing) rows
where there's a primary key constraint violation?

I've got a case where daily I insert >500k rows of daily data, where
the data is date and time stamped. So, for example, I have an insert
statement with constraint: WHERE date >= '5/20/05' AND date <
'5/21/05'. That takes care of one day's data (5/20).

However, the next day's data (5/21) will still have some time stamps
from the previous day. Therefore the statement needs to be something
like WHERE date >= '5/20/05' AND date <= '5/21/05'. The 5/20 data is
already loaded but I need to take the 5/21 data which just happens to
contain just a few rows of data marked 5/20 and insert it without
generating a primary key error from all the other 5/20 rows that are
already inserted.

-DaveINSERT INTO TargetTable (key_col, ...)
SELECT S.key_col, ...
FROM SourceTable AS S
LEFT JOIN TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL
AND S.date >= '20050520' AND date < '20050522'

--
David Portas
SQL Server MVP
--|||The easy way is to limit the insert query to 11:59 59 of the previous
day. Then you tell your users, "this report contains all the data from
yesterday" In fact, if you're doing a report of some kind, this is
really the best way to do it because otherwise, you have incomplete
(and therefore bad) data for the current day.

Another way is to delete yesterday's data right before you run the
insert.|||Should the join run very slowly? If I do the insert with a standard
insert query it takes about 7 minutes. With the join query it runs and
doesn't seem to be able to finish. If I run the query on dates with no
data it finishes ok. Is my join incorrect since I can't use S.keyrow?

insert into final(keyRow, cell, recordDate, high_set )

SELECT CONVERT(CHAR(16),dateadd(hh,datepart(hh, .access_time),
S.record_date),20)+'|'+CONVERT(CHAR(3), S.bts_id)+'-'+CONVERT(CHAR(1),
S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id) AS keyRow,
(CONVERT(CHAR(3), S.bts_id)+'-'+CONVERT(CHAR(1),
S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id)) AS cell,
CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20)as recordDate, SUM(S.high_set_int) AS high_set

from SourceTable AS S
LEFT JOIN TargetTable AS T
ON keyRow = T.keyRow

WHERE T.keyRow IS NULL
AND S.record_date >= '5/06/2005' AND S.record_date < '5/07/2005' AND
convert (char(8), S.access_time,108) != '00:00:00'

GROUP BY CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20),
CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20)+'|'+
CONVERT(CHAR(3), S.bts_id)+'-'+CONVERT(CHAR(1),
S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id)

ORDER BY CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20),
CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20)+'|'+CONVERT(CHAR(3), S.bts_id)+'-'+
CONVERT(CHAR(1), S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id),
S.cell|||
christopher.secord@.gmail.com wrote:
> The easy way is to limit the insert query to 11:59 59 of the previous
> day. Then you tell your users, "this report contains all the data from
> yesterday" In fact, if you're doing a report of some kind, this is
> really the best way to do it because otherwise, you have incomplete
> (and therefore bad) data for the current day.

Yes, I agree but the way the data is generated results in "today's"
data flat file containing some of yesterday's data. So although 99% of
yesterday's data is already in the db, the last little bit needs be
added for completeness. It's not that the nearly all users can't use
the 99% data for their purposes but still the missing 1% needs to be
added for later complete, accurate reports.

> Another way is to delete yesterday's data right before you run the
> insert.

This puts the problem back 1 day because I would still need to add
yesterday's data which is in its own flat file which contains data from
the day before yesterday.

-David|||David Portas wrote:
> INSERT INTO TargetTable (key_col, ...)
> SELECT S.key_col, ...
> FROM SourceTable AS S
> LEFT JOIN TargetTable AS T
> ON S.key_col = T.key_col
> WHERE T.key_col IS NULL
> AND S.date >= '20050520' AND date < '20050522'

I'm thinking maybe the best thing to do is add another column to my
table that uniquely identifies the data from a particular day. Some of
the data from the particular flat file will be from the day before but
it won't matter because I'll use the new field in the where criteria
instead of the actual record dates.

Also thought about using NOT EXISTS somehow.

-Dave|||Make sure you have indexes on the columns that are being joined.|||(wireless200@.yahoo.com) writes:
> Should the join run very slowly? If I do the insert with a standard
> insert query it takes about 7 minutes. With the join query it runs and
> doesn't seem to be able to finish. If I run the query on dates with no
> data it finishes ok. Is my join incorrect since I can't use S.keyrow?

I don't understand that last question. What do you mean, you cannot
use S.keyrow?

A clustered index on S.record_date would be a good thing.

I would also replace the LEFT JOIN with NOT EXISTS. Not because this
is faster, but because expresses what you mean.

Does the target table have an IDENTITY column? Else there is no reason at
all to have the ORDER BY clause. Removing that could also gain some
performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

how to connect with sqlexpress2005 & asp.net for update insert delete codings

I want to know about the how to connect with asp.net 2005 and sqlexpress through codings using parameters

import System.Data.SqlClient;

related update delete insert codings ...

How to know about the make the new connection with different databases sqlserver, oracle mysql...

Hi

Have you had a lookhttp://quickstarts.asp.net/QuickStartv20/default.aspx andhttp://www.asp.net/learn/data-access/.

Work through these samples.. They explain how to access databases using declarative coding (sqldatasource and the likes) and coe behind coding.

Hope that helps.

VJ

How to CONCATENATE >50 fields in Excel table into SQL Insert State

SQL Server 2005, Excel 2003
I would like to populate some tables in a SQL database from some tables in
Excel.
Previously I have used the Concatenate function in Excel to construct SQL
Insert statement such as example below.
=CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
VALUES ('",B2,"','",C2,"',",D2,",",E2,",",F2,")")
However, there is a limit in using the Concatenate function in Excel and
this dows not work on a larger table (50 fileds). Please could you advice
how I could create the SQL insert statements using this Excel table to
populate the associate SQL table? Is there any better/alternative ways?
Many thanks in advance,
Hi Will,
The bcp utility will import the file into a table for you. You can learn
about it here:
http://technet.microsoft.com/en-us/library/ms162802.aspx
-Susan
"will~" wrote:

> SQL Server 2005, Excel 2003
> I would like to populate some tables in a SQL database from some tables in
> Excel.
> Previously I have used the Concatenate function in Excel to construct SQL
> Insert statement such as example below.
> =CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
> VALUES ('",B2,"','",C2,"',",D2,",",E2,",",F2,")")
> However, there is a limit in using the Concatenate function in Excel and
> this dows not work on a larger table (50 fileds). Please could you advice
> how I could create the SQL insert statements using this Excel table to
> populate the associate SQL table? Is there any better/alternative ways?
> Many thanks in advance,
>
|||On Thu, 28 Feb 2008 07:43:00 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:

>Hi Will,
>The bcp utility will import the file into a table for you. You can learn
>about it here:
>http://technet.microsoft.com/en-us/library/ms162802.aspx
>-Susan
BCP.EXE is quite particular about the format of an input file. While
you might be able to EXPORT from Excel in a format that can somehow be
made to work with BCP.EXE, it is not a simple process.
I would start by using the data import wizard. This can create a DTS
package (SQL Server 2000) or an SSIS package (SQL Server 2005).
Roy Harvey
Beacon Falls, CT

How to CONCATENATE >50 fields in Excel table into SQL Insert State

SQL Server 2005, Excel 2003
I would like to populate some tables in a SQL database from some tables in
Excel.
Previously I have used the Concatenate function in Excel to construct SQL
Insert statement such as example below.
=CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
VALUES ('",B2,"','",C2,"',",D2,",",E2,",",F2,")")
However, there is a limit in using the Concatenate function in Excel and
this dows not work on a larger table (50 fileds). Please could you advice
how I could create the SQL insert statements using this Excel table to
populate the associate SQL table? Is there any better/alternative ways?
Many thanks in advance,Hi Will,
The bcp utility will import the file into a table for you. You can learn
about it here:
http://technet.microsoft.com/en-us/library/ms162802.aspx
-Susan
"will~" wrote:
> SQL Server 2005, Excel 2003
> I would like to populate some tables in a SQL database from some tables in
> Excel.
> Previously I have used the Concatenate function in Excel to construct SQL
> Insert statement such as example below.
> =CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
> VALUES ('",B2,"','",C2,"',",D2,",",E2,",",F2,")")
> However, there is a limit in using the Concatenate function in Excel and
> this dows not work on a larger table (50 fileds). Please could you advice
> how I could create the SQL insert statements using this Excel table to
> populate the associate SQL table? Is there any better/alternative ways?
> Many thanks in advance,
>|||On Thu, 28 Feb 2008 07:43:00 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>Hi Will,
>The bcp utility will import the file into a table for you. You can learn
>about it here:
>http://technet.microsoft.com/en-us/library/ms162802.aspx
>-Susan
BCP.EXE is quite particular about the format of an input file. While
you might be able to EXPORT from Excel in a format that can somehow be
made to work with BCP.EXE, it is not a simple process.
I would start by using the data import wizard. This can create a DTS
package (SQL Server 2000) or an SSIS package (SQL Server 2005).
Roy Harvey
Beacon Falls, CT

How to compare value pair of one table with value pair of another table

Here is the problem:
--------
create table A( a1 int, a2 int)
create table B( b1 int, b2 int)

insert A values(1,1)
insert A values(1,2)
insert A values(2,1)
insert A values(2,3)
insert A values(3,1)
insert A values(3,3)

insert B values(1,1)
insert B values(1,2)
insert B values(3,2)
insert B values(2,2)

What is the SQL query to find out the pairs existing in B, but not in A ?

I can solve this by using string functions, but my problem is I have to write a query that can execute on MSSQL, DB2 and ORACLE, and as you know they have differnt syntax for string functions.

Thanks in advance.

Regards,
s99shah.pairs in B but not in A? left outer join, check for unmatched rows --
select b1, b2
from B
left outer
join A
on b1 = a1
and b2 = a2
where a1 is null

rudy
http://r937.com/|||Hi,

TRY THIS

Select B.* from a,b
where (+) a1 = b1;

how to compare value of two fileds and based on that insert value into third fileds

Hi,
I have a database with table name as test in that i have 6 colums
they are
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
please suggest query in sql which i can run to do this .
Regards
On Jan 30, 3:52 pm, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si > wrote:[vbcol=seagreen]
> You can use a computed column. Check this script:
> CREATE TABLE dbo.t1
> (nametype1 char(2),
> nametype2 char(2),
> nameval AS
> CASE
> WHEN nametype1 = nametype2 THEN 2
> WHEN nametype1 = REVERSE(nametype2) THEN 1
> ELSE 0 -- error?
> END)
> GO
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> SELECT *
> FROM dbo.t1
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> news:1170146292.237194.199580@.q2g2000cwa.googlegro ups.com...
>
>
>
>
instead of creatating in new table can i update the value in
existing table please suggest.
|||UPDATE t1 SET nameval =
CASE WHEN nametype1 = nametype2 THEN 1
WHEN nametype1 = REVERSE(nametype2) THEN 2
END
Regards
Amish Shah
http://shahamishm.tripod.com
On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> On Jan 30, 3:52 pm, "Dejan Sarka"
>
>
> <dejan_please_reply_to_newsgroups.sa...@.avtenta.si > wrote:
>
>
>
>
>
>
>
> instead of creatating in new table can i update the value in
> existing table please suggest.- Hide quoted text -
> - Show quoted text -
|||On Jan 30, 5:33 pm, "amish" <shahami...@.gmail.com> wrote:[vbcol=seagreen]
> UPDATE t1 SET nameval =
> CASE WHEN nametype1 = nametype2 THEN 1
> WHEN nametype1 = REVERSE(nametype2) THEN 2
> END
> Regards
> Amish Shahhttp://shahamishm.tripod.com
> On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2
nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1
nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1
please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.
Regards

how to compare value of two fileds and based on that insert value into third fileds

Hi,

I have a database with table name as test in that i have 6 colums
they are

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1

please suggest query in sql which i can run to do this .

RegardsHi,

You do not specify what is the logic for generating the values for the
nameval column, but based on your example seems it is 2 when the values are
equal and 1 when they are different.

Based on that assumption, here is a query that will do the update:

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end)

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||On Jan 30, 6:47 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Hi,
>
You do not specify what is the logic for generating the values for the
nameval column, but based on your example seems it is 2 when the values are
equal and 1 when they are different.
>
Based on that assumption, here is a query that will do the update:
>
update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end)
>
Regards,
>
Plamen Ratchevhttp://www.SQLStudio.com

hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2

nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1

nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1

please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.

Regards|||I am still confused about the logic and the purpose of this, but since it
seems to follow the same pattern (when equal then 2 else 1), here it is (you
just keep repeating the same for the other "nameval" columns):

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 = (case when nametype1 = nametype4 then 2 else 1 end)

Also, you can create those "nameval" columns as computed columns and then
you do not have to run the update statements. Something like this:

create table test(
name varchar (20),
address varchar (20),
position varchar (20),
nametype1 varchar (20),
nametype2 varchar (20),
nametype3 varchar(20),
nametype4 varchar(20),
nameval as (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 as (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 as (case when nametype1 = nametype4 then 2 else 1 end))

insert into test (nametype1, nametype2, nametype3, nametype4) values ('AA',
'AA', 'AB', 'BA')

select * from test

drop table test

Perhaps the table should be normalized too, but since no requirements are
given I do not want to guess...

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jan 31, 11:42 am, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

I am still confused about the logic and the purpose of this, but since it
seems to follow the same pattern (when equal then 2 else 1), here it is (you
just keep repeating the same for the other "nameval" columns):
>
update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 = (case when nametype1 = nametype4 then 2 else 1 end)
>
Also, you can create those "nameval" columns as computed columns and then
you do not have to run the update statements. Something like this:
>
create table test(
name varchar (20),
address varchar (20),
position varchar (20),
nametype1 varchar (20),
nametype2 varchar (20),
nametype3 varchar(20),
nametype4 varchar(20),
nameval as (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 as (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 as (case when nametype1 = nametype4 then 2 else 1 end))
>
insert into test (nametype1, nametype2, nametype3, nametype4) values ('AA',
'AA', 'AB', 'BA')
>
select * from test
>
drop table test
>
Perhaps the table should be normalized too, but since no requirements are
given I do not want to guess...
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

is it possible to run the query in existing table , please suggest how
and also if there more than 20 nametypes is there query which can loop
through all the name types and do the job.

please suggest any reference too if any.

Regards|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Even your narrative is wrong. Did you know that double quotes are not
used for string values in SQL? Have you heard of ISO-11179 rules for
data element names? Absurd things like "name_type_1" look like a
repeating group in violationof 1NF which will lead to some really
horrible kludges and a loss of data integrity.

Please try again and pretend that you have to work from these specs
without any prior knowledge -- we do SQL, not mind-reading :)|||Yes, the query will update an existing table, just keep adding lines for
each column, like this:

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 = (case when nametype1 = nametype4 then 2 else 1 end),
nameval3 = (case when nametype1 = nametype5 then 2 else 1 end),
...

As for you reference to do the update in a loop, then this is doable via
dynamic SQL. Here is a quick sketch of how it may look (by adjusting the
number 10 you will get different number of columns, beware of the 4000
character limit on the SQL string):

DECLARE @.sql nvarchar(4000),
@.count int,
@.numcolumns int

SELECT @.sql = 'update test set ', @.count = 1, @.numcolumns = 10

WHILE @.count <= @.numcolumns
BEGIN
IF @.count = 1
SELECT @.sql = @.sql + 'nameval = (case when nametype1 = nametype' +
CAST(@.count + 1 as nvarchar) + ' then 2 else 1 end)'
ELSE
SELECT @.sql = @.sql + ', nameval' + CAST(@.count - 1 as nvarchar) + ' =
(case when nametype1 = nametype' + CAST(@.count + 1 as nvarchar) + ' then 2
else 1 end)'

SELECT @.count = @.count + 1
END

EXEC(@.sql)

Erland Sommarskog has an excellent guide on dynamic SQL at
http://www.sommarskog.se/dynamic_sql.html. I would recommend reading it
before jumping into using dynamic SQL. Also, please read the comment from
Celko, he is correct that without posting DLL and specifications it is very
difficult to get good answers.

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||Tradeorganizer wrote:

Quote:

Originally Posted by

I have a database with table name as test in that i have 6 colums
they are
>
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
>
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
>
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
>
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
>
please suggest query in sql which i can run to do this .

A couple of things that others have touched on, but not made
quite this explicit:

/Why/ should nametype1 = "AB" and nametype2 = "BA" lead to
nameval = 1? What is the general rule that you want to apply?

Why do you have more than 20 nametypes? Please give serious
consideration to splitting this table into two tables, e.g.

[table1]
person_id, name, address, position
1, 'John Doe', '123 Cherry Lane', 'Regional Manager'
2, 'Thomas Atkins', '987 Easy Street', 'President'

[table2]
person_id, nameindex, nametype, nameval
1, 1, 'AB', 1
1, 2, 'BA', 1
2, 1, 'BB', 2
2, 2, 'BB', 2|||Thanks for great help , yes its working for me.

Thanks to all for taking time to explain me.

Regards

Ed Murphy wrote:

Quote:

Originally Posted by

Tradeorganizer wrote:
>

Quote:

Originally Posted by

I have a database with table name as test in that i have 6 colums
they are

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1

please suggest query in sql which i can run to do this .

>
A couple of things that others have touched on, but not made
quite this explicit:
>
/Why/ should nametype1 = "AB" and nametype2 = "BA" lead to
nameval = 1? What is the general rule that you want to apply?
>
Why do you have more than 20 nametypes? Please give serious
consideration to splitting this table into two tables, e.g.
>
[table1]
person_id, name, address, position
1, 'John Doe', '123 Cherry Lane', 'Regional Manager'
2, 'Thomas Atkins', '987 Easy Street', 'President'
>
[table2]
person_id, nameindex, nametype, nameval
1, 1, 'AB', 1
1, 2, 'BA', 1
2, 1, 'BB', 2
2, 2, 'BB', 2

how to compare value of two fileds and based on that insert value into third fileds

Hi,
I have a database with table name as test in that i have 6 colums
they are
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
please suggest query in sql which i can run to do this .
RegardsYou can use a computed column. Check this script:
CREATE TABLE dbo.t1
(nametype1 char(2),
nametype2 char(2),
nameval AS
CASE
WHEN nametype1 = nametype2 THEN 2
WHEN nametype1 = REVERSE(nametype2) THEN 1
ELSE 0 -- error?
END)
GO
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
SELECT *
FROM dbo.t1
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Tradeorganizer" <tradeorganizer@.gmail.com> wrote in message
news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> Hi,
> I have a database with table name as test in that i have 6 colums
> they are
> name varchar (20)
> address varchar (20)
> position varchar (20)
> nametype1 varchar (20)
> nametype2 varchar (20)
> nameval varchar(20)
> now in the nametype1 and nametype2 there are values like
> nametype1 nametype2
> "AB" "BA"
> "BB" "BB"
> "AA" "AA"
> "BA" "AB"
> now depending upon the combination i want to assign value to the thrid
> field that is nameval like example below
> nametype1 nametype2 nameval
> "AB" "BA" 1
> "BB" "BB" 2
> "AA" "AA" 2
> "BA" "AB" 1
> please suggest query in sql which i can run to do this .
> Regards
>|||On Jan 30, 3:52 pm, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:[vbcol=seagreen]
> You can use a computed column. Check this script:
> CREATE TABLE dbo.t1
> (nametype1 char(2),
> nametype2 char(2),
> nameval AS
> CASE
> WHEN nametype1 = nametype2 THEN 2
> WHEN nametype1 = REVERSE(nametype2) THEN 1
> ELSE 0 -- error?
> END)
> GO
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> SELECT *
> FROM dbo.t1
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
>
>
>
>
>
>
>
>
instead of creatating in new table can i update the value in
existing table please suggest.|||UPDATE t1 SET nameval =
CASE WHEN nametype1 = nametype2 THEN 1
WHEN nametype1 = REVERSE(nametype2) THEN 2
END
Regards
Amish Shah
http://shahamishm.tripod.com
On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> On Jan 30, 3:52 pm, "Dejan Sarka"
>
>
> <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> instead of creatating in new table can i update the value in
> existing table please suggest.- Hide quoted text -
> - Show quoted text -|||On Jan 30, 5:33 pm, "amish" <shahami...@.gmail.com> wrote:[vbcol=seagreen]
> UPDATE t1 SET nameval =
> CASE WHEN nametype1 = nametype2 THEN 1
> WHEN nametype1 = REVERSE(nametype2) THEN 2
> END
> Regards
> Amish Shahhttp://shahamishm.tripod.com
> On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2
nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1
nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1
please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.
Regards

how to compare value of two fileds and based on that insert value into third fileds

Hi,
I have a database with table name as test in that i have 6 colums
they are
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
please suggest query in sql which i can run to do this .
RegardsYou can use a computed column. Check this script:
CREATE TABLE dbo.t1
(nametype1 char(2),
nametype2 char(2),
nameval AS
CASE
WHEN nametype1 = nametype2 THEN 2
WHEN nametype1 = REVERSE(nametype2) THEN 1
ELSE 0 -- error?
END)
GO
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
SELECT *
FROM dbo.t1
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Tradeorganizer" <tradeorganizer@.gmail.com> wrote in message
news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> Hi,
> I have a database with table name as test in that i have 6 colums
> they are
> name varchar (20)
> address varchar (20)
> position varchar (20)
> nametype1 varchar (20)
> nametype2 varchar (20)
> nameval varchar(20)
> now in the nametype1 and nametype2 there are values like
> nametype1 nametype2
> "AB" "BA"
> "BB" "BB"
> "AA" "AA"
> "BA" "AB"
> now depending upon the combination i want to assign value to the thrid
> field that is nameval like example below
> nametype1 nametype2 nameval
> "AB" "BA" 1
> "BB" "BB" 2
> "AA" "AA" 2
> "BA" "AB" 1
> please suggest query in sql which i can run to do this .
> Regards
>|||On Jan 30, 3:52 pm, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> You can use a computed column. Check this script:
> CREATE TABLE dbo.t1
> (nametype1 char(2),
> nametype2 char(2),
> nameval AS
> CASE
> WHEN nametype1 = nametype2 THEN 2
> WHEN nametype1 = REVERSE(nametype2) THEN 1
> ELSE 0 -- error?
> END)
> GO
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> SELECT *
> FROM dbo.t1
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > Hi,
> > I have a database with table name as test in that i have 6 colums
> > they are
> > name varchar (20)
> > address varchar (20)
> > position varchar (20)
> > nametype1 varchar (20)
> > nametype2 varchar (20)
> > nameval varchar(20)
> > now in the nametype1 and nametype2 there are values like
> > nametype1 nametype2
> > "AB" "BA"
> > "BB" "BB"
> > "AA" "AA"
> > "BA" "AB"
> > now depending upon the combination i want to assign value to the thrid
> > field that is nameval like example below
> > nametype1 nametype2 nameval
> > "AB" "BA" 1
> > "BB" "BB" 2
> > "AA" "AA" 2
> > "BA" "AB" 1
> > please suggest query in sql which i can run to do this .
> > Regards
instead of creatating in new table can i update the value in
existing table please suggest.|||UPDATE t1 SET nameval =CASE WHEN nametype1 = nametype2 THEN 1
WHEN nametype1 = REVERSE(nametype2) THEN 2
END
Regards
Amish Shah
http://shahamishm.tripod.com
On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> On Jan 30, 3:52 pm, "Dejan Sarka"
>
>
> <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> > You can use a computed column. Check this script:
> > CREATE TABLE dbo.t1
> > (nametype1 char(2),
> > nametype2 char(2),
> > nameval AS
> > CASE
> > WHEN nametype1 = nametype2 THEN 2
> > WHEN nametype1 = REVERSE(nametype2) THEN 1
> > ELSE 0 -- error?
> > END)
> > GO
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> > SELECT *
> > FROM dbo.t1
> > --
> > Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> > "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> >news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > > Hi,
> > > I have a database with table name as test in that i have 6 colums
> > > they are
> > > name varchar (20)
> > > address varchar (20)
> > > position varchar (20)
> > > nametype1 varchar (20)
> > > nametype2 varchar (20)
> > > nameval varchar(20)
> > > now in the nametype1 and nametype2 there are values like
> > > nametype1 nametype2
> > > "AB" "BA"
> > > "BB" "BB"
> > > "AA" "AA"
> > > "BA" "AB"
> > > now depending upon the combination i want to assign value to the thrid
> > > field that is nameval like example below
> > > nametype1 nametype2 nameval
> > > "AB" "BA" 1
> > > "BB" "BB" 2
> > > "AA" "AA" 2
> > > "BA" "AB" 1
> > > please suggest query in sql which i can run to do this .
> > > Regards
> instead of creatating in new table can i update the value in
> existing table please suggest.- Hide quoted text -
> - Show quoted text -|||On Jan 30, 5:33 pm, "amish" <shahami...@.gmail.com> wrote:
> UPDATE t1 SET nameval => CASE WHEN nametype1 = nametype2 THEN 1
> WHEN nametype1 = REVERSE(nametype2) THEN 2
> END
> Regards
> Amish Shahhttp://shahamishm.tripod.com
> On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> > On Jan 30, 3:52 pm, "Dejan Sarka"
> > <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> > > You can use a computed column. Check this script:
> > > CREATE TABLE dbo.t1
> > > (nametype1 char(2),
> > > nametype2 char(2),
> > > nameval AS
> > > CASE
> > > WHEN nametype1 = nametype2 THEN 2
> > > WHEN nametype1 = REVERSE(nametype2) THEN 1
> > > ELSE 0 -- error?
> > > END)
> > > GO
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> > > SELECT *
> > > FROM dbo.t1
> > > --
> > > Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> > > "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> > >news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > > > Hi,
> > > > I have a database with table name as test in that i have 6 colums
> > > > they are
> > > > name varchar (20)
> > > > address varchar (20)
> > > > position varchar (20)
> > > > nametype1 varchar (20)
> > > > nametype2 varchar (20)
> > > > nameval varchar(20)
> > > > now in the nametype1 and nametype2 there are values like
> > > > nametype1 nametype2
> > > > "AB" "BA"
> > > > "BB" "BB"
> > > > "AA" "AA"
> > > > "BA" "AB"
> > > > now depending upon the combination i want to assign value to the thrid
> > > > field that is nameval like example below
> > > > nametype1 nametype2 nameval
> > > > "AB" "BA" 1
> > > > "BB" "BB" 2
> > > > "AA" "AA" 2
> > > > "BA" "AB" 1
> > > > please suggest query in sql which i can run to do this .
> > > > Regards
> > instead of creatating in new table can i update the value in
> > existing table please suggest.- Hide quoted text -
> > - Show quoted text -
hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2
nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1
nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1
please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.
Regards