how to insert only distinct values from a Flat File

I have to insert the Values from the Flat Files , My table structures have Primary keys , how do i insert only the distinct values into the table without the ERROR VIOLATION OF Primary Key already exists a record.

Dropping and Adding Relationships after insert is a way but doesnt serve the whole purpose is there a way we can eliminate duplicate records based on their Primary key before inserting them into the Database.

You can do a lookup against your target table. Redirect the error output and send these rows to a sql destination for the target table (i.e. if the lookup didn't find it, it is a new row)

See the following thread...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Sort transformation has a check box that allows you to eliminate duplicates; another approach could be to use a agregation transformation.|||Thanks for the Quick Solutions i will do that and come back with the feedback, what about the performance which one is better the sort or Aggregates using group by or Lookup|||Are there duplicate primary key's in your flat file? If so you will need to go with either the aggregate or sort solutions (I may have misread your question, the lookup will find any primary keys already in the table before you start to insert your new records). I don't know which of these will be more effecient for you, but you can always try them both out for a few test runs and see for yourself as it will often depend on environment and data being loaded as to which will come out ahead.|||

The Lookup approach will not detect duplicates within the batch being processed. You would need to change it to no-cache mode. Sort and aggregation transformation should be give you about the same performance. In general the no-cache lookup, sort or aggregation approach are not great from the performance standpoint; but that would depend on many factors; so test and measure yourself.

Sort/aggregation transformation cache the full set of rows in RAM; so if the volume of data to be de-duplicated is huge; the system could run out of memory.

An alternative could be to use an staging table and then let the DB engine to do the dedup work. (e.g. http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html )|||

I got to filter out the distinct values using aggregate transformation but i am unsure baout how to get all the columns into the output ... as the Aggregate is returning only the columns used to get the distinct on the Primary Keys.

|||There are two fundamentals being discussed in this thread. We've yet to get the crucial question answered though:

Are you trying to dedupe the source data before going into the destination table, or are you trying to prevent duplicate records from getting inserted (unique data from the source, but not necessarily all unique in the destination) and hence raising a primary key violation?

Deduping the source data can be done with an aggregate or the sort transformations. You could also load the data into a staging table (as Rafael stated) and then run a SQL statement against that data (select DISTINCT perhaps). Using the techniques described in the thread linked to earlier, you can ensure that your data does not violate primary keys by using a lookup. Note that you may have to do a combination of all of the above.|||

Dev2624 wrote:

I got to filter out the distinct values using aggregate transformation but i am unsure baout how to get all the columns into the output ... as the Aggregate is returning only the columns used to get the distinct on the Primary Keys.

Use the sort transformation instead.|||I am trying to Prevent the Duplicate Records from getting inserted . i am working on it will post back with the results. Thanks!!!sql

How to Insert Null Values into a DataBase Field

I have a function that updates a table in sql server

If a field is left blank I want the database field set to Null. I use:
sqlCmd.Parameter.Add("@.somefield, someintvalue) to pass all my values to the database.

if someintvalue is null I want to set @.somefield to dbnull

How can I do this? I get errors if I submit a null integer when there is a foreign key constraint. What is the best way to handle this? should I just make all parameters objects? so that I can set them to DBNull.Value?

Also on a side note, when you are populating fields from a datatable, is there a better way to set the values (i.e. of a textbox) than cheking for DBNull before assigning?

check this out|||

FOREIGN KEY can allow NULL values if it is a UNIQUE constraint. Run a search in the BOL(books online) enable NULL on FOREIGN KEY. The following is from the BOL. Hope this helps.

"A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can containnull values; however, if any column of a composite FOREIGN KEY constraint containsnull values, then verification of the FOREIGN KEY constraint will be skipped."

sql

How to insert NULL char values in SQLSERVER with a SQL sentence?

Hi everyone!
I am working with Delphi v7 and MS SQLServer.
I am trying to insert data in a table with a SQL sentence. Some of the
fields of my table are type char or varchar, and they can have null
values.
What do i have to write in the SQL sentence to insert a null value in
those fields?
I tried with '', an empty String, but it doesnt work, the tables
stores an empty String (logical :-)).
In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
value, but how can i tell this to the SQLServer through a SQL
Sentence?

Well, thank you very much.On 29 Jul 2004 04:15:15 -0700, schumacker wrote:

>Hi everyone!
>I am working with Delphi v7 and MS SQLServer.
>I am trying to insert data in a table with a SQL sentence. Some of the
>fields of my table are type char or varchar, and they can have null
>values.
>What do i have to write in the SQL sentence to insert a null value in
>those fields?
>I tried with '', an empty String, but it doesnt work, the tables
>stores an empty String (logical :-)).
>In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
>value, but how can i tell this to the SQLServer through a SQL
>Sentence?
>Well, thank you very much.

Hi schumacker,

INSERT INTO MyTable (Col1, Col2, Col3)
VALUES (1, NULL, 3)

or

INSERT INTO MyTable (Col1, Col2, Col3)
SELECT 1, NULL, 3

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Given:

create table foo
(col1 char(1) not null,
col2 char(1) null)

you can insert nulls to col2 by either explicitly specifying a null for the
content:

insert foo (col1, col2) values ('a',null)

or by skipping it in the column list and SQL Server will automatically
insert null:

insert foo (col1) values ('b')

Check it:

select * from foo

outputs:

col1 col2
-- --
a NULL
b NULL

"schumacker" <miguelcampoy@.hotmail.com> wrote in message
news:e1eadaf3.0407290315.99cd0d2@.posting.google.co m...
> Hi everyone!
> I am working with Delphi v7 and MS SQLServer.
> I am trying to insert data in a table with a SQL sentence. Some of the
> fields of my table are type char or varchar, and they can have null
> values.
> What do i have to write in the SQL sentence to insert a null value in
> those fields?
> I tried with '', an empty String, but it doesnt work, the tables
> stores an empty String (logical :-)).
> In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
> value, but how can i tell this to the SQLServer through a SQL
> Sentence?
> Well, thank you very much.

How to insert multiple values to a column at a time?

Hi all,

I was looking to insert multiple values to a single column at a time in SQl.

Any help pleas!

Thank you

Ephi:

What exactly do you mean by "multiple values"? Different data types? A vector? An array? What exactly.

Dave

|||

Lets say I have a single Column called X and I want to insert multiple values into X like (1,2,3) at a time through using the insert statement.

Thank you in advance.

|||Hi,

In reality it is not acceptable according to data normalization rules.
I.e. if you want to have such behavior you should better create an additional table and have foreign constraints mapping to it.
Some example:

table_x(
field_1 .....,
field_2 .....,
field_in_which_you_want_to_have_multiple_values.....
);

table_y(
value_identifier .....,
value nvarchar(1024) ....
);

So you firstly insert several values to table 'table_y' and then just add
value_identifier to a 'table_x.field_in_which_you_want_to_have_multiple_values'|||

do you mean you want to create multiple rows, with one row for each value, using a single Insert statement?

This is not possible. Insert only creates one row in the table.

Unless, of course, you are inserting into one table using the values from another table, in which case you can use the insert...select syntax.

Normal insert syntax:

insert table_name
(col1, col2, col3)
values
(val1, val2, val3)

only inserts one row.

|||I would recommend you use Itzik Ben-Gan's Split function:

CREATE FUNCTION dbo.fn_SplitTSQL
(@.arr NVARCHAR(MAX), @.separator NVARCHAR(1) = N',') RETURNS TABLE
AS
RETURN
SELECT
n - LEN(REPLACE(LEFT(@.arr, n), @.separator, '')) + 1 AS pos,
SUBSTRING(@.arr, n,
CHARINDEX(@.separator, @.arr + @.separator, n) - n) AS element
FROM dbo.Nums
WHERE n <= LEN(@.arr)
AND SUBSTRING(@.separator + @.arr, n, 1) = @.separator;Once you've got this, you could do something like:

insert into mytable (col1, col2, col3)
select 'Val1', 'Val2', element
from dbo.fn_SplitTSQL(N'1,2,3',N',')

This should handle it nicely for you. Oh yes, and you'll need a table called Nums with a field called 'n', which you have populated from 1 to some arbitrarily large number. 1000 might be big enough for most of your uses...

There's more on this at:
http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt

Robsql

How to insert multiple values in a single column

I need to display output as shown below. In Col1, Col2 and Col3 I want to insert values from subqueries. But do not want to use sub-reports... is there any alternative to subqueries.

In Col1, Col2 and Col3 there can be any number of values.

Company

BankCol1Col2Col3AstroTechICICI

123

5

34

MindTreeHDFC

54

8

why don't you want to use a sub-report?

|||

It takes very long time, because i have 17 columns in the report.. its not fisible to insert 15 sub-reports

|||

Ah I understand, and agree. Not sure how else to implement this though...could you insert another table into your main table's cells?

|||

Not resolved yet... any ideas...

|||

One idea would be to build your data using a view. Capture the main data (Company, Bank, ID) and then capture each columns' data (based on the ID in first query). Select from the view.

I think your data would be more like:

ID Company Bank ManagerNames BranchOffices TopSalesNames

1 CompanyA BankOne Jerry, Ted, Lisa Omaha, Chicago Fred, Mary

2 CompanyB BankTwo Paul Lincoln, Springfield, Florence William, John

Not sure if this meets your needs. You may be able to format the multi-value rows once in the report. Hope this helps.

|||

thanks... I will try this

how to insert multiple records into table

insert into table1 (colname) values (value1)

can only insert one record into the table. How to insert multiple records as value1, value2, value3... into a table?

We can of course use the above repeatedly, but if I don't know how many records (which is a variable), and I want to write a code which just take

value1, value2, value3 ...

from the clipboard, to paste as a input. How to insert those multiple records into table without split it. Thanks

What is the source of the data? from another table?

|||

If you are taking values from another table then you can insert multiple records.

Any ways i think it internally dosen't make much difference if you use multiple records or single record multiply internall it will fire that many insert statements only.

I find this intresting article guess it will help

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

|||

U want place into the same table r other clarity is required

Insert into x select * from x

insert into x select * from y

If u want to copy the data of entire table use * other wise U have to specify column clause

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

I hope I'm getting your question right.

Are you looking for something like

insert into table1 ( colname )
select value1 union all
select value2 union all
.
.
.
select valueN

If you can specify your problem in details then we can help you better.

|||

Thank everyone for your input. What I want was taking the contents of multiple records delimited by delimiter from clipboard as a string, then insert into a table. I complished by writing a user function which takes a string, then output a table with multiple records, it works as

insert into table1 (ID)

select values from fn_StringToTable(@.myString, @.delimiter) -- here @.myString='00a1,00a2,00a3,...'; @.delimiter=','

output table1 got the IDs from the clipboard which has'00a1,00a2,00a3,...

Thanks

How to INSERT INTO [Table] ([Field]) VALUES(I Have a in value)

Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')

please teach me how to

xxxINSERT INTO [Table] ([Field]) VALUES('I Have a '' in value')

instead of one single quote, u need to add one more.|||Use Parameterized Queries...you woudnt have to worry about escape characters besides saving your db from sqlinjection attacks.

hth

how to insert int variable...

hi,

I have two question

string a;

int i;

insert into sss (id, name) values(.....)

as id is integer and name is nvarchar in the database.

how can I write this variable within the values paranthesis

2) how to adjust a column in sql server 2005 to auto number

thanks

I may be mistaken, but these seem to me to be questions which are not specific to the ADO.Net vNext technology preview--but rather general SQL server questions. Given that assumption, I'm moving this thread to a more appropriate forum. If I'm mistaken and this does more directly apply to the ADO.Net technology preview, please post again back in that forum.|||Do you want to know how to exactly insert them in .NET or TSQL ? TheI Insert should be straight forward using something like

insert into sss (id, name) values(i,a)

You can′t cahnge the id column to a autonumber (in SQL Server it is called identity column), you will have to add a column and assign the identity value while the creation to it. Thats actually what the designer like SSMS does behind the scenes, creating a new table with the ID column, copying all the data from one to the other table, renaming the new one and dropping the old.
You don′t have to care about that if you are using the GUI, its just to keep in mind as the process will take a while.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

how to INSERT from SELECT

why is this syntax incorrect?

INSERT INTO visits (visit_file_no, direct_or_insurance, insurance_company_id, visit_total_amount) VALUES (@.visit_file_no, @.direct_or_insurance, @.insurance_company_id, SELECT item_price FROM price_list WHERE item_code = 'FIRST_VISIT')

I haved all values passed by the windows form but i only need to retrieve the amount from item price table

Jassim:

Try this and see if it works better:

INSERT INTO visits
( visit_file_no,
direct_or_insurance,
insurance_company_id,
visit_total_amount
)
select @.visit_file_no,
@.direct_or_insurance,
@.insurance_company_id,
item_price
from price_list
where item_code = 'FIRST_VISIT'

How to INSERT DBNull.Values ? ...

Hello,

If I have a database table with column that can accept null values and I want to insert a null value into that column, what is the correct syntax?

db_cmd = New SqlCommand( "sp_stored_procedure", db_connection )
db_cmd.CommandType = CommandType.StoredProcedure
db_cmd.Parameters.Add( "@.col_to_set_to_null", ? )

ie: what expression can be used for "?"

Ideally, I want to check if a string value is empty and if so insert a null, but I have yet to see any examples, ie:

if ( str.empty ) then
insert DBNull.value into column
else
insert str into column
end if

Sincerely,

Brent D.db_cmd.Parameters.Add( "@.col_to_set_to_null", System.DBNull.Value )

How to insert auto increment?

Hi Expert,
How can I do this without enter 100 time every day?
insert into [tablename] (column1) values(1)
insert into [tablename] (column1) values(2)
....
insert into [tablename] (column1) values(100)
Thank you all for reply-MN
MN wrote:
> Hi Expert,
> How can I do this without enter 100 time every day?
> insert into [tablename] (column1) values(1)
> insert into [tablename] (column1) values(2)
> ...
> insert into [tablename] (column1) values(100)
> Thank you all for reply-MN
Why do you want to generate 100 rows per day if that's the only column?
Just increment a single value instead. If there are other columns
involved then maybe you can use an IDENTITY column.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi David,
Thank for reply. Yeah, some day I increate 100 rows, someday 90, or 10...the
value is vary. And there are no other column involved except IDENTITY column.
How can I do that? Regards-MN
"David Portas" wrote:

> MN wrote:
> Why do you want to generate 100 rows per day if that's the only column?
> Just increment a single value instead. If there are other columns
> involved then maybe you can use an IDENTITY column.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||MN wrote:
> Hi David,
> Thank for reply. Yeah, some day I increate 100 rows, someday 90, or 10...the
> value is vary. And there are no other column involved except IDENTITY column.
> How can I do that? Regards-MN
Don't. There are two good reasons. 1. It's inefficient (because a
single row will do the same thing). 2. It may be unreliable (an
IDENTITY sequence can have gaps so the maximum value doesn't
necessarily match the number of rows).
Instead, use a single row:
CREATE TABLE tbl (x INTEGER PRIMARY KEY DEFAULT (1) CHECK (x=1) /*
single row constraint */, col1 INTEGER NOT NULL);
INSERT INTO tbl (col1) VALUES (0);
GO
Then keep updating it like this:
UPDATE tbl SET col1 = col1 + 100 ;
In case you do find it useful again, you can populate a table with
default values only or an IDENTITY column only using the DEFAULT VALUES
clause:
INSERT INTO tbl DEFAULT VALUES;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Tue, 4 Apr 2006 13:16:01 -0700, MN wrote:

>Hi Expert,
>How can I do this without enter 100 time every day?
>insert into [tablename] (column1) values(1)
>insert into [tablename] (column1) values(2)
>...
>insert into [tablename] (column1) values(100)
>Thank you all for reply-MN
Hi MN,
I'd very much like to know why you need to do that....
But the asnwer is: make a permanent table of numbers in your database
(see http://www.aspfaq.com/show.asp?id=2516), and use that:
INSERT INTO tablename (column1)
SELECT Numbers.Number
FROM dbo.Numbers
WHERE Numbers.Number BETWEEN 1 AND 100
Hugo Kornelis, SQL Server MVP
sql

How to insert all the values from a listbox into the database?

I want that the user can chose several options from one 'listbox', and to do this, I have created two 'listbox', in the first one there are the options to select, and the second one is empty. Then, in order to select the options I want the user have select one or more options from the first 'listbox' and then click in a link to pass the options selected to the second 'listbox'. Thus, the valid options selected will be the text and values in the second 'listbox'. I have seen this system in some websites, and I think it is very clear.

Well, my question is, Is it possible to insert into the database all the values from a 'listbox' control? In my case from the second 'listbox' with all the values passed from the first one? If yes, in my database table I have to create one column (field) for every possible selected option?

Thank you,
Cesarwhat you should do is have a relational table that only holds, lets say, an ID for the user and a field for the selections that have been made.|||I don' t understand what you mean. If I have a table with two fields, 'User_id' and 'product_quality_num', the 'product_quality_num' field has a foreign key related with the 'Product_Q' table. So, the user only can enter the product_quality_num 1, 2, 3, 4, 5 or 6. Then, what happens if the user have selected product_quality_num 1, 3 and 6? The 'product_quality_num' field only accepts one number.. not three (1, 3 and 6)|||You have to get those values back to the server. There have been posts on how to pass back contents of a listbox that were added client sde, but one way you can do it is by adding the IDs to a hidden form field separated by a delimiter like a pipe while you're adding them to the dropdown. The dropdown really is just a visual. The hidden form field is what you really need.

Your hidden field winds up like 1|2|4| when you post back. On the server, grab that value, parse it into an array based on the pipe. Loop through the array calling an insert for each value. Or, you could pass back the string and parse it in a SQL function that does the same thing, but returns a table as its output.

How to INSERT a space?

Hi, how does one normally insert a space in a statement like this:

INSERT INTO table (column0, column1, column2)VALUES (getdate(),'blah',CONVERT(VARCHAR(19),GETDATE(), 120) +'blahblah')

In column2 the output looks like ' 2007-10-08 20:19:08blahblah', but I want it to be like '2007-10-08 20:19:08 blahblah' (two spaces between date and text).

Thanks,

Chris

why not this

CONVERT(varchar(19),Getdate(),120)+' '+'blahblah')

|||

INSERT INTO table (column0, column1, column2)VALUES (getdate(),'blah',CONVERT(VARCHAR(19),GETDATE(), 120) +' blahblah')orINSERT INTO table (column0, column1, column2)VALUES (getdate(),'blah',CONVERT(VARCHAR(19),GETDATE(), 120) +' ' +'blahblah')

|||

Hi

Use space function like this

space(0) or space(1) ....

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Also, you can use SqlServer function Space(SpaceNumber). In your case, you can do

VALUES (getdate(),'blah',CONVERT(VARCHAR(19),GETDATE(), 120) + Space(2) + 'blahblah')

|||

Yes, thanks. The Space(*) did it. Could've used the ' ' but that's kind of sloppy, IMO.

How to insert a parenthesis into a field

I'd like to know how to insert a parenthesis into a field:
Example:
insert into MyTable(mydescription) values ('4.ó%?)&.?')
I tried SET QUOTED_IDENTIFIER ON without success. The above is a scrambled
password. It must go into the database exactly as it appears.
Regards,
Jamie
Your code actually worked for me. The parenthesis shouldn't cause a problem
but some non-printable, control characters might. I suggest you insert data
like this as VARBINARY rather than strings so that you can safely insert any
byte values you may require.
Passwords? Don't store them in the database. Store a secure hash of the
password in the database instead. Maybe you meant that this was a password
hash but your use of the word "scrambled" implied to me that this is an
*encrypted* password. Storing encrypted passwords is not really a good idea
from a security point-of-view.
David Portas
SQL Server MVP
|||Please ignore this post. I was having a problem with syntax. It is solved.
Regards,
Jamie
"thejamie" wrote:

> I'd like to know how to insert a parenthesis into a field:
> Example:
> insert into MyTable(mydescription) values ('4.ó%?)&.?')
> I tried SET QUOTED_IDENTIFIER ON without success. The above is a scrambled
> password. It must go into the database exactly as it appears.
> --
> Regards,
> Jamie
|||Thanks David,
Ah... you're dead right and as it is now, I'm storing both. I figure that
scrambling the password is adequate to keep people from knowing that they
are passwords stored in a database provided I don't name the field something
conspicuous like 'password'. I'm not doing rocket science here, just
creating a record to read. Each scrambled password is also hashed. If the
scrambled password is altered, the hash won't work. I have enough checks
and balances to satisfy management and that satisfies me. I hash dates,
cpuids, networklogins, userid's, aliases... anything I can think of that
someone might play with. Probably slows the database down a bit, but since
it all gets done at startup, I can live with that too. I probably overdo
the hash thing and one of these days, I'll trim it down. For now, too much
is probably enough. Something like that.
Thanks for the advice though. Never thought of using the varbinary to
store the string. I do use it for the hash. The special characters should
store in the varchar though, shoudn't they?
Giac
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0tqdndBnn_mcJPjcRVn-jg@.giganews.com...
> Your code actually worked for me. The parenthesis shouldn't cause a
> problem but some non-printable, control characters might. I suggest you
> insert data like this as VARBINARY rather than strings so that you can
> safely insert any byte values you may require.
> Passwords? Don't store them in the database. Store a secure hash of the
> password in the database instead. Maybe you meant that this was a password
> hash but your use of the word "scrambled" implied to me that this is an
> *encrypted* password. Storing encrypted passwords is not really a good
> idea from a security point-of-view.
> --
> David Portas
> SQL Server MVP
> --
>
sql

How to insert a parenthesis into a field

I'd like to know how to insert a parenthesis into a field:
Example:
insert into MyTable(mydescription) values ('4.ó%ø)&.ö')
I tried SET QUOTED_IDENTIFIER ON without success. The above is a scrambled
password. It must go into the database exactly as it appears.
--
Regards,
JamieYour code actually worked for me. The parenthesis shouldn't cause a problem
but some non-printable, control characters might. I suggest you insert data
like this as VARBINARY rather than strings so that you can safely insert any
byte values you may require.
Passwords? Don't store them in the database. Store a secure hash of the
password in the database instead. Maybe you meant that this was a password
hash but your use of the word "scrambled" implied to me that this is an
*encrypted* password. Storing encrypted passwords is not really a good idea
from a security point-of-view.
--
David Portas
SQL Server MVP
--|||Please ignore this post. I was having a problem with syntax. It is solved.
Regards,
Jamie
"thejamie" wrote:
> I'd like to know how to insert a parenthesis into a field:
> Example:
> insert into MyTable(mydescription) values ('4.ó%ø)&.ö')
> I tried SET QUOTED_IDENTIFIER ON without success. The above is a scrambled
> password. It must go into the database exactly as it appears.
> --
> Regards,
> Jamie|||Thanks David,
Ah... you're dead right and as it is now, I'm storing both. I figure that
scrambling the password is adequate to keep people from knowing that they
are passwords stored in a database provided I don't name the field something
conspicuous like 'password'. I'm not doing rocket science here, just
creating a record to read. Each scrambled password is also hashed. If the
scrambled password is altered, the hash won't work. I have enough checks
and balances to satisfy management and that satisfies me. I hash dates,
cpuids, networklogins, userid's, aliases... anything I can think of that
someone might play with. Probably slows the database down a bit, but since
it all gets done at startup, I can live with that too. I probably overdo
the hash thing and one of these days, I'll trim it down. For now, too much
is probably enough. Something like that.
Thanks for the advice though. Never thought of using the varbinary to
store the string. I do use it for the hash. The special characters should
store in the varchar though, shoudn't they?
Giac
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0tqdndBnn_mcJPjcRVn-jg@.giganews.com...
> Your code actually worked for me. The parenthesis shouldn't cause a
> problem but some non-printable, control characters might. I suggest you
> insert data like this as VARBINARY rather than strings so that you can
> safely insert any byte values you may require.
> Passwords? Don't store them in the database. Store a secure hash of the
> password in the database instead. Maybe you meant that this was a password
> hash but your use of the word "scrambled" implied to me that this is an
> *encrypted* password. Storing encrypted passwords is not really a good
> idea from a security point-of-view.
> --
> David Portas
> SQL Server MVP
> --
>

how to insert a identity key through store procudure?

I want to insert 3 values to sql database through store procudure

-- a store procudure is like this--

ALTER PROCEDURE dbo.FreeExperience

@.identity ,

@.name nvarchar(20),
@.gender int,

AS
begin
Insert into list(cid,name,gender)
values (@.identity,@.name,@.gender)

end
RETURN

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

the cid is a identity value . it will +1 automatically when user insert a new data..

if i ingore this column in store procudure it will cause error (becuase this column is not allow null)

please help. thanks

Try

ALTER

PROCEDURE dbo.FreeExperience
@.namenvarchar(20),
@.genderint,
AS
INSERTINTO list(name,gender)values(@.name,@.gender)
RETURN
GO

or

CREATE

PROCEDURE dbo.FreeExperience2
@.identityINT,
@.namenvarchar(20),
@.genderint,
AS
SETIDENTITY_INSERT listON-- Allows Identity value to be supplied
Insertinto list(cid,name,gender)values(@.identity,@.name,@.gender)
SETIDENTITY_INSERT listOFF
RETURN|||

if you cid field is setup as identity in you table (cid int identity(1,1))

insert statement like this should work without any errors

Insert into list(name,gender)
values (@.name,@.gender)

and identity value will be automatically set by SQL server

|||

I still got error message...

exceptiona information: System.Data.Sqlclien.SqlException: procedure or parameters 'FreeExperience' must have a parameters'@.identity', but didn't provide..

p.s FreeExperience is my store procedure name..

|||

If you want SQL Sever to automatically add the Identity value for you, you need to remove the column entirely from the proc, and your INSERT statement. If you want to manually supply the value then you use the SET IDENTITY_INSERT <Table> ON clause, and provide the value you want to be used instead. IF the column is a Primary Key for the table, you also need to make sure the value you are providing does not already exist.

|||

thank you... but I am not sure if I really understand what did you mean

can you please give me a simple example for this ?

this store procudure is for a program which will insert new colum to table

1 column named " cid" in table which is set as a identity column will automatically +1 and insert to cid

|||

Use this stored procedure
alter PROCEDURE dbo.FreeExperience
@.name nvarchar(20),
@.gender int,
AS
Insert into list(name,gender) values (@.name,@.gender)
RETURN

|||

this stored procedure will cause error

because the cid column is not allow to be null...

|||

>>this stored procedure will cause error because the cid column is not allow to be null...

Identity columns are populated automatically by the database so the stored procedure will work!

|||

thank you .. but it really doesn't works..

it continue showing the error message...

can't insert NULL to 'cid，table'list'; not allow Null。INSERT fail..

I checked all column in table list from database, all column allow null except column cid.

the statement will work only if I set the cid column as accept null ..

please help..

|||

Modify ur table by setting the "IdentityIncrement=1, IdentitySeed=1" of column cId, then remove cId from ur storedprocedure

HTH

|||

Modify ur table by setting the "IsIdentity=Yes, IdentityIncrement=1, IdentitySeed=1" of column cId, then remove cId from ur storedprocedure

HTH

how to insert identity values

hi to the group,
i am small problem,
i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc)
with out using identity(sql server)/generated always(db2)
can any one knows please explain itDidn't I answer this one already?

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx|||Didn't I answer this one already?

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx
OMFG brett, are you kidding?

you seriously expect people to remember everything you wrote up to several years ago on a completely different site from this one?

i don't know, man, did you answer it already?

sheesh|||jagadish, look up the SET IDENTITY INSERT option in Books Online.

How to increment a field for a Line Number?

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

HI ,
Can you tell me clearly.please send how you tried it|||What is the relationship between the two columns ?

I don't think SQL Server 2000 will allow you to use the result of a Stored/Procedure / User Defined Function as the default value for a column.

SQL Server 2005 might.|||Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END|||

Quote:

Originally Posted by TrentC

Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END

It looks like you are trying to replicate the functionality of an identity column.

Unless the value in your line_number column is directly related to the information in that record just make your line_number column an int column with identity turned on and seed at +1.|||

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Hi,

I recently had to do the same thing, if your table has an identity column (i.e. primary key that is incremented automatically when you insert a new row), which all tables should, then you can use the following method:

First of all insert all your DocNum records into the table so that the identity column (ID say) is updated automatically. Then update the LineNum field as follows:

UPDATE
[TABLE_NAME]
SET
LineNum = (SELECT
COUNT(*)
FROM
[TABLE_NAME] t1
WHERE
t1.ID <= [TABLE_NAME].ID
AND
t1.DocNum = [TABLE_NAME].DocNum
)

How to improve the performance of a query?

I have a table called DMPD_Product_Lookup_Dom. It is a lookup table which contains values for certain fields of other tables in the database.
This takes long time to run.
Is there any way to improve performance of this query ??

SELECT
BNAD.Benefit_Admin_Cat_CD AS 'AdminCategory',
DOM1.Value_Description AS 'AdminCategoryDesc',
BNAD.Benefit_Component_Name_CD AS 'BenefitAdmin',
DOM2.Value_Description AS 'BenefitAdminDesc',
BNDT.Benefit_Rider_CD AS 'BenefitRider',
DOM3.Value_Description AS 'BenefitRiderDesc',
BNDT.Benefit_Exception_Ind AS 'Exception',
BNDT.Benefit_Detail_Desc_CD AS 'BenefitDetail',
DOM4.Value_Description AS 'BenefitDetailDesc',
DMCS.Cost_Share_Value_Type_CD AS 'CSValueType',
DOM5.Value_Description AS 'CSValueTypeDesc',
DMCS.Cost_Share_Value AS 'CS_Value',
DMCS.Cost_Share_Rule_Cat_CD AS 'CS_Rule_Cat_CD',
DOM6.Value_Description AS 'CS_Rule_Cat_Value',
BNCS.Cost_Share_Rule_Type_CD AS 'CS_Rule_Type_CD',
DOM7.Value_Description AS 'CSRuleTypeDesc',
BNCS.Cost_Share_Mbr_Family_Ind AS 'MemberOrFamily',
DOM8.Value_Description AS 'MemberOrFamilyDesc',
BNCS.Network_Ind AS 'NetworkInd'
FROM
prdtrk01..DMPD_Product_Lookup_Dom DOM1,
prdtrk01..DMPD_Product_Lookup_Dom DOM2,
prdtrk01..DMPD_Product_Lookup_Dom DOM3,
prdtrk01..DMPD_Product_Lookup_Dom DOM4,
prdtrk01..DMPD_Product_Lookup_Dom DOM5,
prdtrk01..DMPD_Product_Lookup_Dom DOM6,
prdtrk01..DMPD_Product_Lookup_Dom DOM7,
prdtrk01..DMPD_Product_Lookup_Dom DOM8,
prdtrk01..BNAD_Benefit_Admin BNAD,
prdtrk01..BNDT_Benefit_Detail BNDT,
prdtrk01..BNCS_Cost_Share_Rule BNCS,
prdtrk01..DMCS_Cost_Share_Dom DMCS
WHERE
BNAD.Benefit_Admin_ID = BNCS.Benefit_Admin_ID
AND BNDT.Benefit_Detail_ID = BNCS.Benefit_Detail_ID
AND DMCS.Cost_Share_Rule_ID = BNCS.Cost_Share_Rule_ID
AND DOM1.Product_Domain_Entity = "BNAD"
AND DOM1.Product_Attribute_Type = "Benefit_Admin_Cat_CD"
AND DOM1.Domain_Value = BNAD.Benefit_Admin_Cat_CD
AND DOM2.Product_Domain_Entity = "BNAD"
AND DOM2.Product_Attribute_Type = "Benefit_Component_Name_CD"
AND DOM2.Domain_Value = BNAD.Benefit_Component_Name_CD
AND DOM3.Product_Domain_Entity = "BNDT"
AND DOM3.Product_Attribute_Type = "Benefit_Rider_CD"
AND BNDT.Benefit_Rider_CD *= DOM3.Domain_Value
AND DOM4.Product_Domain_Entity = "BNDT"
AND DOM4.Product_Attribute_Type = "Benefit_Detail_Desc_CD"
AND DOM4.Domain_Value = BNDT.Benefit_Detail_Desc_CD
AND DOM5.Product_Domain_Entity = "DMCS"
AND DOM5.Product_Attribute_Type = "Cost_Share_Value_Type_CD"
AND DOM5.Domain_Value = DMCS.Cost_Share_Value_Type_CD
AND DOM6.Product_Domain_Entity = "DMCS"
AND DOM6.Product_Attribute_Type = "Cost_Share_Rule_Cat_CD"
AND DOM6.Domain_Value = DMCS.Cost_Share_Rule_Cat_CD
AND DOM7.Product_Domain_Entity = "BNCS"
AND DOM7.Product_Attribute_Type = "Cost_Share_Rule_Type_CD"
AND DOM7.Domain_Value = BNCS.Cost_Share_Rule_Type_CD
AND DOM8.Product_Domain_Entity = "BNCS"
AND DOM8.Product_Attribute_Type = "Cost_Share_Mbr_Family_Ind"
AND DOM8.Domain_Value = BNCS.Cost_Share_Mbr_Family_Ind
AND BNCS.Product_ID = @.Product_ID
ORDER BY
DOM1.Sort_Seq_No,
DOM1.Value_Description,
DOM2.Sort_Seq_No,
DOM2.Value_Description,
DOM3.Sort_Seq_No,
DOM3.Value_Description,
DOM4.Sort_Seq_No,
DOM4.Value_Description,
DOM5.Sort_Seq_No,
DOM5.Value_Description,
DOM6.Sort_Seq_No,
DOM6.Value_Description,
DOM7.Sort_Seq_No,
DOM7.Value_Description,
DOM8.Sort_Seq_No,
DOM8.Value_DescriptionDo you have indexes on these tables?|||

Quote:

Originally Posted by iburyak

Do you have indexes on these tables?

Yes, the tables mentioned have indexes. But i want to know is there any other way to improve the performance of this query.|||Unfortunately you didn't provide specific answer.
To help you I need to see all index compositions.
Some people think they have indexes but it could be that they are not the once that such query will use.

To view if your query uses indexes in SQL Query Analyzer go to Query Show Execution Plan.
Execute your query and point to each item and see if your indexes are actually used.|||well the performance can be boosted using the appropriate filters in the FROM clause itself. Try to use INNER and the other different appropriate joins in the clause.The default join is the CROSS join which is the worst join. So try to filter out the results as much as possible in the FROM clause. Definitely the performance will be boosted up to some instant.|||

Quote:

Originally Posted by abhishek8236

well the performance can be boosted using the appropriate filters in the FROM clause itself. Try to use INNER and the other different appropriate joins in the clause.The default join is the CROSS join which is the worst join. So try to filter out the results as much as possible in the FROM clause. Definitely the performance will be boosted up to some instant.

Thanks Everyone! The Problem has been resolved.

How to identify when the values are going on particular record

Hi,
I want to identify, when the values are modified/updated on the particular
row in a table, i am not using triggers in this table.
Please advise me, any options in SQL Profiler Trace to find out this.
rgds,
SouraSouRa
Which version of SQL Server you are using?
In SQL Server 2005 there is new OUTPUT clause to track changes
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> Hi,
> I want to identify, when the values are modified/updated on the particular
> row in a table, i am not using triggers in this table.
> Please advise me, any options in SQL Profiler Trace to find out this.
> rgds,
> Soura|||Hi,
I am using Sql Server 2000, any options in Sql 2000
rgds,
Soura
"Uri Dimant" wrote:
> SouRa
> Which version of SQL Server you are using?
> In SQL Server 2005 there is new OUTPUT clause to track changes
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> > Hi,
> >
> > I want to identify, when the values are modified/updated on the particular
> > row in a table, i am not using triggers in this table.
> >
> > Please advise me, any options in SQL Profiler Trace to find out this.
> >
> > rgds,
> > Soura
>
>