Copying Text Field

I am refreshing records on a target table from a similar source table (two
databases, same server) and having problems with the Text fields. I am usin
g
INSERTas follows:
delete from Trackpad4..Archived
set identity_insert Trackpad4..Archived on
insert into Trackpad4..Archived
(... DEL_RECIP_NAME, DEL_RECIP_LEN, DEL_RECIP_SIG, ...)
select ... DEL_RECIP_NAME, DEL_RECIP_LEN, null, ...
from Trackpad..Archived
set identity_insert Trackpad4..Archived off
The problem we noticed (and why I've got null populating in DEL_RECIP_SIG)
is that a simple SELECT would return three rows but a rowcount of four. A
modification of the query picked up all the rows, but the DEL_RECIP_SIG fiel
d
was blank and none of the following fields displayed. Using null at least
allows the SELECTs to perform as expected and the later columns to be handle
d
properly.
My thought was to use a UPDATETEXT wrapped in a cursor to finish the job.
But the test displayed below, while it did populate the target text field,
again rendered the later columns apparently non-existent. Also, the
gobbledygook (some sort of encoded representation of a person's signature) i
n
the source and target DEL_RECIP_SIG fields don't match.
DECLARE @.ptrval_source varbinary(16)
, @.ptrval_target varbinary(16)
, @.Length_Source int
, @.Length_Target int
SELECT @.ptrval_source = TEXTPTR(DEL_RECIP_SIG)
, @.Length_Source = DEL_RECIP_LEN
FROM trackpad..old_data
where PKG_NUM = 'W44287093174'
print 'Source'
print @.ptrval_source
print @.Length_Source
/*
update Trackpad4..Old_Data
set DEL_RECIP_SIG = 'placeholder'
where PKG_NUM = 'W44287093174'
*/
select @.ptrval_target = TEXTPTR(DEL_RECIP_SIG)
, @.Length_Target = len(cast(DEL_RECIP_SIG as varchar(8000)))
FROM trackpad4..old_data
where PKG_NUM = 'W44287093174'
print 'Target'
print @.ptrval_target
print @.Length_Target
print 'source'
readtext trackpad..old_data.DEL_RECIP_SIG @.ptrval_source 0 @.Length_Source
print 'target'
readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
print 'update'
UPDATETEXT trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0
@.Length_Target trackpad..old_data.DEL_RECIP_SIG @.ptrval_source
print 'target'
readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
I tried copying the source table over through DTS, but target text fields
were empty. I was able to restore from a backup. All I can think is that
I'm not properly delineating the field going into the target record and its
violating SQL's internal rules for terminating fields and records.
Thanks for any insight,
KevinHi Kevin
You don't say what SQL Server versions you are using?
This was a problem with DTS!
http://support.microsoft.com/defaul...kb;en-us;257425
If you set the value for specific DEL_RECIP_SIG to NULL you will find out
the one that is causing this issue.
John
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:08CADCB0-8B74-4987-B78F-7F375C1BB7C8@.microsoft.com...
>I am refreshing records on a target table from a similar source table (two
> databases, same server) and having problems with the Text fields. I am
> using
> INSERTas follows:
>
> delete from Trackpad4..Archived
> set identity_insert Trackpad4..Archived on
> insert into Trackpad4..Archived
> (... DEL_RECIP_NAME, DEL_RECIP_LEN, DEL_RECIP_SIG, ...)
> select ... DEL_RECIP_NAME, DEL_RECIP_LEN, null, ...
> from Trackpad..Archived
> set identity_insert Trackpad4..Archived off
>
> The problem we noticed (and why I've got null populating in DEL_RECIP_SIG)
> is that a simple SELECT would return three rows but a rowcount of four. A
> modification of the query picked up all the rows, but the DEL_RECIP_SIG
> field
> was blank and none of the following fields displayed. Using null at least
> allows the SELECTs to perform as expected and the later columns to be
> handled
> properly.
> My thought was to use a UPDATETEXT wrapped in a cursor to finish the job.
> But the test displayed below, while it did populate the target text field,
> again rendered the later columns apparently non-existent. Also, the
> gobbledygook (some sort of encoded representation of a person's signature)
> in
> the source and target DEL_RECIP_SIG fields don't match.
>
> DECLARE @.ptrval_source varbinary(16)
> , @.ptrval_target varbinary(16)
> , @.Length_Source int
> , @.Length_Target int
> SELECT @.ptrval_source = TEXTPTR(DEL_RECIP_SIG)
> , @.Length_Source = DEL_RECIP_LEN
> FROM trackpad..old_data
> where PKG_NUM = 'W44287093174'
> print 'Source'
> print @.ptrval_source
> print @.Length_Source
> /*
> update Trackpad4..Old_Data
> set DEL_RECIP_SIG = 'placeholder'
> where PKG_NUM = 'W44287093174'
> */
> select @.ptrval_target = TEXTPTR(DEL_RECIP_SIG)
> , @.Length_Target = len(cast(DEL_RECIP_SIG as varchar(8000)))
> FROM trackpad4..old_data
> where PKG_NUM = 'W44287093174'
> print 'Target'
> print @.ptrval_target
> print @.Length_Target
> print 'source'
> readtext trackpad..old_data.DEL_RECIP_SIG @.ptrval_source 0 @.Length_Source
> print 'target'
> readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
> print 'update'
> UPDATETEXT trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0
> @.Length_Target trackpad..old_data.DEL_RECIP_SIG @.ptrval_source
> print 'target'
> readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
>
> I tried copying the source table over through DTS, but target text fields
> were empty. I was able to restore from a backup. All I can think is that
> I'm not properly delineating the field going into the target record and
> its
> violating SQL's internal rules for terminating fields and records.
> Thanks for any insight,
> Kevin|||Hi, John,
Thanks for the reply. I'm using SQL 2000, SP3. I've tried this operation
in TSQL and DTS - same results. The UpdateText operation also didn't work -
seemingly the stored text data wasn't terminated properly, and following
fields in the record were not rendered at all. In any event, the target tex
t
did not resemble the source text.
If you can think of anything else, thanks in advance.
- Kevin
"John Bell" wrote:

> Hi Kevin
> You don't say what SQL Server versions you are using?
> This was a problem with DTS!
> http://support.microsoft.com/defaul...kb;en-us;257425
> If you set the value for specific DEL_RECIP_SIG to NULL you will find out
> the one that is causing this issue.
> John
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:08CADCB0-8B74-4987-B78F-7F375C1BB7C8@.microsoft.com...
>
>

Copying Records into a different table using triggers

I am in the process of writing a trigger that will insert a new record into
another table when a record is added to a specific table. I was wondering if
there is a way to just 'copy' the entire row without using INSERT INTO
...and VALUES?
adv-thanks-anceYou could use
INSERT INTO [AnotherTable]
SELECT * FROM inserted
assuming that [AnotherTable] has the same number of columns, in the same
order, with the same datatypes as the table that has the trigger on it.
"CSHARPITPRO" wrote:

> I am in the process of writing a trigger that will insert a new record int
o
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Thanks Mark,
Is this being copied from the 'INSERTED' table in SQL?
"Mark Williams" wrote:
> You could use
> INSERT INTO [AnotherTable]
> SELECT * FROM inserted
> assuming that [AnotherTable] has the same number of columns, in the same
> order, with the same datatypes as the table that has the trigger on it.
> --
> "CSHARPITPRO" wrote:
>|||I am not sure if there is a way to do this (I have not seen one) but I would
avoid this like the plague.
The tables are identical today, but who knows what will change tomorrow.
Something as simple as adding and audit or archive date to either table
would break any app using such an approach. Structures have a tendency to
evolve over time, and any code that assumes two structures will be identical
is begging for trouble down the line.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> I am in the process of writing a trigger that will insert a new record
into
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Yes. If all you want to do is a straight copy of the rows inserted into the
table with the trigger on it, you can use the syntax
INSERT INTO [AnotherTable] SELECT * FROM inserted
inserted is the virtual table that conains the inserted rows that caused the
trigger to fire. Note that inserted will also contain rows when an UPDATE is
performed (The new values will be in the inserted virtual table, and the old
values will be in the deleted virtual table).
--
If"CSHARPITPRO" wrote:
> Thanks Mark,
> Is this being copied from the 'INSERTED' table in SQL?
> "Mark Williams" wrote:
>|||Thanks Jim,
What do you think the best method is to copy the 'changed' records to
my AuditTable with out using the INSERTED Table? Should I just use INSERT
INTO ..and VALUES?
Thanks
"Jim Underwood" wrote:

> I am not sure if there is a way to do this (I have not seen one) but I wou
ld
> avoid this like the plague.
> The tables are identical today, but who knows what will change tomorrow.
> Something as simple as adding and audit or archive date to either table
> would break any app using such an approach. Structures have a tendency to
> evolve over time, and any code that assumes two structures will be identic
al
> is begging for trouble down the line.
>
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> into
> if
>
>|||I would use insert into with a specific list of values.
i.e. Insert into table1 (field1, field2, field3) values (select field1,
field2, field3 from inserted)
As everyone else mentioned you can use "insert into table select * from
inserted", but if any structures change you will runinto trouble.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>
would
to
identical
wondering|||Yes. Performance and consistency will both be served if you do so. It does
require a bit more work now, and a bit more when you make changes, but if
you design things, this won't be too much of a problem. You can also use
the information_schema.columns view to get the columns that are needed and
build a quicky insert generator (or use the ones in QA/SSMS) to do much of
the hard work of typing out column names.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>|||On Wed, 18 Jan 2006 09:23:03 -0800, CSHARPITPRO wrote:

>Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
>my AuditTable with out using the INSERTED Table? Should I just use INSERT
>INTO ..and VALUES?
Hi CSHARPITPRO,
No. INSERT ... VALUES inserts just one row at a time; a trigger can be
fired with a large number of rows in the inserted pseudo-table.
Use
INSERT INTO Tablename (Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM inserted
Hugo Kornelis, SQL Server MVP

Copying records from one table to another

I have a lot of templates that I copy from table to another, but ran into
one table where I am having trouble.
I normally do a copy like so:
INSERT table2 (field1, field2)
SELECT field1, field2 FROM table1
This works fine most of the time where I take all the data from table 1 and
put it in table 2.
My problem is that I have one table that has a version number that is only
in table 1. Normally, I would just do this in that case:
INSERT table2 (field1, field2, version)
SELECT field1, field2, 0 FROM table1
But what I need is to be able to look at the version in table2 and if null
(first time) then it is a 0, if there is a version already there, add 1 to
the max version number. Something like:
INSERT table2 (field1, field2, version = IsNull(Max(version),-1)+1)
SELECT field1, field2, 0 FROM table1
The problem is the version is in table 2 and not table1 (where I am doing my
select from).
How would I do this? Would I do a select inside of the insert?
Thanks,
TomI got it.
INSERT table2 (field1, field2, version)
SELECT field1, field2, isnull((select max(version) from table2),-1)+1 FROM
table1
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23yC02Tu5FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I have a lot of templates that I copy from table to another, but ran into
>one table where I am having trouble.
> I normally do a copy like so:
> INSERT table2 (field1, field2)
> SELECT field1, field2 FROM table1
> This works fine most of the time where I take all the data from table 1
> and put it in table 2.
> My problem is that I have one table that has a version number that is only
> in table 1. Normally, I would just do this in that case:
> INSERT table2 (field1, field2, version)
> SELECT field1, field2, 0 FROM table1
> But what I need is to be able to look at the version in table2 and if null
> (first time) then it is a 0, if there is a version already there, add 1 to
> the max version number. Something like:
> INSERT table2 (field1, field2, version = IsNull(Max(version),-1)+1)
> SELECT field1, field2, 0 FROM table1
> The problem is the version is in table 2 and not table1 (where I am doing
> my select from).
> How would I do this? Would I do a select inside of the insert?
> Thanks,
> Tom
>|||INSERT adds rows to a table, yet you're describing an update. So, which is i
t?
Please post DDL and sample data.
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:F309F0BE-3466-46B0-8736-515DF8B54C37@.microsoft.com...
> INSERT adds rows to a table, yet you're describing an update. So, which is
> it?
No it is an insert.
I am copying records from another table (which is a template).
When I need a new set of records I copy them from the template. The
template is where all changes are made.
Tom
> Please post DDL and sample data.
>
> ML|||Actually I thought I thought I had the answer (and I do), but only by
accident. It doesn't work as I expect it should.
Here is the DDL and Samples:
DROP TABLE table1
DROP TABLE table2
CREATE TABLE table1 (field1 Int,field2 Int)
CREATE TABLE table2 (field1 Int,field2 Int,version Int)
INSERT table1 values(15,20)
INSERT table1 values(15,21)
INSERT table1 values(15,22)
The inserts are:
INSERT table2 (field1,field2,version)
SELECT field1,field2,(SELECT isnull(max(version),-1)+1 FROM table2)
FROM table1
SELECT * FROM table2
On the first run table2 looks like:
field1 field2 version
-- -- --
15 20 0
15 21 0
15 22 0
on the second run it looks like:
field1 field2 version
-- -- --
15 20 0
15 21 0
15 22 0
15 20 1
15 21 1
15 22 1
This is actually what I wanted, but I am as to why it does this.
When I look at the statement I thought I made a mistake and thought I would
have to get the max(version) before I did the insert/select as I expected
the result to be:
field1 field2 version
-- -- --
15 20 0
15 21 1
15 22 2
15 20 3
15 21 4
15 22 5
But what seems to be happening is that the inner select (the one that gets
the maximum version) is only being executed once for the whole command
instead of for each record inserted.
Is that what is happening?
Thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eaPxHBv5FHA.3416@.TK2MSFTNGP15.phx.gbl...
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:F309F0BE-3466-46B0-8736-515DF8B54C37@.microsoft.com...
> No it is an insert.
> I am copying records from another table (which is a template).
> When I need a new set of records I copy them from the template. The
> template is where all changes are made.
> Tom
>|||On Fri, 11 Nov 2005 09:25:49 -0800, tshad wrote:

>I have a lot of templates that I copy from table to another, but ran into
>one table where I am having trouble.
>I normally do a copy like so:
>INSERT table2 (field1, field2)
>SELECT field1, field2 FROM table1
>This works fine most of the time where I take all the data from table 1 and
>put it in table 2.
>My problem is that I have one table that has a version number that is only
>in table 1. Normally, I would just do this in that case:
>INSERT table2 (field1, field2, version)
>SELECT field1, field2, 0 FROM table1
>But what I need is to be able to look at the version in table2 and if null
>(first time) then it is a 0, if there is a version already there, add 1 to
>the max version number. Something like:
>INSERT table2 (field1, field2, version = IsNull(Max(version),-1)+1)
>SELECT field1, field2, 0 FROM table1
>The problem is the version is in table 2 and not table1 (where I am doing m
y
>select from).
>How would I do this? Would I do a select inside of the insert?
Hi Tom,
Probably something like this:
INSERT INTO table2 (field1, field2, version)
SELECT t1.field1, t1.field2,
(SELECT COALESCE(MAX(t2.version) + 1, 0)
FROM table2 AS t2
WHERE t2.field1 = t1.field1
AND t2.field2 = t1.field2)
FROM table1 AS t1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 11 Nov 2005 11:18:02 -0800, tshad wrote:
(snip)
>When I look at the statement I thought I made a mistake and thought I would
>have to get the max(version) before I did the insert/select as I expected
>the result to be:
>field1 field2 version
>-- -- --
>15 20 0
>15 21 1
>15 22 2
>15 20 3
>15 21 4
>15 22 5
>But what seems to be happening is that the inner select (the one that gets
>the maximum version) is only being executed once for the whole command
>instead of for each record inserted.
>Is that what is happening?
Hi Tom,
No. The subquery IS executed for each row (at least in theory - SQL
Server can -and will!- optimize, as long as it doesn't affect the
results).
What happens is that all the subqueries are executed against a "before"
image of the table. That is done because data modifications are supposed
to be "instanteneous" - all rows are added to the table at once.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:0hdan11rc8q2b013305tdk0dq0l65o6ic9@.
4ax.com...
> On Fri, 11 Nov 2005 11:18:02 -0800, tshad wrote:
> (snip)
> Hi Tom,
> No. The subquery IS executed for each row (at least in theory - SQL
> Server can -and will!- optimize, as long as it doesn't affect the
> results).
> What happens is that all the subqueries are executed against a "before"
> image of the table. That is done because data modifications are supposed
> to be "instanteneous" - all rows are added to the table at once.
What does it do - put the whole table in memory to do that?
I assume you mean that it takes an image of the table before the first
select is done and just keeps using that?
What if I had done something like:
INSERT table2 (field1,field2,field3,field4,field5,fiel
d6,version)
SELECT field1,field2,(SELECT isnull(max(version),-1)+1 FROM table2),
(SELECT isnull(max(version),-1)+1 FROM table3),
(SELECT isnull(max(version),-1)+1 FROM table4),
(SELECT isnull(max(version),-1)+1 FROM table5),
(SELECT isnull(max(version),-1)+1 FROM table6)
FROM table1
and I was inserting 20 records.
Would the system take a before snapshot of all 5 tables before executing the
insert/select? Would all the records have the same number in field3 of all
20 records, and the same number in field 4 of all records etc?
Thanks,
Tom

> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 11 Nov 2005 17:17:17 -0800, tshad wrote:
(snip)
>What does it do - put the whole table in memory to do that?
>I assume you mean that it takes an image of the table before the first
>select is done and just keeps using that?
Hi Tom,
Don't forget that my explanation is the theoretic way of handling
things. Or rather, the way it is defined in the ANSI standard. Real
databases will choose more efficient ways to achieve the same effect.
In this specific case, where there's no correlation between subquery and
main query, the optimizer will probably choose to execute the subquery
only once and remember the results.
In more compicated situation, I _think_ that the actual implementation
stores the rows to be inserted (or the new data for changed rows, in
case of an UPDATE statement) in temporary storage until the query is
completely evaluated, then moves them all at once to the table. But only
the MS developers will kno for sure how it's acutally done.

>What if I had done something like:
>INSERT table2 (field1,field2,field3,field4,field5,fiel
d6,version)
>SELECT field1,field2,(SELECT isnull(max(version),-1)+1 FROM table2),
> (SELECT isnull(max(version),-1)+1 FROM table3),
> (SELECT isnull(max(version),-1)+1 FROM table4),
> (SELECT isnull(max(version),-1)+1 FROM table5),
> (SELECT isnull(max(version),-1)+1 FROM table6)
>FROM table1
>and I was inserting 20 records.
>Would the system take a before snapshot of all 5 tables before executing th
e
>insert/select? Would all the records have the same number in field3 of all
>20 records, and the same number in field 4 of all records etc?
No - for the subqueries that read for table3 through table6, there's no
need for special handling since these tables are not affected by the
INSERT statement.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Copying records from 7.0 2000 with problems

Hi.

I need to copy data from one database to another, on separate servers. I've got an old production database on "server1" and its SQL Server 7.0 on NT4. This data needs to move from there over to a database on "server2" which is SQL2000/Windows2000server.

Im trying to just run test queries from "server2" to read tables on "server1" and all I can get is 'invalid object name.'

Server1 is a linked server under Server2 and is being logged into as sa.

The computername for server1 happens to be "DATABASE"
Is this a problem? From what I understand I should be able to select from <server name>.<dbname>.dbo.<tablename>

I have tried typing <server name> as [DATABASE] and "DATABASE" and any other delimiting under the sun but all I get is 'invalid object name'

I have also tried just trying to select max(helptext) from "DATABASE".master.dbo.helpsql and I still get 'invalid object'

All of my queries will work if run from query analyzer on "server1" but not from query analyzer on "server2"

thanks in advance,

-jakedo you intend to move all the data in the database??
if you are maybe you can use the dts tools or if possible restore the full backup to the server2.
I can't really remember if sql 7's backup file can be restore to sql 2k version as haven't been using for quite a long time but you can try.|||I am only moving the data from select tables. The database names are different too.

I've pretty much ruled out the possibility of a backup/restore senario.|||have u tried import/export ??|||It was a communication issue.

I Turned Mixed Protocol OFF on the old 7.0 box and turned it ON the 2K box. Go Figure. The most confusing part of this ordeal was that the login for linked server was working from both servers and you could see the master tables/views under linked server.|||sorry in that case can't help you much
hopefully somebody else can help you on this|||Wow. That was like phone tag in a message board. Don't think I've ever experienced that.

Copying records

How would I copy x amount of records, at the same time changing one of the
fields?
I can't copy the records as is, because of unique constraints, and I can't
leave the field that needs to be changed blank, also because nulls are not
allowed. So how would I do it in one swoop?Use a staging table.
Copy the rows from TableA to a staging table. Change the data that
needs changing and then copy the rows in to Table B
Barry|||Not sure about doing in one swoop but you could use a staging table.
Copy the rows from TableA to a staging table. Change the data that
needs changing and then copy the rows in to Table B
Barry|||what does it need to be changed to? and from? and what data type is it?
x amount of records? do you mean e.g., "copy 50 rows from a to b" or
"copy however many rows match my criteria from a to b"?
Please post DDL, including constraints etc., sample data and desired
results. Otherwise, all answers can be considered nothing more than guesses.
dew wrote:
> How would I copy x amount of records, at the same time changing one of the
> fields?
> I can't copy the records as is, because of unique constraints, and I can't
> leave the field that needs to be changed blank, also because nulls are not
> allowed. So how would I do it in one swoop?
>|||Ah. That works great, thanks!
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1139592215.572339.195290@.g47g2000cwa.googlegroups.com...
> Use a staging table.
> Copy the rows from TableA to a staging table. Change the data that
> needs changing and then copy the rows in to Table B
> Barry
>sql

Copying column into another

Hi,
We already have "Data Entry Time" column with hundreds of records in it, and
realized later we also need "Data Update Time." The "Data Update Time"
should not be NULL. So as a starting point, we will copy everything in the
"Data Entry Time" column into the "Data Update Time" column for the already
existing records, and then each will go its own way. So my question is how
do I copy the "Data Entry Time" column with records into the "Data Update
Time" column?
YCAsp Psa wrote:
> Hi,
> We already have "Data Entry Time" column with hundreds of records in it, a
nd
> realized later we also need "Data Update Time." The "Data Update Time"
> should not be NULL. So as a starting point, we will copy everything in th
e
> "Data Entry Time" column into the "Data Update Time" column for the alread
y
> existing records, and then each will go its own way. So my question is ho
w
> do I copy the "Data Entry Time" column with records into the "Data Update
> Time" column?
> YC
UPDATE your_table
SET data_update_time = data_entry_time
WHERE data_update_time IS NULL ;
ALTER TABLE your_table
ALTER COLUMN data_update_time DATETIME NOT NULL;
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
--|||Thank you!
YC
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1141860909.277930.39530@.z34g2000cwc.googlegroups.com...
> Asp Psa wrote:
> UPDATE your_table
> SET data_update_time = data_entry_time
> WHERE data_update_time IS NULL ;
> ALTER TABLE your_table
> ALTER COLUMN data_update_time DATETIME NOT NULL;
> --
> 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
> --
>

copying a record within the same table...

Hi. I would like to copy some records within the same table. what happens is we get very similar new cases in our office and we want to apply the same information from one client to the new client.

i looked at:
INSERT INTO ClientSpecs (ClientID, Spec1, Spec2, etc.)
SELECT Spec1, Spec2 from ClientSpecs WHERE ClientID=1234
but i want to use the new client id in place of the old client id. is there a way to do this? does this make sense? we're basically copying one client's file to a new client's file, except we need to use the new client's id.INSERT INTO ClientSpecs (ClientID, Spec1, Spec2)
SELECT '123', Spec1, Spec2 from ClientSpecs WHERE ClientID=1234|||thank you for that cfr! i never thought it was going to be that easy. Now, I have another question.

How do I update a Spec from one client to another client. For example, I have

ClientID Spec1 Spec2
1234 1 23
1234 2 12

And I want to copy the first client's Spec2 information to another client.

ClientID Spec1 Spec2
4567 1 NULL
4567 2 NULL

But, I only want to copy the Spec2 information for the client. How would I do this? Does this even make sense?|||Untested:

UPDATE ClientSpecs destTble
SET destTble.Spec2 = (SELECT srcTbl.Spec2
FROM ClientSpecs srcTbl
WHERE srcTbl.ClientID = destTble.ClientID
AND srcTble.Spec1 = destTble.Spec1)
WHERE destTble.ClientID = '4567'

Copy table and/or records from SQL Server 2000 to 2005 Express

How can I copy a table from my sql server 2000 db to my sql server 2005 express edition?

I have a project in VS.NET 2005 and I have a db in App_Data folder. However, when I look into that folder, there is nothing visible. I now need to copy a table from my existing sql server 2000 to my db located in my project's App_Data folder.

Any help would be appreciated..

Regards,

You can use the DTS to do that.

Also, use the SQL managment Studio (enterprise manager in sql 2000) to manipulate MSSQL 2005

http://www.microsoft.com/downloads/details.aspx?FamilyID=82afbd59-57a4-455e-a2d6-1d4c98d40f6e&DisplayLang=en

Regards

Copy rows from a table to a new one using cursor in PL/SQL

Hi there,

can someone explain me how to use a cursor to retrieve all the rows in one table and then insert some of the records into another table? (PL/SQL) Or maybe tell me where I can find a good tutorial or information about how to do it?

Thanx a lot, Fausto

The real problem is the following:

I have a table SPECS with attributes (among others) ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 what means that the number of INP/OUT is restricted to a max of 3.

In order to have as many INP/OUT as needed for each SPEC, I created a new table (SPECS_INFO) with attributes ID (foreign key from table SPECS) , SEQUENCE_NR, INPUT, OUTPUT where I want to copy the information from a row ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 (at the SPECS table) to three rows of the new SPECS_INFO table:

For example:
ID INP1 INP2 INP3 OUT1 OUT2 OUT3
A1 CALL A CALL B CALL C ANSW A ANSW B ANSW C

TO

ID SEQUENCE_NR INPUT OUTPUT
A1 1 CALL A ANSW A
A1 2 CALL B ANSW B
A1 3 CALL C ANSW CTry something like this:

begin
for r in (select * from specs)
loop
insert into specs_info
values (r.id,1,r.inp1,r.out1);
insert into specs_info
values (r.id,2,r.inp2,r.out2);
insert into specs_info
values (r.id,3.r.inp3,r.out3);
end loop;
end;
/

;)|||Thanx a lot. It works fine :-)

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.