datatype problem

how can i transfer incoming data from flat file which would be a string to my sql table of column int...

i have a problem with datatype can i conver string to int how should i do it...new to it

please help!!

This is what the Data Conversion Task is for. U could also use the Derived Column Task. Place it between source and destination, convert your column there.

What more can I say?

Pipo1

|||Use a derived column transformation.

This is one example of an expression you could use: (DT_I4)[Your_Column]sql

Datatype for Primary key fields ...

Hi,
As far as my understanding goes, normally PK would be set on fields whose
datatype is INT. But in one of the project I saw 99% of the tables they have
used Varchar datatype for PK fields.
This internally means that it would string comparisons. I was arguing that
SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
something? Any pointers on this topic would be of great help to me.
Best Regards
Vadivel
http://vadivel.blogspot.comVadivel wrote:

> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they ha
ve
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
This is the wrong question because the datatype for keys is determined
by the data you need to model in the table. For example how are you
going to represent names using an INTEGER?
If you meant to ask "what should I use for an artiificial surrogate
key?" then you can search the archives of this group for many previous
discussions on that topic.
David Portas
SQL Server MVP
--|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
If you don't use surrogates, I'd say that most natural primary keys are
strings.
Even telephone numbers, license plate numbers, zip codes, serial codes,
credit card numbers, bar codes, invoice numbers, bank account numbers... are
in reality, strings.|||Are you speaking of a natural or surrogate primary key?
http://www.aspfaq.com/show.asp?id=2504
Natural keys can be a combination of most any data type. However, if the key
in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) then
do try to define it as integer, becuase it's storage will be smaller than
character, and it will thus result in less memory usage and fewer index
pages to traverse.
If you plan to use an identity column as a surrogate primary key, then I
don't see a reason to use anything but an integer.
http://www.windowsitpro.com/Article...ArticleID=23449
Data Type Performance Tuning Tips for Microsoft SQL Server
http://www.sql-server-performance.com/datatypes.asp
In general integer based comparisons are more efficient than Char or VarChar
comparisons, but I don't know of SQL Server specifically not being not good
at character comparisons relative to any other DBMS system such as Oracle or
DB2.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com|||I know that telephone numbers, Credit card nos, Zipcode all would be varchar
fields only. As we won't be doing any mathematical calculation based on that
data there isn't a need for us to go for INT datatype. Even though u would
have those fields as Varchar in ur DB won't you have a ID field in that
table? Won't that ID field be of INT datatype?
Best Regards
Vadivel
http://vadivel.blogspot.com
"Raymond D'Anjou" wrote:

> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> If you don't use surrogates, I'd say that most natural primary keys are
> strings.
> Even telephone numbers, license plate numbers, zip codes, serial codes,
> credit card numbers, bar codes, invoice numbers, bank account numbers... a
re
> in reality, strings.
>
>|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
>I know that telephone numbers, Credit card nos, Zipcode all would be
>varchar
> fields only. As we won't be doing any mathematical calculation based on
> that
> data there isn't a need for us to go for INT datatype. Even though u would
> have those fields as Varchar in ur DB won't you have a ID field in that
> table? Won't that ID field be of INT datatype?
> Best Regards
> Vadivel
Read JTs response including the links.
Your ID field (column) is a surrogate.
The use of surrogates (including Identity) has been discussed in this
newsgroup "ad nauseum".
Even though you can use surrogates as keys, there should always exist a
"natural" primary key in your tables.|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:u8lYzCMAGHA.3936@.TK2MSFTNGP12.phx.gbl...
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
> Read JTs response including the links.
> Your ID field (column) is a surrogate.
> The use of surrogates (including Identity) has been discussed in this
> newsgroup "ad nauseum".
> Even though you can use surrogates as keys, there should always exist a
> "natural" primary key in your tables.
One other thing:
Concerning your "performance hit" using INTs versus VARCHARs.
From what I've read, especially in today's databases, if there is one it's
negligable.|||Thanks for the links JT.
Lets assume that I have a table with just two fields,
CityID Varchar(20) -- PK
CityName Varchar(50)
Now I would be using this field CityID in some other table as a FK field. Is
that ok? Or is it advisable / mandatory to have another column with INT
datatype and use it in other tbls as FK?
Best Regards
Vadivel
http://vadivel.blogspot.com
"JT" wrote:

> Are you speaking of a natural or surrogate primary key?
> http://www.aspfaq.com/show.asp?id=2504
> Natural keys can be a combination of most any data type. However, if the k
ey
> in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) th
en
> do try to define it as integer, becuase it's storage will be smaller than
> character, and it will thus result in less memory usage and fewer index
> pages to traverse.
> If you plan to use an identity column as a surrogate primary key, then I
> don't see a reason to use anything but an integer.
> http://www.windowsitpro.com/Article...ArticleID=23449
> Data Type Performance Tuning Tips for Microsoft SQL Server
> http://www.sql-server-performance.com/datatypes.asp
> In general integer based comparisons are more efficient than Char or VarCh
ar
> comparisons, but I don't know of SQL Server specifically not being not goo
d
> at character comparisons relative to any other DBMS system such as Oracle
or
> DB2.
>
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
>
>|||If CityID is the primary key, then that is the column you want to use as the
foreign key when joining referencing tables. However, I don't understand why
CityID would be 20 characters long. Is this something like an ISO code
assigned to every city on the planet? If CityID contains embedded attributes
like geographic coordinates or nation, state, county codes, then split those
attributes out as seperate columns.
Provide more details about what CityID means and how it's values are
assigned.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:67DAA04A-A277-49AB-8745-521699D2F06F@.microsoft.com...
> Thanks for the links JT.
> Lets assume that I have a table with just two fields,
> CityID Varchar(20) -- PK
> CityName Varchar(50)
> Now I would be using this field CityID in some other table as a FK field.
> Is
> that ok? Or is it advisable / mandatory to have another column with INT
> datatype and use it in other tbls as FK?
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
>
> "JT" wrote:
>

DataType Conversion using WHERE IN ( )

I am getting a "Syntax error converting the varchar value '10,90' to a column of data type int." error when I run the following procedure:

@.myList varchar(200)

SELECT column1
FROM table1
WHERE table1.ID IN (@.myList)

When @.myList is a single value, I get no errors. However, when @.myList is a comma separated list like in the message above, I error out. I am using SQL Server 2000.

How else can I build this list of IDs? Thank you in advance for your comments.

--ColonelYou cannot do what you are trying to do. YOu need to use dynamic SQL, or send in a string and use a function to create a table variable and do the operation based upon a select on that table variable.|||I found that my varchar parameter was being sent in with single quotes around it. I removed these, and now my WHERE clause looks like this:

WHERE table1.ID IN (REPLACE(@.myList,'''',NULL))

and it works just fine.

I did not add those quotes to the list of values. I believe that SQL Server adds them to delimit the text. Thank you for your comments.

Datatype COnversion chart

Do you have a SQL Datataype conversion chart, ie a chart that represents if for eg an INT datatype can be converted to Datetime... etc...?

I dont need the one on SQL Books online. But need some other chart that lists out the datatype covnersions in SQL

If you are using T-SQL to do the 'conversion', the chart in Books Online is the definitive source.

And since it is the most complete source, I guess I don't understand what you are hoping to find.

|||

What does this mean? "lists out the datatype covnersions in SQL"

There are a couple of things here. Are you talking about implicit conversions? Or what types you can convert using cast and convert? Both are included in the chart in BOL in the CAST and CONVERT section.

Can you give us a sample of what you are looking for?

|||Btw, if you don't find the information in the Books Online topic useful or not satisfactory then please use the "Send Feedback" link in the topic. This will help you to provide feedback directly to the topic owners and will help improve the quality of the documentation.|||

I tried to convert an INT to a datetime, it gave an error "Arithmetic overflow error converting expression to data type datetime."

So I first converted it to varchar, and then to datetime, then it worked fine. eg

select convert(datetime,convert(varchar,xxx)),xxx,* from tblq

However, when I looked up, the BOL, it shows That Conversion from INT TO DATETIME, AND VARCHAR TO DATETIME Are both IMPLICT... Then how come the convert from Int to datetime errored out....?

I guess I am not able to Interpret the BOL Doc, for Implied and Explict conversions.. If it says impied conversion for both Varchar to datetime, and Int to datetime-- then why does 1 work and not the other.

|||

Sounds like a typo in the conversion -number too big, or perhaps, not a number ...

The following works as expected:

select convert( datetime, 2958463 )

9999-12-31 00:00:00.000

The following fails with the error you received -only 1 digit larger...

select convert( datetime, 2958464 )

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

|||hi,
what's the integer you're trying to convert as datetime? if it's greater than 2,958,463, it will produce that error because i think sqlserver supports datetime from 01/01/17? [convert(datetime, -53690)] to 12/31/9999 [convert(datetime, 2958463)].

- clintz|||

The Integer I am trying to convert is a large one, ie

select convert(datetime,20060404)
It is has a date value.

But I still need to know what is the diff between the Implicit and explicit conversions. BOL was not clear enough.

|||

Is 20060404 an integer, or a date? (It looks like a date to me...)

There is a really, really, big difference.

(The interger value 38809 would convert to 2006/04/04.)

As I, and others indicated in other responses to this post, the largest integer value that will convert to a datetime is 2958463. Your number, 20060404, greatly exceeds that value and WILL always cause an overflow error.

|||

If your data looks like 20060404 and is stored as interger values, and you wish to convert them to datetime values, try something like this:

First convert (or cast) to char(8), and then cast that to a datetime.

Code Snippet

SELECT cast( cast( 20060404 AS char(8)) AS datetime )

2006-04-04 00:00:00.000

Datatype change INT to BIGINT on a large table

Hi folks,
I have a table which is of 500 GB in size. I need to change the datatype
of a column from INT to BIGINT.
When I tried making this change from Enterprise Manager, it was throwing
log space is full. I also truncated the log and tried again, eventhen I
face the same problem.
Also I have limited space available on data drive. After some research I
found that SQL Server interally creates a Tmp table with the new
datatype, populates that table with orginal table data, drops the
original table and then renames the Tmp table.
So I must need atleast 500 GB additional freespace on data drive, but I
do not have 500 GB free space on data drive.
I am just thinking the below alternate way to do this task.
1. BCP out the data to a temporary mapped network drive which has 500 GB
free space.
2. Drop the table.
3. Recreate the table with BIGINT datatype on the required column.
4. BCP in the data.
5. Recreate the Keys and constraints.
Can someone suggest me whether this is the best way, any possibility of
loosing the data if I follow this way. Please suggest me if there is a
better approach.
Thanks in advance.
*** Sent via Developersdex http://www.examnotes.net ***Another option is to use ALTER TABLE ... ALTER COLUMN ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Tibor,
If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
file?
*** Sent via Developersdex http://www.examnotes.net ***|||You need to test first. Create a similar table in a smaller database, copy o
ver a subset of the rows
and do a test. Sometimes, these changes can go without touching the data (im
mediately), sometimes,
all data is changed immediately, and changes has to be logged. I haven't see
n any document
describing the exact rules for when a change is immediate or not. So, do a t
est first to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:OkPRGnDyFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor,
> If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
> file?
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||I think your bulk copy solution is the best way to go. To reduce the size of
the exported file, use native format instead of fixed width or tab delimited
text columns. To reduce transaction logging while importing the data, set
the database recovery model to "bulk insert" or "simple", set the database
to single user / dbo use only mode, and re-create indexes only after the
import has completed.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks JT for your excellent suggestions/comments.
I tried Tibor's suggestion (ALTER TABLE...ALTER COLUMN), it logs to
transaction log even when the recovery model is set to simple.
Internally it updates all the rows. But I do not have enough space on
log drive. So I will have to go with BCP option.
*** Sent via Developersdex http://www.examnotes.net ***|||I tried BCPing with Native format and Char format option. It seems that
the the file unloaded using Char format is smaller than the one created
with Native format. Any ideas..
--
*** Sent via Developersdex http://www.examnotes.net ***|||I don't recall offhand the specifics, but there are cases (perhaps with
decimal data types) where exporting to char format and then re-importing
will cause loss of data resolution. If you have space for exporting to
native format, then go ahead and use that.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:%235R%23ikNyFHA.3420@.TK2MSFTNGP10.phx.gbl...
>I tried BCPing with Native format and Char format option. It seems that
> the the file unloaded using Char format is smaller than the one created
> with Native format. Any ideas..
> --
> *** Sent via Developersdex http://www.examnotes.net ***sql

Datatime order by Problem

Hi everybody,
I have onde table called (TB) with two fields : F1(int) and F2(varchar:10)
In this table the field F2 is used to store dates in the format
dd/mm/yyyy (27/12/2002). Its is record as varchar and undesired.
I need to select all records from a single id from the field F1 and
then order by the result set by date to result set be this way:

F1 F2
------------
01 15/12/1975
01 15/12/1980
01 16/12/1998
01 27/12/2003
------------

To do this Im using the follow syntax :

SELECT * FROM TB
WHERE (F1 = '01')
ORDER BY CAST(F2 AS datetime(103))

But it gives the follow message erro trying to select data :

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

How can I convert the format used in the select to order by date type in a SQL Server range datetype to get a result set as above?

Thansk for attention.

Leonardo AlmeidaUse convert(datetime, f2, 103) instead of the cast.sql

Databse Design problem

Hello all,
I have a database design problem
I have a hierarchy that includes 5 levels and each level have a table
EX :
TABLE_L1
L1_ID INT AUTO
L1_CODE nvarchar(50)
L1_NAME nvarchar(255)
TABLE_L2
L2_ID INT AUTO
L1_ID INT
L2_CODE nvarchar(50)
L2_NAME nvarchar(255)
etc
The primary key is an id auto. (can be replaced by a GUID if it is
necessary)
The problem :
I have a user table and must affect rights on some members than can be a
different level of the hierarchy.
For example :
User 1 can access to the member A of level one and all the level A
children's but he can also access to member B4 of level 2
I try to implement integrity so when a member is deleted all rights are
deleted too.
My first design is to have one security definition table per level but i
think i am not the first person to have to give rights on different levels
of a hierarchy and they're must be a "best practice" to design it!
anoyone knows an "ideal" solution?
Thanks
cymryrPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
table .. <<
Then this is not a hierarchy. It should be in one table. You should
not use any kind of auto numbering in a relational database and a GUID
is the worst choice.
How much research did you do when you decided to use NVARCHAR(50) and
NVARCHAR(255). Thise "magic numbers" are a sign of no design work at
all.
children's but he can also access to member B4 of level 2 <<
I have implemented a security scheme with a nested sets model in which
privileges were inherited down the tree from a superior to a
subordinate.
You seem to have no rule for determining privileges, so you will have
to list all combinations.
I have an entire book on TREES & HIERARCHIES IN SQL which might help.|||> How much research did you do when you decided to use NVARCHAR(50) and
> NVARCHAR(255). Thise "magic numbers" are a sign of no design work at
> all.
This is not "Magic numbers" there are the result of an extraction. i am not
responsable of the other database and my design is subbordinate by the other
apps

> children's but he can also access to member B4 of level 2 <<
> I have implemented a security scheme with a nested sets model in which
> privileges were inherited down the tree from a superior to a
> subordinate.
> You seem to have no rule for determining privileges, so you will have
> to list all combinations.
> I have an entire book on TREES & HIERARCHIES IN SQL which might help.
Wich book?I am very interested|||>> Which book? I am very interested <<
TREES & HIERARCHIES IN SQL (Morgan-Kaufmann, 2004)
--CELKO--

Databasename Variable

declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'SRVDB1@.mydomain.com',
@.TO = N'gmatteson@.mydomain',
@.priority = N'HIGH',
@.subject = N'Process Success',
@.message = N'Database Backup Succeeded.', - Rather than putting Database
Backup Succeeded, I would like to put "@.databasename Backup Succeeded".
@.server = N'mail.mydomain.com',
@.attachment = N'F:\TEST.txt'
select RC = @.rc
GO
Anyone know how to do this, this is my first stored procedure and I really
have no idea how the syntax works! thanks.You can't concatenate there, you'll have to build the message first.
DECLARE @.msg VARCHAR(255), @.rc INT
SET @.msg = @.databasename + ' Backup Succeeded.'
EXEC @.rc = master..xp_smtp_sendmail
...
@.message = @.msg,
What does this have to do with DTS, setup, security? Followups set to
.server only.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:##3QnDqGFHA.544@.TK2MSFTNGP12.phx.gbl...
> declare @.rc int
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = N'SRVDB1@.mydomain.com',
> @.TO = N'gmatteson@.mydomain',
> @.priority = N'HIGH',
> @.subject = N'Process Success',
> @.message = N'Database Backup Succeeded.', - Rather than putting Database
> Backup Succeeded, I would like to put "@.databasename Backup Succeeded".
> @.server = N'mail.mydomain.com',
> @.attachment = N'F:\TEST.txt'
> select RC = @.rc
> GO
> Anyone know how to do this, this is my first stored procedure and I really
> have no idea how the syntax works! thanks.
>

database_name.table_name in create table ?

Hi,
In create table statements we use database_name.table_name.
Eg :
create table my_database.test_table(sno int ....)
What is the meaning of this ? Is that, the table is created in the specified database_name ?
Please clarify.
Thanks,
SamOf course ... But the correct form include owner information as below ...

CREATE TABLE DBName.OwnerName.TableName (ID int)

Bye.|||Can't I give

create table db_name.table_name

( omitting the owner name ) ?|||No. You can use CREATE TABLE db_name..table_name.

The owner will default to whoever is running the CREATE TABLE statement. If the are db_owner, it will default to dbo.