copy on condition

i have two tables A and B with the same fields,

If the id field of table B equals id field in Table A i need to update th edata for that id row.
If the id field doesn;t match then i need to insert a new record in tale A for that id

that is i need to perform insertion or updation into table A depending on table B data
Can anyone give me some idea how to start?

You need two statements.
UPDATE A SET col1 = b.col1, col2 = b.col2, ... FROM A INNER JOIN B on A.pkcol = B.pkcol
INERT INTO A (pkcol, col1, col2, col3)
SELECT pkcol, col1, col2, col3
FROM B
WHERE pkcol NOT IN (SELECT pkcol FROM A)|||

Thank you very much Alex!
One more question, I have millions of data in the table, will the 2 queries work well for such huge data
.I mean performance wise. I have created indexes .

|||You'll just have to try it to find out. No way of guessing that without knowing what the tables are, how the indexes are, hardware, load, etc ...

Copy Database to another server

Hello there
I have database on my local server
I have also database on internet server
at 12:00pm i would like to update the internet server with all the changes
that made on the local server
how can i do this?
any help would be useful
If every table on your database has a primary key you should be using
transactional replication for this.
"Oded Kovach" <roygoldh@.hotmail.com> wrote in message
news:u8joTU$kEHA.1936@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I have database on my local server
> I have also database on internet server
> at 12:00pm i would like to update the internet server with all the changes
> that made on the local server
> how can i do this?
> any help would be useful
>
|||Thankes kilary
I have many databases on the server
and some of the tables in some databases realy haven't primary key
These databases shouldn't be raplicated at all
How can i remove them from the list of the replication?
hope that you can help me
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eDrqXlAlEHA.2500@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> If every table on your database has a primary key you should be using
> transactional replication for this.
>
> "Oded Kovach" <roygoldh@.hotmail.com> wrote in message
> news:u8joTU$kEHA.1936@.TK2MSFTNGP12.phx.gbl...
changes
>
|||Thanks Hilary
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eDrqXlAlEHA.2500@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> If every table on your database has a primary key you should be using
> transactional replication for this.
>
> "Oded Kovach" <roygoldh@.hotmail.com> wrote in message
> news:u8joTU$kEHA.1936@.TK2MSFTNGP12.phx.gbl...
changes
>

copy data from one table to another...

hi. i'm trying to copy data from one table to another. the table has already been created but i just want to update some of the information in it.

here is what i have:

INSERT INTO DefendantCaseBAK

SELECT * FROM DefendantCase WHERE DefendantCase.StatusID=2
when i run this query, i get this error:
The column prefix 'DefendantCaseBAK' does not match with a table name or alias name used in the query.
what am i doing wrong? i looked up some examples on the internet and i copied them exactly. thanks for your help!First I would say you need to supply the column list

Does the bak table exist already?|||yes, the table does exist.

i tried including a couple columns to insert and i still get the same error.|||This is the whole script from start to end, not just the part where the compiler says the error is? Executed in a fresh new connection?|||i closed query analyzer and opened it back up and re-executed my query and worked with no problem. what gives? oh well. at least i'm not paranoid about why it's not working.|||Is it possible that you had the wrong DB selected in QA?|||I don't think that's the case the error message was too specific about a "column prefix". I think it was thrown off by some other piece of code.

copy data from 2 tables

I need to copy the data in the last name table to the field2 table do i use the update query in sql 2005?I don't really know enough about your problem to answer your question. Depending on the table structure and the tools you are using the answer is probably either yes or no.

I'm going to move your question to the SQL Server forum, since that is the database engine that you're using. See the FAQ at the top of the forum postings for the entry on how to ask questions to get quick and correct answers, it should help a lot.

I'll be happy to help, once I know what your tables look like, and what client tool(s) you are using (Query Analyzer, Microsoft Access, etc).

-PatP|||I have two databases database1 and database2 i need to copy all the data in database1 field lastname to database2 field field2 but only if database1.cust_no matches database2.sourceid|||CREATE TABLE database1 (
cust_no VARCHAR(9)
, lastname VARCHAR(25)
)

CREATE TABLE database2 (
cust_no VARCHAR(9)
, field2 VARCHAR(100)
)

INSERT INTO database1 (cust_no, lastname)
SELECT 'doe', 'Doe' UNION
SELECT 'jones', 'Jones' UNION
SELECT 'freshfitz', 'Freshfitz' UNION
SELECT 'pope', 'Pope' UNION
SELECT 'smith', 'Smith'

INSERT INTO database2 (cust_no, field2)
SELECT 'doe', NULL UNION
SELECT 'jones', 'Jones' UNION
SELECT 'freshfitz', 'was here' UNION
SELECT 'popper', NULL UNION
SELECT 'smith', 'Smith'

UPDATE z
SET field2 = lastname
FROM database2 AS z
JOIN database1 AS x
ON (x.cust_no = z.cust_no)

SELECT *
FROM database2

DROP TABLE database1
DROP TABLE database2-PatP|||Thats awesome but the problem is database1 and database 2 already exist and the fields already exist i just need to copy all the records in the field of database 1 field last name to database2 field field2(which has no data). The records in database1 have a unique id Cust_no that match the same unique id in database2 sourceid.

So if cust_no = sourceid copy the last name field

select * from database1 field lastname
copy to database2 field field2
where database1.cust_no = database2_sourceid|||So just start with the UPDATE, and you'll be fine.

-PatP

Copy a BLOB field in a stored-procedure from

Hello
I try to run the following query:
Update DestTable
set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID = 123)
Where DestTableID = 321
I always get the error message that this is not possible with image data.
Does anyone know a solution on how to copy a BLOB from one table to another?
I tried with WRITETEXT but this does not work either.
Many thanks
Thomas (thomas.steiner@.novaesprit.ch)Can you post your WRITETEXT code please.
Rick
"Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> Hello
> I try to run the following query:
> Update DestTable
> set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID =123)
> Where DestTableID = 321
> I always get the error message that this is not possible with image data.
> Does anyone know a solution on how to copy a BLOB from one table to
another?
> I tried with WRITETEXT but this does not work either.
> Many thanks
> Thomas (thomas.steiner@.novaesprit.ch)|||Here it is:
Declare @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(content)
FROM SambaReports_Test.dbo.Documents
Where DocumentID = 171
WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
RequestDataBody from RequestData where RequestID = 2328)
This results in a 'missing datastream' error. When I put a text-string
instead of the select-statement it works.
Thomas
"Rick Sawtell" wrote:
> Can you post your WRITETEXT code please.
> Rick
>
> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> > Hello
> > I try to run the following query:
> >
> > Update DestTable
> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID => 123)
> > Where DestTableID = 321
> >
> > I always get the error message that this is not possible with image data.
> > Does anyone know a solution on how to copy a BLOB from one table to
> another?
> > I tried with WRITETEXT but this does not work either.
> >
> > Many thanks
> >
> > Thomas (thomas.steiner@.novaesprit.ch)
>
>|||AFAIK, you can't pass a subquery to UPDATETEXT. The script below shows how
to copy text data between tables. If your data type is ntext, you'll need
to add calculations to allow 2 bytes per character.
DECLARE @.ptrval binary(16),
@.StartIndex int,
@.RequestDataBodyLength int,
@.RequestDataBody varchar(8000)
--clear any existing data and init text pointer
UPDATE Documents
SET content = NULL
WHERE DocumentID = 171
SET @.StartIndex = 0
--get length of data to copy
SELECT @.RequestDataBodyLength = DATALENGTH(RequestDataBody)
FROM RequestData
WHERE RequestID = 2328
--get text pointer
SELECT @.ptrval = TEXTPTR(content)
FROM Documents
WHERE DocumentID = 171
--copy data in chunks of 8000
WHILE @.StartIndex < @.RequestDataBodyLength
BEGIN
SELECT @.RequestDataBody = SUBSTRING(RequestDataBody, @.StartIndex, 8000)
FROM RequestData
WHERE RequestID = 2328
UPDATETEXT Documents.content @.ptrval @.StartIndex 0 @.RequestDataBody
SET @.StartIndex = @.StartIndex + DATALENGTH(@.RequestDataBody)
END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Grosswesir" <Grosswesir@.discussions.microsoft.com> wrote in message
news:73535098-61B7-4BEC-88AC-B125C0F5155C@.microsoft.com...
> Here it is:
> Declare @.ptrval binary(16)
> SELECT @.ptrval = TEXTPTR(content)
> FROM SambaReports_Test.dbo.Documents
> Where DocumentID = 171
> WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
> RequestDataBody from RequestData where RequestID = 2328)
> This results in a 'missing datastream' error. When I put a text-string
> instead of the select-statement it works.
> Thomas
> "Rick Sawtell" wrote:
>> Can you post your WRITETEXT code please.
>> Rick
>>
>> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
>> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
>> > Hello
>> > I try to run the following query:
>> >
>> > Update DestTable
>> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID
>> > =>> 123)
>> > Where DestTableID = 321
>> >
>> > I always get the error message that this is not possible with image
>> > data.
>> > Does anyone know a solution on how to copy a BLOB from one table to
>> another?
>> > I tried with WRITETEXT but this does not work either.
>> >
>> > Many thanks
>> >
>> > Thomas (thomas.steiner@.novaesprit.ch)
>>|||Hi Dan
It works fine! Thanks a lot.
However I had to change the line:
SUBSTRING(RequestDataBody, @.StartIndex, 8000)
to
SUBSTRING(RequestDataBody, @.StartIndex+1, 8000)
Best regards
Thomas
"Dan Guzman" wrote:
> AFAIK, you can't pass a subquery to UPDATETEXT. The script below shows how
> to copy text data between tables. If your data type is ntext, you'll need
> to add calculations to allow 2 bytes per character.
>
> DECLARE @.ptrval binary(16),
> @.StartIndex int,
> @.RequestDataBodyLength int,
> @.RequestDataBody varchar(8000)
> --clear any existing data and init text pointer
> UPDATE Documents
> SET content = NULL
> WHERE DocumentID = 171
> SET @.StartIndex = 0
> --get length of data to copy
> SELECT @.RequestDataBodyLength = DATALENGTH(RequestDataBody)
> FROM RequestData
> WHERE RequestID = 2328
> --get text pointer
> SELECT @.ptrval = TEXTPTR(content)
> FROM Documents
> WHERE DocumentID = 171
> --copy data in chunks of 8000
> WHILE @.StartIndex < @.RequestDataBodyLength
> BEGIN
> SELECT @.RequestDataBody => SUBSTRING(RequestDataBody, @.StartIndex, 8000)
> FROM RequestData
> WHERE RequestID = 2328
> UPDATETEXT Documents.content @.ptrval @.StartIndex 0 @.RequestDataBody
> SET @.StartIndex = @.StartIndex + DATALENGTH(@.RequestDataBody)
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Grosswesir" <Grosswesir@.discussions.microsoft.com> wrote in message
> news:73535098-61B7-4BEC-88AC-B125C0F5155C@.microsoft.com...
> > Here it is:
> >
> > Declare @.ptrval binary(16)
> >
> > SELECT @.ptrval = TEXTPTR(content)
> > FROM SambaReports_Test.dbo.Documents
> > Where DocumentID = 171
> > WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
> > RequestDataBody from RequestData where RequestID = 2328)
> >
> > This results in a 'missing datastream' error. When I put a text-string
> > instead of the select-statement it works.
> >
> > Thomas
> >
> > "Rick Sawtell" wrote:
> >
> >> Can you post your WRITETEXT code please.
> >>
> >> Rick
> >>
> >>
> >> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
> >> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> >> > Hello
> >> > I try to run the following query:
> >> >
> >> > Update DestTable
> >> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID
> >> > => >> 123)
> >> > Where DestTableID = 321
> >> >
> >> > I always get the error message that this is not possible with image
> >> > data.
> >> > Does anyone know a solution on how to copy a BLOB from one table to
> >> another?
> >> > I tried with WRITETEXT but this does not work either.
> >> >
> >> > Many thanks
> >> >
> >> > Thomas (thomas.steiner@.novaesprit.ch)
> >>
> >>
> >>
>
>

Converting Select query to Update

Hi,
I need to update some data based on the results of a select query. The
following select statement returns the values:
SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
C.Action, B.Status, C.dateEntered
FROM A INNER JOIN
B ON A.itemid = B.RecordNumber INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
ORDER BY A.itemid DESC
What I want to do is update DataSigned as follows.
UPDATE B SET B.DateSigned=C.dateEntered
WHERE '?
I am not sure how to set up the WHERE clause to update the correct records
with the correct values. Suggestions?
Thanks,
JerryYou can try the following:
UPDATE B
SET B.DateSigned = C.dateEntered
FROM B
inner join ( SELECT DISTINCT A.itemid
, B.DateSigned
, B.RefID
, C.EnteredBy
, C.Action
, B.Status
, C.dateEntered
FROM A
INNER JOIN B
ON A.itemid = B.RecordNumber
INNER JOIN C
ON A.itemid = C.recordid
LEFT OUTER JOIN D
ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
) C
on C.ItemID = B.RecordNumber
You may want to check the join clause to make sure you match the records
exactly. In any case, the idea is to use derived tables, which is the one
that is created on-the-fly using the SELECT statement, and referenced just
like a regular table or view
Let me know if it helps
"JerryK" wrote:

> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>
>|||something like this (completely untested):
UPDATE B
SET DateSigned=(
SELECT C.dateEntered
FROM A INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE D.itemid IS NULL
and C.action like '%signed%'
and A.itemid = B.RecordNumber )
WHERE status = 'app'
dean
"JerryK" <jerryk@.nospam.com> wrote in message
news:%23S1clJfIGHA.2668@.tk2msftngp13.phx.gbl...
> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>|||On Wed, 25 Jan 2006 13:00:37 -0800, JerryK wrote:
(snip)
Hi Jerry,
I just answered this question in microsoft.public.sqlserver.newusers.
In the future, please post your questions to one group only. And if you
really feel that a question should be in two groups, crosspost it (i.e.
send one copy to both groups at the same time) instead of sending
independent copies to the groups. With crossposting, all replies will
(normally) show up in both groups as well. That saves others the time
and energy to find an answer if the question already was answered
elsewhere!
Hugo Kornelis, SQL Server MVP

Converting NULL to a Text String

I'm using UPDATE as follows"

UPDATE Dubai

SET DB = 'D'

WHERE DB = NULL

but nothing happens. What am I doing wrong?

Thanks.

Your update needs to be modified from this:

Code Snippet

UPDATE Dubai

SET DB = 'D'

WHERE DB = NULL

to this:

Code Snippet

UPDATE Dubai

SET DB = 'D'

WHERE DB IS NULL

In older version of SQL Server your syntax would have worked. Null is considered a state and not a value. Null is considered a state. To check to see if a column is in a null state the syntax is to check if the column "is null".

|||

Just in addition and for reference:

http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp

Jens K. Suessmeyer

http://www.sqlserver2005.de