copying tables in SQL SERVER

I have a 100m row table that I need to come from one database to another database in SQL SERVER.

The bulkcopy feature in DTS is nice -- however is there a stored procedure or external software that will be able to do this outside of DTS.

Right now I am doing a
SELECT *
INTO
(table name)
FROM (table name)

and on a 100m row table it is taking around 52 hours. Not acceptable.Is it on the same server?

bcp out in native format and bcp in is probably the fastest

But the SELECT * INTO is a minimally logged operation...and if it's on the same server...

I'm not so sure bcp would beat it since it's 1 operation as compared to 2.|||Yes it is on the same server.|||For 100 million rows, I think I'd partition it up anyway...

What's the DDL of the table...is it a heap or does it have a pk?

If it's got something unique, I'd split it up in to 10 tables and thread out the SELECT Collist INTO Table1
SELECT Collist INTO Table2
SELECT Collist INTO Table3
SELECT Collist INTO Table4
ect

And run all 10 at the same time from 10 separate osql bat files...

thats 1 select per file....

How long does a backup take?|||Hello Everyone,

If you would like to copy records within the same database between the different databases the you can use this query,

insert into <table Name> select * from <Destination Table>|||insert into <table Name> select * from <Destination Table>The only problem is that this syntax is fully logged, while the SELECT INTO syntax is only minimally logged. Since the SELECT INTO appears to be too slow and I'd expect this to be even slower, I don't think it would be a good solution.

-PatP|||Damn...I wonder if that belongs here...

http://thedailywtf.com/archive/2004/09/01/1511.aspx

Copying tables each other but with new row numbers or something like that

Take T4.ProductID , T4.ProductName from T4 where T4.CartID = @.CartID
.IF EXISTS
.Insert Into T1 values(T4.ProductId) and Get the T1.RowNo (T1.RowNo autoincrement column)
.Take T5.ProductID , T5.ProductGroup where T5.ProductID = T4.ProductID
.IF EXISTS
.INSERT INTO T2 values(T1.RowNo,T5.ProductGroup)
.Take T6.ProductID,T6.ProductGroup,T6.ProductDetail Where T6.ProductID = T4.ProductName AND T6.ProductGroup = T5.ProductGroup
.IF EXISTS
.INSERT INTO T3 values (T1.RowNo,T5.ProductGroup,T6.ProductDetail)

I Hope i am clear. It is very confusing but try to thing. I really need help. I am trying to make a shopping site.
T1 stores ProductID, this table is ShoppingCart
T2 stores DetailGroup of Product stored in T1.
T3 stores Detail of Product.

T1 - T2 - T3
EX: 1,Samsung HDD - 1,External - 1,External,750GB

User can save this cart. When user saves cart, tables are copying each other. T1 to T4 , T2 to T5 , T3 to T6. So everything is ok up to this point. But when user try to add his/her saved cart to current cart problem begins.

T4 - T5 - T6
EX: 1,Samsung HDD - 1,External - 1,External,750GB

When user adds a product, it is inserted into T1 as a new row and first column(T1.RowNo) is autoincrement. I need to copy user cart values from T1 to T4 and get the inserted rows new RowNo and use it when inserteing the T5 into T2 and T6 into T3. I hopw i am clear. I try to write code but it was very long. If you need code please let me know. I REALLY NEED HELP .

AND

Another simple question. Are stored procedures takes values that comes from another stored procedure as Table. I mean SP1 returns a table. And SP2 takes that table row by row and makes the operation?

Ex: you are calling SP2 5 times for different 5 values(nvarchar(50),int). Likewise can you call SP2 one time and getting a Table whisch stores 5 nvarchar(50),int value as rows.

Happy Coding

"MasterG"

Surely your table names are NOT "T1", T2", etc.

|||Sure names are not T1 or T2 etc. But is it important? Just a table name. Why did you asked that?

Copying rows with all child rows

Hallo NG,
I have some complicated database with many relational tables and one root
table (i.e. has no FK)
What I need to do is to copy a row inside the root table then copy all child
rows and change references in them to refer to the copied row in the root
table, and then copy the child rows from child rows and make them referring
to the copied root child row . and so on until the last level.
I thought about making a strong typed DataSet for this database and then
loading the relevant rows, call MemberwiseClone on it and then update the
DataSet, anyway this way is very inefficient because I need to retrieve all
data from the sql server to the computer running .Net and then sending the
copied data back.
Does anybody know a way to do this job using just T-SQL ?
Thanks in advance
Stefan RosiI'm not sure of the details of what you're asking. but if I understand
correctly this is the direction I'd try...
INSERT RootCopy
FROM Root
WHERE Rootexpression
INSERT ChildCopy
FROM Child
JOIN Root
ON Root.PK = Child.FK
WHERE Rootexpression
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Stefan Rosi" <Stefan.Rosie_ReplaceWithAt_gmail.com> wrote in message
news:OxBkPic0EHA.824@.TK2MSFTNGP11.phx.gbl...
>
> Hallo NG,
>
> I have some complicated database with many relational tables and one root
> table (i.e. has no FK)
> What I need to do is to copy a row inside the root table then copy all
> child
> rows and change references in them to refer to the copied row in the root
> table, and then copy the child rows from child rows and make them
> referring
> to the copied root child row . and so on until the last level.
> I thought about making a strong typed DataSet for this database and then
> loading the relevant rows, call MemberwiseClone on it and then update the
> DataSet, anyway this way is very inefficient because I need to retrieve
> all
> data from the sql server to the computer running .Net and then sending the
> copied data back.
> Does anybody know a way to do this job using just T-SQL ?
>
> Thanks in advance
> Stefan Rosi
>sql

Copying rows with all child rows

Hallo NG,
I have some complicated database with many relational tables and one root
table (i.e. has no FK)
What I need to do is to copy a row inside the root table then copy all child
rows and change references in them to refer to the copied row in the root
table, and then copy the child rows from child rows and make them referring
to the copied root child row . and so on until the last level.
I thought about making a strong typed DataSet for this database and then
loading the relevant rows, call MemberwiseClone on it and then update the
DataSet, anyway this way is very inefficient because I need to retrieve all
data from the sql server to the computer running .Net and then sending the
copied data back.
Does anybody know a way to do this job using just T-SQL ?
Thanks in advance
Stefan Rosi
I'm not sure of the details of what you're asking. but if I understand
correctly this is the direction I'd try...
INSERT RootCopy
FROM Root
WHERE Rootexpression
INSERT ChildCopy
FROM Child
JOIN Root
ON Root.PK = Child.FK
WHERE Rootexpression
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Stefan Rosi" <Stefan.Rosie_ReplaceWithAt_gmail.com> wrote in message
news:OxBkPic0EHA.824@.TK2MSFTNGP11.phx.gbl...
>
> Hallo NG,
>
> I have some complicated database with many relational tables and one root
> table (i.e. has no FK)
> What I need to do is to copy a row inside the root table then copy all
> child
> rows and change references in them to refer to the copied row in the root
> table, and then copy the child rows from child rows and make them
> referring
> to the copied root child row . and so on until the last level.
> I thought about making a strong typed DataSet for this database and then
> loading the relevant rows, call MemberwiseClone on it and then update the
> DataSet, anyway this way is very inefficient because I need to retrieve
> all
> data from the sql server to the computer running .Net and then sending the
> copied data back.
> Does anybody know a way to do this job using just T-SQL ?
>
> Thanks in advance
> Stefan Rosi
>

Copying Row Data within the same table

I have the following table:

Table name: RR

columns:

Subject (varchar (35), Null)

Topic (varchar (35), Null)

RD (text, null)

RR (text, null)

Picture (varchar (50), Null)

Video (varchar (50), Null)

RRID (int, Not Null)

TSTAMP (datetime, Null)

RRCount (int, Not Null)

This table stores common information used in resolving technical problems based on Subject and Topic. However, I've now created a Subject/Topic where I want to copy all the data that corresponds to another Subject/topic.

Example:

There are 35 rows that correspond to Subject = 'Publisher01' and Topic = 'Subcategory03'. I want to create 35 new rows that contain the same RD and RR data, but have Subject = 'Publisher02' and Topic = 'Subcategory07'. Highest current RRID = 5008

I cannot figure out how to write that query. I apologize in advance for the fact that this is, no doubt, a seriously beginner question.

Hi,

would be nice to have some DDL on hand to see your additional table information and some expected results, but anyway:

if you just want to copy these rows (I don′t know what you mean by RRID ?!) the easiest insert statement is:

INSERT INTO RR
(...collist....,Subject,Topic )
SELECT
...collist...,'Publisher02','Subcategory07'
FROM RR
WHERE = 'Publisher01' and
Topic = 'Subcategory03'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Copying from one field to another in the same row

Is there a simple method within SQL to copy the contents of one field to
another field in the same row?
TGSYes,
UPDATE Sometable Set DestColumn = SourceColumn
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"The Good Son" <good-son@.freeuk.com> schrieb im Newsbeitrag
news:Xns964C68ECC509Dgoodsonfreeukcom@.13
0.133.1.4...
> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS|||UPDATE <Table>
SET column1 = column2
thanks and regards
Chandra
"The Good Son" wrote:

> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS
>|||UPDATE Table SET col1=col2
WHERE ..........
"The Good Son" <good-son@.freeuk.com> wrote in message
news:Xns964C68ECC509Dgoodsonfreeukcom@.13
0.133.1.4...
> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS|||Many thanks to all those who replied.
TGS

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?
Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:

> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?
|||> Hope that following can help you:
Thanks for the reply!!

> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?
|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:

> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?|||> Hope that following can help you:
Thanks for the reply!!

> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:
> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?|||> Hope that following can help you:
Thanks for the reply!!
> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

Copying an ntext cell from one cell into another (destination row already exists

Hi!

What I'd like to do is:

UPDATE table1
SET
A_TEXT_COLUMN = (SELECT another_text_column
FROM table2
WHERE table2_id = @.precomputed_id_1)
WHERE table1_ID = @.precomputed_id_2

Since the cells are text, this does not work. Since the cell to be updated is in an already exitant row, it's not possible to simply use insert.

I'd like to do something like (PSEUDOcode):

WRITETEXT(table1.A_TEXT_COLUMN, READTEXT(@.textptr_initialised_to_point_at_target_c ell))

But the *actual* synatx of WRITETEXT and READTEXT seem totally inappropriate for any such trick...

Any hints or pointers HUGELY appreciated... THANXIs this a standard *problem*? Or an unsolved one (just an sqlserver limitation that must be dealt with in a totally different way??)

I saw 10 people read the thread but nobody answered, maybe the problem is a generally troublesome one with no good solutions...

Any ideas at all?

Thanx,

wOAst

copying a row to new table - not copying one date column

I have two tables, a PendingOrder table and a CompletedOrder table. I have a stored procedure that moves an order from the pending table to the completed table, and I am having an issue with copying over a date field (the date the order was created). Here is what I have....

The store procedure takes in the following:
@.PendOrderKey INT,
@.Status INT

Here is my insert statement:

INSERT INTO CustOrder (CustAddrKey, UserKey, ContactName, ContactPhone, CustPONo, VendLocalSupplierKey, Notes, Status, OrderTypeKey, CreatedDate, ModifiedDate)
SELECT CustAddrKey, UserKey, ContactName, ContactPhone, CustPONo, VendLocalSupplierKey, Notes, @.Status, OrderTypeKey, CreatedDate, GETDATE()
FROM PendCustOrder
WHERE PendOrderKey = @.PendOrderKey

For some reason, when I run this on an order, instead of taking the value of "CreatedDate" from the pending order table and inserting it into the CreatedDate field of the completed order table, it inserts the current date and time into the completed order table. The "CreatedDate" field in the completed orders table does not allow nulls and has no default value, so I'm confused as to why it's inserting the current date/time. Any help would be greatly appreciated. Thanks!!

Maguidhir:

Check and see if the target table has a trigger that sets the "CreatedDate" field when a new record is inserted into the table.

Dave

|||Thank you so much! It does have a trigger that sets the CreatedDate=GetDate()!

Copy values from previous row

I'm guessing this is a fairly straight forward need, but want to make sure I am using the correct set of tasks:

In the dataflow, some values I need to carry forward from the previous row, such as a balance that I need to carry forward for the current customer record. This is similar to a running total, only I am not summing anything, but just carrying over from the previous records value (assuming dataset is sorted correctly, first by customer #, then by date).

Do I need the Dervied Column transform, and use a variable to store the previous value, or is there another transform that would be better suited?

Thanks

Kory

as far as i know, the derived column transformation cannot store a value in a variable. your problem seems to cry out for a script component or custom component solution.

i hope this helps.

|||I would do this in a T-SQL, Stored Procedure, or Execute SQL Task, avoiding a cursor. I worked on a SQL script like this once and avoided using a cursor by using an incremented identity field.|||

KoryS wrote:

I'm guessing this is a fairly straight forward need, but want to make sure I am using the correct set of tasks:

In the dataflow, some values I need to carry forward from the previous row, such as a balance that I need to carry forward for the current customer record. This is similar to a running total, only I am not summing anything, but just carrying over from the previous records value (assuming dataset is sorted correctly, first by customer #, then by date).

Do I need the Dervied Column transform, and use a variable to store the previous value, or is there another transform that would be better suited?

Thanks

Kory

I think you're going to need an asynchronous script transform in order to achieve that.

-Jamie

copy the data from one field of one row to the same field of another row

I have been puzzling over this for nearly two days now and I'm about ready to dispair.

The task seems simple:

There are a few rows in a table that looks like this:

RecipientID, Name, FreshUpload, Removed
info@.test.com,Tester, 1, NULL
info@.test.com,Tester, 0, 07/18/2002
info2@.another.com,Other2,0,NULL
info3@.another.com, Other3,0,NULL
...

After uploading a bunch of records they got inserted into this table. The new records are marked with a "1" in the field "FreshUpload". The task is to find the matching record (#2 above) with new=0 and to save that "Removed" date into the record with new=1. so my record #1 would look like this

info@.test.com,Tester, 1, 07/18/2002

The closest I got to a solution was this:

CREATE PROCEDURE [eWW].[REAddKeepNew]
(@.UserID nvarchar (50),

AS

UPDATE Recipients
SET RERemoved =
(SELECT RERemoved
FROM Recipients
WHERE (REFreshUpload = 0) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 1 AND REUserID = @.UserID)) AND (REUserID = @.UserID))
WHERE (REFreshUpload = 1) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 0 AND REUserID = @.UserID)) AND (REUserID = @.UserID)

GO

This works if the table holds only one incident where a new record matches an old record that carries the Removed Flag.

Can anyone see how to copy selectable data from one row to another?

eWWThe procedure accepts a recipientID to check - did you want it to process all of the recipients in the table at once?|||Not too clear what you want but something like this maybe

update tbl
set Removed =
(select max(Removed) from tbl t2 where t2.RecipientID = tbl.RecipientID and t2.Name = tbl.Name and t2.FreshUpload = 0)
where tbl.FreshUpLoad = 1

If there can only be one rec with FreshUpload - 0 then

update tbl
set Removed = t2.Removed
from tbl, tbl t2
where t2.RecipientID = tbl.RecipientID
and t2.Name = tbl.Name and t2.FreshUpload = 0
and tbl.FreshUpLoad = 1|||Hi,

We can use a cursor and instead of updating the "Removed" field, y cant we jus update the "FreshLoad" field with a 1 when the match is found.
Does this query help??
************************************************** ******
declare updaterec cursor
for
select * from <Tablename> where FreshUpload=1

open updaterec

fetch next from updaterec into @.varRecipientID,@.varName,@.varFreshUpload,@.varRemov ed

while @.@.fetch_status=0
begin
Update <Tablename> set FreshLoad=1 where RecipientID=@.varRecipientID
fetch next from updaterec into @.varRecipientID,@.varName,@.varFreshUpload,@.varRemov ed
end

close updaterec
deallocate updaterec
************************************************** *****
Please tell me if this helps.
Regards,
Ramya

Originally posted by eWW
I have been puzzling over this for nearly two days now and I'm about ready to dispair.

The task seems simple:

There are a few rows in a table that looks like this:

RecipientID, Name, FreshUpload, Removed
info@.test.com,Tester, 1, NULL
info@.test.com,Tester, 0, 07/18/2002
info2@.another.com,Other2,0,NULL
info3@.another.com, Other3,0,NULL
...

After uploading a bunch of records they got inserted into this table. The new records are marked with a "1" in the field "FreshUpload". The task is to find the matching record (#2 above) with new=0 and to save that "Removed" date into the record with new=1. so my record #1 would look like this

info@.test.com,Tester, 1, 07/18/2002

The closest I got to a solution was this:

CREATE PROCEDURE [eWW].[REAddKeepNew]
(@.UserID nvarchar (50),

AS

UPDATE Recipients
SET RERemoved =
(SELECT RERemoved
FROM Recipients
WHERE (REFreshUpload = 0) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 1 AND REUserID = @.UserID)) AND (REUserID = @.UserID))
WHERE (REFreshUpload = 1) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 0 AND REUserID = @.UserID)) AND (REUserID = @.UserID)

GO

This works if the table holds only one incident where a new record matches an old record that carries the Removed Flag.

Can anyone see how to copy selectable data from one row to another?

eWW|||Nigelrivett: I tried your solution first as it looked the leanest and BINGO!
That's it. You solved my riddle. Amazing how simple this can be when you know what you're doing.

Thank you very much nigelrivett!

and thank all of you who have helped.

eWW

[QUOTE][SIZE=1]Originally posted by nigelrivett
Not too clear what you want but something like this maybe

update tbl
set Removed =
(select max(Removed) from tbl t2 where t2.RecipientID = tbl.RecipientID and t2.Name = tbl.Name and t2.FreshUpload = 0)
where tbl.FreshUpLoad = 1

Copy SQL Server Row - Repost

Hello all,
I've reposted this message as I didn't make it to clear what i'm
trying to achive in an earlier post - my apologies.
I need to write a procedure that will SELECT a row in a table and
INSERT the values into the same table, however, one of the columns,
'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
it will try to INSERT a row with a key that has the same value. So, is
there a way to do this without having to specify every field in the
SELECT clause and auto incrementing the 'ID' value?
Thanks all,
JY
Jon
You WILL have to specify all columns except ID
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178711070.737514.324520@.e51g2000hsg.googlegr oups.com...
> Hello all,
> I've reposted this message as I didn't make it to clear what i'm
> trying to achive in an earlier post - my apologies.
> I need to write a procedure that will SELECT a row in a table and
> INSERT the values into the same table, however, one of the columns,
> 'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
> it will try to INSERT a row with a key that has the same value. So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
> Thanks all,
> JY
>
|||> So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
No, you'll need to specify a column list. It's a Best Practice to specify
an explicit column list, even without the IDENTITY issue. Query
Analyzer/Management Studio allow you to script SELECTs so you don't need to
type the list manually.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178711070.737514.324520@.e51g2000hsg.googlegr oups.com...
> Hello all,
> I've reposted this message as I didn't make it to clear what i'm
> trying to achive in an earlier post - my apologies.
> I need to write a procedure that will SELECT a row in a table and
> INSERT the values into the same table, however, one of the columns,
> 'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
> it will try to INSERT a row with a key that has the same value. So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
> Thanks all,
> JY
>

Copy SQL Server Row - Repost

Hello all,
I've reposted this message as I didn't make it to clear what i'm
trying to achive in an earlier post - my apologies.
I need to write a procedure that will SELECT a row in a table and
INSERT the values into the same table, however, one of the columns,
'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
it will try to INSERT a row with a key that has the same value. So, is
there a way to do this without having to specify every field in the
SELECT clause and auto incrementing the 'ID' value?
Thanks all,
JYJon
You WILL have to specify all columns except ID
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178711070.737514.324520@.e51g2000hsg.googlegroups.com...
> Hello all,
> I've reposted this message as I didn't make it to clear what i'm
> trying to achive in an earlier post - my apologies.
> I need to write a procedure that will SELECT a row in a table and
> INSERT the values into the same table, however, one of the columns,
> 'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
> it will try to INSERT a row with a key that has the same value. So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
> Thanks all,
> JY
>|||> So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
No, you'll need to specify a column list. It's a Best Practice to specify
an explicit column list, even without the IDENTITY issue. Query
Analyzer/Management Studio allow you to script SELECTs so you don't need to
type the list manually.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178711070.737514.324520@.e51g2000hsg.googlegroups.com...
> Hello all,
> I've reposted this message as I didn't make it to clear what i'm
> trying to achive in an earlier post - my apologies.
> I need to write a procedure that will SELECT a row in a table and
> INSERT the values into the same table, however, one of the columns,
> 'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
> it will try to INSERT a row with a key that has the same value. So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
> Thanks all,
> JY
>

Copy SQL Server Row - Repost

Hello all,
I've reposted this message as I didn't make it to clear what i'm
trying to achive in an earlier post - my apologies.
I need to write a procedure that will SELECT a row in a table and
INSERT the values into the same table, however, one of the columns,
'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
it will try to INSERT a row with a key that has the same value. So, is
there a way to do this without having to specify every field in the
SELECT clause and auto incrementing the 'ID' value?
Thanks all,
JYJon
You WILL have to specify all columns except ID
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178711070.737514.324520@.e51g2000hsg.googlegroups.com...
> Hello all,
> I've reposted this message as I didn't make it to clear what i'm
> trying to achive in an earlier post - my apologies.
> I need to write a procedure that will SELECT a row in a table and
> INSERT the values into the same table, however, one of the columns,
> 'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
> it will try to INSERT a row with a key that has the same value. So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
> Thanks all,
> JY
>|||> So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
No, you'll need to specify a column list. It's a Best Practice to specify
an explicit column list, even without the IDENTITY issue. Query
Analyzer/Management Studio allow you to script SELECTs so you don't need to
type the list manually.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178711070.737514.324520@.e51g2000hsg.googlegroups.com...
> Hello all,
> I've reposted this message as I didn't make it to clear what i'm
> trying to achive in an earlier post - my apologies.
> I need to write a procedure that will SELECT a row in a table and
> INSERT the values into the same table, however, one of the columns,
> 'ID' is a PK with Identity set to 'Yes'. Obviously this won't work as
> it will try to INSERT a row with a key that has the same value. So, is
> there a way to do this without having to specify every field in the
> SELECT clause and auto incrementing the 'ID' value?
> Thanks all,
> JY
>

copy row within same table

Gud day guys...i have this problem of copying records within a table...pls help me

I have tried the code below but returned an error.......
"violation of PRIMARY or UNIQUE KEY constraint "INTEG_29" on table "SERVICE" "

Insert into Service (GroupNo,ServiceCode,ServiceName,SystemNo,Upload,timestampstr)
SELECT 6,ServiceCode,ServiceName,SystemNo,Upload,TIMESTAMPSTR
FROM Service
WHERE GroupNo=1

is this possible? pls help me with the sql statement to make this work......

note: timestampstr is the primary key with a not null value ...

Short answer: if timestampstr is your primary key, you need to have different values in there. I don't know what the definition of your timestampstr or what the semantics of its values are, so I can't tell you how to generate a unique value for it for the new rows.

HTH,

Mostafa Elhemali - SQL Engine

|||Its a datatype in Firebird which holds the timestamp value wherein date and time is stored = it is equivalent to datetime datatype in other languages...

my purpose in doing this is that i wanted to copy all records in a table(service) with groupno = 1 and insert in the same table changing only the groupno field in to 6 and all the other records are the same.....any alternative solution for this if primary doesnt allow the same value for a field? thanks.....|||you can not insert the value in the TimeStamp datatype explicitly, the Server will automatically insert the value into it when you do insert operation.

Copy Row Of Data From Table to Table In Same DB

Hi.

Like the title says - how do i do this?

I was given the following example:

INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A'

The above statement threw the following error:

An explicit value for the identity column in table 'TABLE2' can only
be specified when a column list is used and IDENTITY_INSERT is ON.

Then, after filling in all the column names in my above select
statement I kept getting an error to the effect that the number of
source and destination columns don't match. This is because one column
"confirm_hash" does not exist in the destination table, just the
source table.

could somebody show me how to get this to work?

thanks!

PS - MS SQL SERVER EXPRESS 2005The syntax to use INSERT INTO is like this:

INSERT INTO TABLE2
(<column_list>)
SELECT <column_list>
FROM TABLE1
WHERE COL1 = 'A'

A few brief notes:
- the <column_listwill list your columns (like COL1, COL2, COL3, etc.)
- the <column_listmust contain the same number of columns in both clauses
(INSERT INTO and SELECT)
- if you do not specify the <column_listin the INSERT INTO clause (as you
did in your sample query), then the <column_listin SELECT must much all
columns in TABLE2
- the columns have to be of the same data type and size, being able to
implicitly convert, or explicitly converted via CAST/CONVERT
- in your case if the "confirm_hash" column does not exists in the
destination table, then you have to drop it from the column list (or alter
TABLE2 before the insert to add the column)
- you do not have to list the IDENTITY column as it will get automatically
the value based on the IDENTITY (of if you want to force a value in that
column, run before the query SET IDENTITY_INSERT TABLE2 ON)

If you post your CREATE TABLE statements for both tables, some sample data
and desired results you can get much better help.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 4, 1:54 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

The syntax to use INSERT INTO is like this:
>
INSERT INTO TABLE2
(<column_list>)
SELECT <column_list>
FROM TABLE1
WHERE COL1 = 'A'
>
A few brief notes:
- the <column_listwill list your columns (like COL1, COL2, COL3, etc.)
- the <column_listmust contain the same number of columns in both clauses
(INSERT INTO and SELECT)
- if you do not specify the <column_listin the INSERT INTO clause (as you
did in your sample query), then the <column_listin SELECT must much all
columns in TABLE2
- the columns have to be of the same data type and size, being able to
implicitly convert, or explicitly converted via CAST/CONVERT
- in your case if the "confirm_hash" column does not exists in the
destination table, then you have to drop it from the column list (or alter
TABLE2 before the insert to add the column)
- you do not have to list the IDENTITY column as it will get automatically
the value based on the IDENTITY (of if you want to force a value in that
column, run before the query SET IDENTITY_INSERT TABLE2 ON)
>
If you post your CREATE TABLE statements for both tables, some sample data
and desired results you can get much better help.
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Thanks Plamen,

I have followed your directions and that works (tested in QA).
Since the query is now getting kind of detailed, I have decided to
create a stored procedure out of this. I am getting an error:

Msg 102, Level 15, State 1, Procedure sp_MyStoredProcedure, Line 75
Incorrect syntax near ','.

Would you mind telling me why I am getting this error (and checking my
SPROC in general)? One note - I have added a final column (column18)
in my sproc that exists in Table2, but not in Table1.

Thanks, I really appreciate any feedback you can provide.

Peter

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:<Author: Last, First>
-- Create date: <Create Date: 4 June 2007>
-- Description:<Description: Table To Table Copy>
-- =============================================

CREATE PROCEDURE sp_MyStoredProcedure

@.column1 DATETIME = NULL,
@.column2 VARCHAR(50) = NULL,
@.column3 VARCHAR(50) = NULL,
@.column4 VARCHAR(50) = NULL,
@.column5 VARCHAR(50) = NULL,
@.column6 INT = NULL,
@.column7 VARCHAR(50) = NULL,
@.column8 VARCHAR(50) = NULL,
@.column9 INT = NULL,
@.column10 INT = NULL,
@.column11 INT = NULL,
@.column12 VARCHAR(50) = NULL,
@.column13 VARCHAR(50) = NULL,
@.column14 VARCHAR(50) = NULL,
@.column15 VARCHAR(50) = NULL,
@.column16 VARCHAR(50) = NULL,
@.column17 VARCHAR(50) = NULL,
@.column18 VARCHAR(50) = NULL

AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO Table1
(column1,
column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17)
SELECT column1,
column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17
FROM Table2 t2
WHERE t2.column1 = @.column1,
column2 = @.column2,
column3 = @.column3,
column4 = @.column4,
column5 = @.column5,
column6 = @.column6,
column7 = @.column7,
column8 = @.column8,
column9 = @.column9,
column10 = @.column10,
column11 = @.column11,
column12 = @.column12,
column13 = @.column13,
column14 = @.column14,
column15 = @.column15,
column16 = @.column16,
column17 = @.column17,
column18 = @.column18

END
GO|||The syntax error is because of the commas in the WHERE clause. The
conditions in the WHERE clause are logical expressions and you have to use
AND or OR between expressions based on what you need to filter. A trimmed
down example is:

INSERT INTO Table1
(column1,
column2)
SELECT column1,
column2
FROM Table2
WHERE column1 = @.column1
AND column2 = @.column2

All that said, I am a bit puzzled why you decided to write this stored
procedure and the purpose of passing those column parameters. If you just
need to copy the Table2 to Table1, then directly run the statement like
this:

INSERT INTO Table1
(column1,
column2,
-- ... the rest of the columns go here
column17)
SELECT column1,
column2,
-- ... the rest of the columns go here
column17
FROM Table2

And then if you have any filters that you need to apply to the columns from
Table2, you can add the WHERE clause. Also, you could wrap that statement in
a stored procedure, but I just do not see the purpose of passing all those
column parameters to the SP. Can you explain why you added them and how you
plan to execute the SP, and maybe an example of what parameters you pass?

If you are trying to perform something like dynamic searching (that is
filter on multiple variable conditions), then you may want to read Erland
Sommarskog's article on dynamic search conditions:
http://www.sommarskog.se/dyn-search.html
HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 4, 3:21 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

The syntax error is because of the commas in the WHERE clause. The
conditions in the WHERE clause are logical expressions and you have to use
AND or OR between expressions based on what you need to filter. A trimmed
down example is:
>
INSERT INTO Table1
(column1,
column2)
SELECT column1,
column2
FROM Table2
WHERE column1 = @.column1
AND column2 = @.column2
>
All that said, I am a bit puzzled why you decided to write this stored
procedure and the purpose of passing those column parameters. If you just
need to copy the Table2 to Table1, then directly run the statement like
this:
>
INSERT INTO Table1
(column1,
column2,
-- ... the rest of the columns go here
column17)
SELECT column1,
column2,
-- ... the rest of the columns go here
column17
FROM Table2
>
And then if you have any filters that you need to apply to the columns from
Table2, you can add the WHERE clause. Also, you could wrap that statement in
a stored procedure, but I just do not see the purpose of passing all those
column parameters to the SP. Can you explain why you added them and how you
plan to execute the SP, and maybe an example of what parameters you pass?
>
If you are trying to perform something like dynamic searching (that is
filter on multiple variable conditions), then you may want to read Erland
Sommarskog's article on dynamic search conditions:http://www.sommarskog.se/dyn-search.html
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Hi Plamen,

Thanks - you have been a ton of help.

OK, the situation is that I have a page that is a "click-back" from
a registration page. The user finds the code in his inbox and pastes
it in his http:// box for registration confirmation - you know the
deal.
Once that happens, the code I have been writing moves the data
the user input for registration from a temp table to the official
registered
users table. This is what we have been discussing in this thread.
So, all the values are registration values (reg date, firstName,
lastName,
city, state, zip code, security question, security answer, etc). There
is no identity column in common because the userID identity column
in the destination table will automatically increment upon insertion.

As for the SPROC decision, I decided to use a SPROC because of the
the size of the SQL statement - i thought it was a bit lengthly and
involved
so, i figured it turn it into a SPROC. I am guessing this is a poor
reason
to create a SPROC... maybe you could tell me when is the best time to
use them? I am kind of learning as I go.

Anyway, below is the original statement (inside the SqlConnection
statement). It works when I run it in SQL Express. Let me know if you
think it is more forgiving to do it this way.

Thanks again for your help.

MyConn As New
SqlConnection(ConfigurationManager.ConnectionStrin gs("myConnStr").ConnectionString)
Dim MyCmd As New SqlCommand("INSERT INTO Users (regdate, pass, role,
squestion, sanswer, zcode, altemail, email, bdaymonth, bdayday,
bdayyear, gender, sitename, city, state, country, lastName, firstName)
SELECT regdate, pass, role, squestion, sanswer, zcode, altemail,
email, bdaymonth, bdayday, bdayyear, gender, sitename, city, state,
country, lastName, firstName FROM TempRegistration t WHERE t.confirm
= '8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077'", MyConn)|||Ok, now it is more clear what you are trying to do... :)

Yes, stored procedure is best here, as it can reuse previously cached
execution plan. Perhaps something like this:

CREATE PROCEDURE ConfirmUserRegistration
@.confirmation_cd NVARCHAR(50)
AS

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRAN

INSERT INTO Users
(egdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName)
SELECT regdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName
FROM TempRegistration
WHERE confirm = @.confirmation_cd;

COMMIT TRAN;

END TRY
BEGIN CATCH

IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRAN;
END
ELSE IF (XACT_STATE()) = 1
BEGIN
COMMIT TRAN;
END

DECLARE
@.ErrorMessage NVARCHAR(4000),
@.ErrorSeverity INT,
@.ErrorState INT,
@.ErrorNumber INT,
@.ErrorLine INT,
@.ErrorProcedure NVARCHAR(200),
@.ErrMessage NVARCHAR(4000);

SELECT
@.ErrorMessage = ERROR_MESSAGE(),
@.ErrorSeverity = ERROR_SEVERITY(),
@.ErrorState = ERROR_STATE(),
@.ErrorNumber = ERROR_NUMBER(),
@.ErrorLine = ERROR_LINE(),
@.ErrorProcedure = COALESCE(ERROR_PROCEDURE(), '-');

SET @.ErrMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+
N'Message: '+ ERROR_MESSAGE();

RAISERROR(
@.ErrMessage,
@.ErrorSeverity,
1,
@.ErrorNumber,
@.ErrorSeverity,
@.ErrorState,
@.ErrorProcedure,
@.ErrorLine
);

END CATCH;

GO

Then in your code call the SP like this (I just typed here, please check for
syntax, I've been using more C# lately and could be missing something):

Using connection As New
SqlConnection(ConfigurationManager.ConnectionStrin gs("myConnStr").ConnectionString)

Try
Dim command As SqlCommand = New SqlCommand( _
"ConfirmUserRegistration",
connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter = command.Parameters.Add( _
"@.confirmation_cd ",
SqlDbType.NVarChar, _
50)
parameter.Value = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077"

command.Connection.Open()
command.ExecuteNonQuery()

Catch exSQL As SqlException
' Log and show error

Catch exGen As Exception
' Log and show error

End Try

End Using

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 4, 8:15 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Ok, now it is more clear what you are trying to do... :)
>
Yes, stored procedure is best here, as it can reuse previously cached
execution plan. Perhaps something like this:
>
CREATE PROCEDURE ConfirmUserRegistration
@.confirmation_cd NVARCHAR(50)
AS
>
SET NOCOUNT ON;
>
BEGIN TRY
>
BEGIN TRAN
>
INSERT INTO Users
(egdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName)
SELECT regdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName
FROM TempRegistration
WHERE confirm = @.confirmation_cd;
>
COMMIT TRAN;
>
END TRY
BEGIN CATCH
>
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRAN;
END
ELSE IF (XACT_STATE()) = 1
BEGIN
COMMIT TRAN;
END
>
DECLARE
@.ErrorMessage NVARCHAR(4000),
@.ErrorSeverity INT,
@.ErrorState INT,
@.ErrorNumber INT,
@.ErrorLine INT,
@.ErrorProcedure NVARCHAR(200),
@.ErrMessage NVARCHAR(4000);
>
SELECT
@.ErrorMessage = ERROR_MESSAGE(),
@.ErrorSeverity = ERROR_SEVERITY(),
@.ErrorState = ERROR_STATE(),
@.ErrorNumber = ERROR_NUMBER(),
@.ErrorLine = ERROR_LINE(),
@.ErrorProcedure = COALESCE(ERROR_PROCEDURE(), '-');
>
SET @.ErrMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+
N'Message: '+ ERROR_MESSAGE();
>
RAISERROR(
@.ErrMessage,
@.ErrorSeverity,
1,
@.ErrorNumber,
@.ErrorSeverity,
@.ErrorState,
@.ErrorProcedure,
@.ErrorLine
);
>
END CATCH;
>
GO
>
Then in your code call the SP like this (I just typed here, please check for
syntax, I've been using more C# lately and could be missing something):
>
Using connection As New
SqlConnection(ConfigurationManager.ConnectionStrin gs("myConnStr").ConnectionString)
>
Try
Dim command As SqlCommand = New SqlCommand( _
"ConfirmUserRegistration",
connection)
command.CommandType = CommandType.StoredProcedure
>
Dim parameter As SqlParameter = command.Parameters.Add( _
"@.confirmation_cd ",
SqlDbType.NVarChar, _
50)
parameter.Value = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077"
>
command.Connection.Open()
command.ExecuteNonQuery()
>
Catch exSQL As SqlException
' Log and show error
>
Catch exGen As Exception
' Log and show error
>
End Try
>
End Using
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Thanks again Plamen. This thread has been very helpful.

I have a final question. How do I handle cases where the confirmation
code
doesn't exist? Say a user is trying to guess a code - How would the
stored procedure catch a mismatch and return the result to VB.NET so
the appropriate message can be sent to the user?

Thanks again for all your help.
Peter|||On Jun 5, 7:17 am, pbd22 <dush...@.gmail.comwrote:

Quote:

Originally Posted by

On Jun 4, 8:15 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:
>
>
>

Quote:

Originally Posted by

Ok, now it is more clear what you are trying to do... :)

>

Quote:

Originally Posted by

Yes, stored procedure is best here, as it can reuse previously cached
execution plan. Perhaps something like this:

>

Quote:

Originally Posted by

CREATE PROCEDURE ConfirmUserRegistration
@.confirmation_cd NVARCHAR(50)
AS

>

Quote:

Originally Posted by

SET NOCOUNT ON;

>

Quote:

Originally Posted by

BEGIN TRY

>

Quote:

Originally Posted by

BEGIN TRAN

>

Quote:

Originally Posted by

INSERT INTO Users
(egdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName)
SELECT regdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName
FROM TempRegistration
WHERE confirm = @.confirmation_cd;

>

Quote:

Originally Posted by

COMMIT TRAN;

>

Quote:

Originally Posted by

END TRY
BEGIN CATCH

>

Quote:

Originally Posted by

IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRAN;
END
ELSE IF (XACT_STATE()) = 1
BEGIN
COMMIT TRAN;
END

>

Quote:

Originally Posted by

DECLARE
@.ErrorMessage NVARCHAR(4000),
@.ErrorSeverity INT,
@.ErrorState INT,
@.ErrorNumber INT,
@.ErrorLine INT,
@.ErrorProcedure NVARCHAR(200),
@.ErrMessage NVARCHAR(4000);

>

Quote:

Originally Posted by

SELECT
@.ErrorMessage = ERROR_MESSAGE(),
@.ErrorSeverity = ERROR_SEVERITY(),
@.ErrorState = ERROR_STATE(),
@.ErrorNumber = ERROR_NUMBER(),
@.ErrorLine = ERROR_LINE(),
@.ErrorProcedure = COALESCE(ERROR_PROCEDURE(), '-');

>

Quote:

Originally Posted by

SET @.ErrMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+
N'Message: '+ ERROR_MESSAGE();

>

Quote:

Originally Posted by

RAISERROR(
@.ErrMessage,
@.ErrorSeverity,
1,
@.ErrorNumber,
@.ErrorSeverity,
@.ErrorState,
@.ErrorProcedure,
@.ErrorLine
);

>

Quote:

Originally Posted by

END CATCH;

>

Quote:

Originally Posted by

GO

>

Quote:

Originally Posted by

Then in your code call the SP like this (I just typed here, please check for
syntax, I've been using more C# lately and could be missing something):

>

Quote:

Originally Posted by

Using connection As New
SqlConnection(ConfigurationManager.ConnectionStrin gs("myConnStr").ConnectionString)

>

Quote:

Originally Posted by

Try
Dim command As SqlCommand = New SqlCommand( _
"ConfirmUserRegistration",
connection)
command.CommandType = CommandType.StoredProcedure

>

Quote:

Originally Posted by

Dim parameter As SqlParameter = command.Parameters.Add( _
"@.confirmation_cd ",
SqlDbType.NVarChar, _
50)
parameter.Value = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077"

>

Quote:

Originally Posted by

command.Connection.Open()
command.ExecuteNonQuery()

>

Quote:

Originally Posted by

Catch exSQL As SqlException
' Log and show error

>

Quote:

Originally Posted by

Catch exGen As Exception
' Log and show error

>

Quote:

Originally Posted by

End Try

>

Quote:

Originally Posted by

End Using

>

Quote:

Originally Posted by

HTH,

>

Quote:

Originally Posted by

Plamen Ratchevhttp://www.SQLStudio.com

>
Thanks again Plamen. This thread has been very helpful.
>
I have a final question. How do I handle cases where the confirmation
code
doesn't exist? Say a user is trying to guess a code - How would the
stored procedure catch a mismatch and return the result to VB.NET so
the appropriate message can be sent to the user?
>
Thanks again for all your help.
Peter

Actually, I have a bit of an addition to the above "final
question" :) .
I am also wondering where in the SPROC that you have provided
I could place a confirmation that the insert statement has happened
successfully? Or, how do I include a check within the SPROC to
verify successful insertion? I ask because, once the data has been
successfully moved from the Temp table to the Users table, I will need
to delete the source row in the Temp table. I can figure out how to
code the deletion but am not quite sure how the "onSuccess" statement
looks that indicates that it is OK to go ahead and delete the row.

Thanks again!|||I will try to sketch here the answer to both questions:

1). To detect that the confirmation code exists, you can check the number of
rows affected by the insert (using @.@.rowcount), and then return that value
to the client using an output parameter. If the number of rows is 1 (I
assume you have either a primary key or UNIQUE constraint on the
confirmation code column so duplicates are not possible), then you know you
had a code match, if 0 then there was no match. Here is an abbreviated code
of the SP:

CREATE PROCEDURE ConfirmUserRegistration
@.confirmation_cd NVARCHAR(50),
@.numrows INT OUTPUT
AS
-- ...
BEGIN TRAN

INSERT INTO Users
(egdate,
pass,
-- ...
firstName)
SELECT regdate,
pass,
-- ...
firstName
FROM TempRegistration
WHERE confirm = @.confirmation_cd;

SET @.numrows = @.@.rowcount;

DELETE FROM TempRegistration
WHERE confirm = @.confirmation_cd;

COMMIT TRAN;

Note that you can directly perform the DELETE without checking the result of
the INSERT, because if there is no match then there will be no rows deleted.
If you want you can have an IF @.numrows 0 before executing the DELETE
statement to run it only when there is a match.

2). On your client side, you have to define the output parameter and then
check the results, abbreviated code here:

'... connection, command and first parameter initialization go here
' now add the output parameter
parameter = command.Parameters.Add( _
"@.numrows",
SqlDbType.Int)
parameter.Direction = ParameterDirection.Output

'... open the connection and execute command go here
' retrieve the output value
If (command.Parameters("@.numrows").Value = 1) Then
' we have a match and confirmation is complete
Else
' confirmation code is invalid - show alert
End If

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 5, 8:36 am, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

I will try to sketch here the answer to both questions:
>
1). To detect that the confirmation code exists, you can check the number of
rows affected by the insert (using @.@.rowcount), and then return that value
to the client using an output parameter. If the number of rows is 1 (I
assume you have either a primary key or UNIQUE constraint on the
confirmation code column so duplicates are not possible), then you know you
had a code match, if 0 then there was no match. Here is an abbreviated code
of the SP:
>
CREATE PROCEDURE ConfirmUserRegistration
@.confirmation_cd NVARCHAR(50),
@.numrows INT OUTPUT
AS
-- ...
BEGIN TRAN
>
INSERT INTO Users
(egdate,
pass,
-- ...
firstName)
SELECT regdate,
pass,
-- ...
firstName
FROM TempRegistration
WHERE confirm = @.confirmation_cd;
>
SET @.numrows = @.@.rowcount;
>
DELETE FROM TempRegistration
WHERE confirm = @.confirmation_cd;
>
COMMIT TRAN;
>
Note that you can directly perform the DELETE without checking the result of
the INSERT, because if there is no match then there will be no rows deleted.
If you want you can have an IF @.numrows 0 before executing the DELETE
statement to run it only when there is a match.
>
2). On your client side, you have to define the output parameter and then
check the results, abbreviated code here:
>
'... connection, command and first parameter initialization go here
' now add the output parameter
parameter = command.Parameters.Add( _
"@.numrows",
SqlDbType.Int)
parameter.Direction = ParameterDirection.Output
>
'... open the connection and execute command go here
' retrieve the output value
If (command.Parameters("@.numrows").Value = 1) Then
' we have a match and confirmation is complete
Else
' confirmation code is invalid - show alert
End If
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Thanks a ton Plamen,

This thread was immensely helpful. I really appreciate it.
As a final note, for anybody that is using this thread for their own
registration system, you need to comment out NOCOUNT ON to
get the appropriate response from the SPROC (at least, I think
that is what solved my "no response" problem).

Thanks again Plamen!|||>Like the title says - how do i do this? .. An explicit value for the identity column in table 'TABLE2' can only be specified when a column list is used and IDENTITY_INSERT is ON. <<

Let us go back to RDBMS basics. A row models a complete fact ("John
bought a squid on 2007-05-12"). This fact should appear in one
table, one time and in one way in the entire schema. This is the
foundation of normalization.

In the old days, with punch cards, paper files, etc. we would
physically move these physical records from one physical location to
another physical location. We had redundancy and we want to get rid
of it. You want to increase it.

You should never use IDENTITY in an RDBMS; you want to have a
relational key. A key has nothing to do with the physical location of
the data in the hardware; it is based on the nature of the data being
modeled.

You entire approach is that of someone managing a 1950's paper file
system. Please read a book before you code again. If you want
stinking dirty kludges, then you can get them in Newsgroup; but being
a good RDBMS programmer will take YEARS of hard work. Be better than
that.

Copy row in trigger instead of update?

I think this is a tough one, at least with my limited knowlege. Here
is what I am trying to do.
Background:
I want to create an audit trail for edits made to a database. I want
to do this by catching the update in a SQL trigger and turning it into
an insert with a time stamp and cancel the update.
So any row you change really does not change, it is copied to a new
row in the same table with a time stamp.
Copying the row is normally easy as in:
Insert into mytable select * from mytable where id= '17'
This will not work though, The problem with this is that id is an
Identity field and you cannot specify the value of an identity. So the
first trick is to basically do a select * (except id). I figured this
out here is the code:
_________________________________________________________________
declare @.tb sysname, @.col sysname, @.identid sysname
select @.tb='testupdate',
@.col='id'
declare @.sql varchar(2000)
select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
from information_schema.columns
where column_name!=@.col
and table_name=@.tb
select @.sql='insert into ' + @.tb + ' ' +
substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where
id=17'
print @.sql
exec(@.sql)
The problem with this is that it copies every row in the table. I
need to copy just the changed row. The above code does not include the
timestamp part here is that part, and separate section of code that
will be in the same trigger.
UPDATE testupdate
SET testupdate.updatedate = GETDATE()
FROM testupdate INNER JOIN Inserted ON testupdate.id = Inserted.id
This little bit of code effectively creates a time stamp on every
edited row and obviously get the id of the changed row. Now if I could
capture that id and use it in the first bit of code I think I would
have it.
I tried to do something like this
CREATE TRIGGER testtrig
ON testupdate
FOR UPDATE
AS
declare @.tb sysname, @.col sysname, @.myid sysname
select @.tb='testupdate',
@.col='id',
@.myid = @.@.identity
declare @.sql varchar(2000)
select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
from information_schema.columns
where column_name!=@.col
and table_name=@.tb
select @.sql='insert into ' + @.tb + ' ' +
substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where id='
+ @.myid
print @.sql
exec(@.sql)
UPDATE testupdate
SET testupdate.updatedate = GETDATE()
FROM testupdate INNER JOIN Inserted ON testupdate.id = Inserted.idThere are several things you might consider.
If you can, add a column to the table with a default value of getdate, so
all inserts are covered.
Then read about instead of triggers... They are new to SQL 2000, the normal
trigger fires AFTER the action, which is causing you a problem. An instead
of trigger fires INSTEAD OF the update ( for instance). You still get the
inserted ,deleted tables. The ACTUAL update only occurs IF you include an
update in your trigger... you could write something like
create trigger mytrig on mytable instead of update
as
insert into mytable select * from inserted
The original update would NOT occur, and instead you would have inserted the
NEW value of the row. The NEW row would have a new ID of course... you'd
have to mess with that...perhaps have a second integer column which
contains the original ID..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"OrlandoRocks" <parkJunkie@.orlandorocks.com> wrote in message
news:55879c92.0409120419.8418a63@.posting.google.com...
> I think this is a tough one, at least with my limited knowlege. Here
> is what I am trying to do.
> Background:
> I want to create an audit trail for edits made to a database. I want
> to do this by catching the update in a SQL trigger and turning it into
> an insert with a time stamp and cancel the update.
> So any row you change really does not change, it is copied to a new
> row in the same table with a time stamp.
> Copying the row is normally easy as in:
> Insert into mytable select * from mytable where id= '17'
> This will not work though, The problem with this is that id is an
> Identity field and you cannot specify the value of an identity. So the
> first trick is to basically do a select * (except id). I figured this
> out here is the code:
> _________________________________________________________________
> declare @.tb sysname, @.col sysname, @.identid sysname
> select @.tb='testupdate',
> @.col='id'
> declare @.sql varchar(2000)
> select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
> from information_schema.columns
> where column_name!=@.col
> and table_name=@.tb
> select @.sql='insert into ' + @.tb + ' ' +
> substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where
> id=17'
> print @.sql
> exec(@.sql)
>
> The problem with this is that it copies every row in the table. I
> need to copy just the changed row. The above code does not include the
> timestamp part here is that part, and separate section of code that
> will be in the same trigger.
> UPDATE testupdate
> SET testupdate.updatedate = GETDATE()
> FROM testupdate INNER JOIN Inserted ON testupdate.id => Inserted.id
>
> This little bit of code effectively creates a time stamp on every
> edited row and obviously get the id of the changed row. Now if I could
> capture that id and use it in the first bit of code I think I would
> have it.
> I tried to do something like this
>
> CREATE TRIGGER testtrig
> ON testupdate
> FOR UPDATE
> AS
> declare @.tb sysname, @.col sysname, @.myid sysname
> select @.tb='testupdate',
> @.col='id',
> @.myid = @.@.identity
> declare @.sql varchar(2000)
> select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
> from information_schema.columns
> where column_name!=@.col
> and table_name=@.tb
> select @.sql='insert into ' + @.tb + ' ' +
> substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where id='
> + @.myid
> print @.sql
> exec(@.sql)
> UPDATE testupdate
> SET testupdate.updatedate = GETDATE()
> FROM testupdate INNER JOIN Inserted ON testupdate.id => Inserted.id