Question about INSERT

Hi,

I have a table with columns X, Y, and Z. X is the primary key and is an integer that is set to auto-increment whenever a new row is added. Y and Z are pieces of information which are not necessarily unique to any given row, so in order to get the row I just added, I really need to know X.

So when I insert a new row, I would like to somehow have X returned to me, so that I can then use it as a pointer since in my application a row that is just added must often be modified very shortly afterwards.

Is there a call which will do this? Right now I am using:

sqlAdapter.InsertCommand.ExecuteNonQuery();

Which just returns the number of the rows affected, not the actual row, or its primary key.

Thanks,

Hi,

Here is a good example that shows you how to do it: http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Try using ExecuteScalar() instead of ExecuteNonQuery() with an appropriate Insert Command String illustrated below. The code just illustrates the basic need as per the scenario in the Question and may not be fully functional.

int newX = 0;
string strInsertCommandText = "INSERT INTO MyTable (Y, Z) VALUES (@.Y, @.Z); SELECT CAST(scope_identity() AS int)";
sqlAdapter.InsertCommand.CommandType = CommandType.Text;
sqlAdapter.InsertCommand.CommandText = strInsertCommandText;

//Set the Parameter Values here.
sqlAdapter.InsertCommand.Parameters.Add("@.Y", SqlDbType.VarChar);
sqlAdapter.InsertCommand.Paramaters["@.Y"].Value = "Y Value";
sqlAdapter.InsertCommand.Parameters.Add("@.Z", SqlDbType.VarChar);
sqlAdapter.InsertCommand.Paramaters["@.Z"].Value = "Z Value";

//This line inserts a new record into the table and returns the generated primary key for the new record.
newX = (Int32)sqlAdapter.InsertCommand.ExecuteScalar();

Question about IDENTITY columns.

We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
This table is present in 2 databases Db1 and Db2. We want to keep them
both in sync daily. The mapping of Name -> ID should be the same in
both the tables. We allow only Db1 tables to be updated. So we would
like to have a program that daily truncates [Db2].[TAB1] and creates
and executes a DTS package to transfer the table from DB1 to DB2.
My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
will get copied to DB2. If we delete the row with ID 3, will the next
syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
the table as 1,2,4,5?
Thanks
YashIf you create the field in table 2 as an identity field you will have
1,2,3,4 rather than 1,2,4,5 when trucating and re-populating. You could use
a table trigger to keep both tables in sync.
groutme in SO Cal
groutme_alternate@.sbcglobal.net
<yashgt@.yahoo.com> wrote in message
news:1109006625.544445.159200@.c13g2000cwb.googlegroups.com...
> We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
> This table is present in 2 databases Db1 and Db2. We want to keep them
> both in sync daily. The mapping of Name -> ID should be the same in
> both the tables. We allow only Db1 tables to be updated. So we would
> like to have a program that daily truncates [Db2].[TAB1] and creates
> and executes a DTS package to transfer the table from DB1 to DB2.
> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
> Thanks
> Yash
>|||> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
It will copy 1, 2, 4,5. But you have to set the property "Enable Identity
insert" in the options tab of the "Transform Data Task" properties, in order
to allow explicit values to be inserted into the identity column.
AMB
"yashgt@.yahoo.com" wrote:

> We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
> This table is present in 2 databases Db1 and Db2. We want to keep them
> both in sync daily. The mapping of Name -> ID should be the same in
> both the tables. We allow only Db1 tables to be updated. So we would
> like to have a program that daily truncates [Db2].[TAB1] and creates
> and executes a DTS package to transfer the table from DB1 to DB2.
> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
> Thanks
> Yash
>|||>> If we delete the row with ID 3, will the next syncing execution cause the
There is an option in DTS which allow identity inserts under the
transformation section. When this is off the values are inserted without
generting new ones. Otherwise the new values will be generated in the
sequence based on the seed and increment set on the identity column for the
table.
Anith|||If the data in DB2 gets replaced every day then what's the point of giving i
t
an IDENTITY column? Make it a regular numeric column so that you have full
control over what values go in there.
David Portas
SQL Server MVP
--

Question about formatting a date

I have a date string that is an integer 20040119 and I converted it to a varchar and then to date format-CONVERT(datetime, CAST(my_date AS varchar(8))) AS my_date
My result is 2003-12-31 00:00:00.000. How can I now format this string so I don't get the time string at the end?
ThanksYou already did.|||Maybe I didn't explain good enough. I want to drop all the zeros at the end and just show the date and not the time.
Thanks|||Something like this:

select RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar(8))),103),10) AS my_date|||select convert(char(10), convert(datetime, '20040119'), 101)|||Thanks alot Avatar. You left off a right parenthesis, but that was the least of my worries. It works just like I wanted. Thanks again.|||Avatar,
I don't suppose you could explain real quick how that works?
Thanks.|||Im glad its work for you..

Here is my explanation:

1) CAST(my_date AS varchar(8)):
Convert my_date from DATETIME to VARCHAR(8) because I'm asuming that the COLUMN is DATETIME.
2) CONVERT(datetime, CAST(my_date AS varchar(8))),103):
Transform the VARCHAR(8) from Step 1 to a DATETIME with a format 'dd/mm/yyyy' (103), to convert a datetime column.
3) CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103) :
Now transform the DATETIME from Step 2 to a VARCHAR (once again) because the function RIGHT only works with VARCHARS.
4)RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103),10):
Finally the RIGHT functions takes the first 10 characters of the string.

I hope I explain well. ;)|||I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!|||Originally posted by exdter
I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!

Oppss..!
Now I'm looking better.. the RIGHT function is not necessary.
The finaly version of the QUERY is like this:

SELECT (CONVERT(char(10),CONVERT(datetime, CAST(getdate() as varchar(12)) ),103))

Because the last CONVERT function transform the datetime to a char(10)... without the use of RIGHT truncate the '2002-01-19 00:00:00.000' date to a '19/01/2004'.

Ahh.. by the way:
The LEFT or RIGHT function cuts a string by the LEFT or RIGHT respectabely... In this case RIGHT('19/01/2004',10) is no use :P

What a mess.. :D|||Thanks a whole lot!!!|||...In other words, this should have worked to begin with:

select convert(char(10), convert(datetime, '20040119'), 101)

The only difference is that your data is coming (as you said) in INTEGER data type, the the function above should have looked like this:

select convert(char(10), convert(datetime, cast(20040119 as char(8))), 101)

101 - MM/DD/YYYY
103 - DD/MM/YYYY

Take your pick!|||Yeah, gotta wonder why you don't just use rdjabarov's simple conversion. Shorter is better in programming.

question about delete statement

Hi,My question is :
CREATE TABLE t1
(c1 INTEGER,
c2 INTEGER,
c3 DECIMAL(15,0 ))
INSERT INTO t1 VALUES (1, 2, 3.0)
How can i define a SQL commend will cause C1 to be decremented each time a row is deleted from the T2 table?
Thanks !Can you give more details on what you are trying to do? I suspect that there are better ways to accomplish whatever you need to do.

To answer your question as it was posted, you could create a trigger on the T2 table that updated the T1 table appropriately. I'm a little vague on the implementation details because it seems that you are looking for more than a simple counter, and you haven't provided a lot of details on what you want to happen.

-PatP|||CREATE TRIGGER myTriggerForDelete
ON T2
AFTER DELETE
AS
UPDATE T1
SET C1=COALESCE((SELECT MIN(C1) FROM T1),0)-1|||It should be something like my previous post.

But as Pat said, I don't think this makes senses, maybe you should give us more explanations.

Hope it can helps you|||please go to books online and read the article on CREATE TRIGGER and pay special attention to the section on the inserted and deleted tables.|||Are you trying to prevent "holes" in a meaningless "key"|||Are you trying to prevent "holes" in a meaningless "key"

If it's the case you should read that:
http://guelphdad.wefixtech.co.uk/sqlhelp/gaps.shtml