Copy info from one table two another with conditions

I am importing a csv file into a temp table (works fine), now I want to take the data in the temp table based off of the temp id compared to the id of the permanant file.

basically if the record id exists skip to the next line and so forth, I am able to import everything just fine but I cannot get the WHERE condition to acually work.

INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1 WHERE table2.col1 <> table1.col1

this is what my actual code is

INSERT INTO USER_INFO (fpu_id,FIRSTNAME, LASTNAME, BOXNUMBER) SELECT id,first,last,box FROM stmailbox WHERE stmailbox.id <> dbo.USER_INFO.USERID

this is the actual error that I get.

Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'USER_INFO' does not match with a table name or alias name used in the query.

--
thanks for the helpWell I think that I got it...

INSERT INTO USER_INFO (id,FIRSTNAME, LASTNAME, BOXNUMBER) SELECT id,first,last,box FROM stmailbox WHERE stmailbox.id NOT IN (SELECT id FROM USER_INFO)

Let me know if there is a better way

Thanks

Copy databases

Hello all,
I have a unique situation on my hands, and I need a bit of advice. I
currently use transaction-log based backups on my SQL server through
Symantec BackupExec for data protection / disaster recovery purposes.
However, I need a copy of my SQL data available for my development and
support people.
What I need is a way to copy the data from my production server that
does NOT truncate the transaction log, but does not require that my
support people restore from a full backup file, and then apply log
backup files to get a "current" database. As of now, I have written
a .Net app that allows them to restore files created with the "BACKUP
DATABASE" command, but clearly that will not work going forward.
Is there a good way to copy databases from one server to another
server in this situation? I don't necessarily care about syncing the
user IDs on my test machine, because end users have no visibility into
the testing area. If this process works, I would also like to use it
when updating training databases (copies of prod databases kept on the
same SQL instance).
Any help here is GREATLY appreciated!
Tom:
Have you considered some form of replication: either transactional or
snapshot?
HTH
Paul
"tom@.drdabbles.us" wrote:

> Hello all,
> I have a unique situation on my hands, and I need a bit of advice. I
> currently use transaction-log based backups on my SQL server through
> Symantec BackupExec for data protection / disaster recovery purposes.
> However, I need a copy of my SQL data available for my development and
> support people.
> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log, but does not require that my
> support people restore from a full backup file, and then apply log
> backup files to get a "current" database. As of now, I have written
> a .Net app that allows them to restore files created with the "BACKUP
> DATABASE" command, but clearly that will not work going forward.
> Is there a good way to copy databases from one server to another
> server in this situation? I don't necessarily care about syncing the
> user IDs on my test machine, because end users have no visibility into
> the testing area. If this process works, I would also like to use it
> when updating training databases (copies of prod databases kept on the
> same SQL instance).
> Any help here is GREATLY appreciated!
>
|||Replication would not be appropriate, since we want a point-in-time
copy of the data and not a constantly refreshed copy.
|||Tom wrote:
> Replication would not be appropriate, since we want a point-in-time
> copy of the data and not a constantly refreshed copy.
Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
<database> WITH COPY_ONLY.
|||> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log
BACKUP DATABASE. The only type of backup that truncates the log is BACKUP LOG.
I agree with Jeffrey that you check out COPY_ONLY. This isn't needed in your situation, but it makes
the intent of that backup a bit more clear. (COPY_ONLY for BACKUP DATABASE will not affect
differential backups - has no effect on log backups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<tom@.drdabbles.us> wrote in message
news:fd8f3a92-c082-4e21-b366-0e5079265554@.b1g2000hsg.googlegroups.com...
> Hello all,
> I have a unique situation on my hands, and I need a bit of advice. I
> currently use transaction-log based backups on my SQL server through
> Symantec BackupExec for data protection / disaster recovery purposes.
> However, I need a copy of my SQL data available for my development and
> support people.
> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log, but does not require that my
> support people restore from a full backup file, and then apply log
> backup files to get a "current" database. As of now, I have written
> a .Net app that allows them to restore files created with the "BACKUP
> DATABASE" command, but clearly that will not work going forward.
> Is there a good way to copy databases from one server to another
> server in this situation? I don't necessarily care about syncing the
> user IDs on my test machine, because end users have no visibility into
> the testing area. If this process works, I would also like to use it
> when updating training databases (copies of prod databases kept on the
> same SQL instance).
> Any help here is GREATLY appreciated!
|||Isnt there a copy db task in SSIS?
MC
"Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Tom wrote:
> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
|||Watch out so Erland don't see this! ;-)
I haven't used it, but I do know that among others, Erland have issues with this. It can run in two
modes where one does detach and attach (the only mode available for 2000).
The other mode does some object scripting and then transfer the data. I believe this is where most
of the problems are.
SSIS also have some options to transfer server level objects, like logins, jobs etc. This can be
fine, but unfortunately for logins the SID isn't the same on dest server...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:8900F35F-E714-4D73-902B-2104E8F950A8@.microsoft.com...
> Isnt there a copy db task in SSIS?
>
> MC
>
> "Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
> news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
>
|||Ahhh okay. It actually worked for me, but it seems i was lucky. Anyway,
Erland DONT read this ;)
MC
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uED7OdtmIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Watch out so Erland don't see this! ;-)
> I haven't used it, but I do know that among others, Erland have issues
> with this. It can run in two modes where one does detach and attach (the
> only mode available for 2000).
> The other mode does some object scripting and then transfer the data. I
> believe this is where most of the problems are.
> SSIS also have some options to transfer server level objects, like logins,
> jobs etc. This can be fine, but unfortunately for logins the SID isn't the
> same on dest server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:8900F35F-E714-4D73-902B-2104E8F950A8@.microsoft.com...
>
|||On Apr 10, 1:08Xam, Jeffrey Williams <jeff.williams3...@.verizon.ent>
wrote:
> Tom wrote:
> Are you on SQL Server 2005? XIf so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
Jeffrey,
This was EXACTLY what I needed. I somehow missed the section in the
docs on MSDN that gave the description of this option. As it turns
out, this actually is important. While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out. This is also true of log-
based backups using BACKUP from within T-SQL code.
Thanks again!!
|||<<While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out.>>
What a weird design. BE cripples the product and usability, for no gain. Glad that COPY_ONLY solves
this for you, though!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom" <tom@.drdabbles.us> wrote in message
news:d8bee064-e931-49f5-a3d8-e329f96e6c04@.u69g2000hse.googlegroups.com...
On Apr 10, 1:08 am, Jeffrey Williams <jeff.williams3...@.verizon.ent>
wrote:
> Tom wrote:
> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
Jeffrey,
This was EXACTLY what I needed. I somehow missed the section in the
docs on MSDN that gave the description of this option. As it turns
out, this actually is important. While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out. This is also true of log-
based backups using BACKUP from within T-SQL code.
Thanks again!!

Copy databases

Hello all,
I have a unique situation on my hands, and I need a bit of advice. I
currently use transaction-log based backups on my SQL server through
Symantec BackupExec for data protection / disaster recovery purposes.
However, I need a copy of my SQL data available for my development and
support people.
What I need is a way to copy the data from my production server that
does NOT truncate the transaction log, but does not require that my
support people restore from a full backup file, and then apply log
backup files to get a "current" database. As of now, I have written
a .Net app that allows them to restore files created with the "BACKUP
DATABASE" command, but clearly that will not work going forward.
Is there a good way to copy databases from one server to another
server in this situation? I don't necessarily care about syncing the
user IDs on my test machine, because end users have no visibility into
the testing area. If this process works, I would also like to use it
when updating training databases (copies of prod databases kept on the
same SQL instance).
Any help here is GREATLY appreciated!Tom:
Have you considered some form of replication: either transactional or
snapshot?
HTH
Paul
"tom@.drdabbles.us" wrote:
> Hello all,
> I have a unique situation on my hands, and I need a bit of advice. I
> currently use transaction-log based backups on my SQL server through
> Symantec BackupExec for data protection / disaster recovery purposes.
> However, I need a copy of my SQL data available for my development and
> support people.
> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log, but does not require that my
> support people restore from a full backup file, and then apply log
> backup files to get a "current" database. As of now, I have written
> a .Net app that allows them to restore files created with the "BACKUP
> DATABASE" command, but clearly that will not work going forward.
> Is there a good way to copy databases from one server to another
> server in this situation? I don't necessarily care about syncing the
> user IDs on my test machine, because end users have no visibility into
> the testing area. If this process works, I would also like to use it
> when updating training databases (copies of prod databases kept on the
> same SQL instance).
> Any help here is GREATLY appreciated!
>|||Replication would not be appropriate, since we want a point-in-time
copy of the data and not a constantly refreshed copy.|||Tom wrote:
> Replication would not be appropriate, since we want a point-in-time
> copy of the data and not a constantly refreshed copy.
Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
<database> WITH COPY_ONLY.|||> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log
BACKUP DATABASE. The only type of backup that truncates the log is BACKUP LOG.
I agree with Jeffrey that you check out COPY_ONLY. This isn't needed in your situation, but it makes
the intent of that backup a bit more clear. (COPY_ONLY for BACKUP DATABASE will not affect
differential backups - has no effect on log backups).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<tom@.drdabbles.us> wrote in message
news:fd8f3a92-c082-4e21-b366-0e5079265554@.b1g2000hsg.googlegroups.com...
> Hello all,
> I have a unique situation on my hands, and I need a bit of advice. I
> currently use transaction-log based backups on my SQL server through
> Symantec BackupExec for data protection / disaster recovery purposes.
> However, I need a copy of my SQL data available for my development and
> support people.
> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log, but does not require that my
> support people restore from a full backup file, and then apply log
> backup files to get a "current" database. As of now, I have written
> a .Net app that allows them to restore files created with the "BACKUP
> DATABASE" command, but clearly that will not work going forward.
> Is there a good way to copy databases from one server to another
> server in this situation? I don't necessarily care about syncing the
> user IDs on my test machine, because end users have no visibility into
> the testing area. If this process works, I would also like to use it
> when updating training databases (copies of prod databases kept on the
> same SQL instance).
> Any help here is GREATLY appreciated!|||Isnt there a copy db task in SSIS?
MC
"Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Tom wrote:
>> Replication would not be appropriate, since we want a point-in-time
>> copy of the data and not a constantly refreshed copy.
> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.|||Watch out so Erland don't see this! ;-)
I haven't used it, but I do know that among others, Erland have issues with this. It can run in two
modes where one does detach and attach (the only mode available for 2000).
The other mode does some object scripting and then transfer the data. I believe this is where most
of the problems are.
SSIS also have some options to transfer server level objects, like logins, jobs etc. This can be
fine, but unfortunately for logins the SID isn't the same on dest server...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:8900F35F-E714-4D73-902B-2104E8F950A8@.microsoft.com...
> Isnt there a copy db task in SSIS?
>
> MC
>
> "Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
> news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Tom wrote:
>> Replication would not be appropriate, since we want a point-in-time
>> copy of the data and not a constantly refreshed copy.
>> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE <database> WITH COPY_ONLY.
>|||Ahhh okay. It actually worked for me, but it seems i was lucky. Anyway,
Erland DONT read this ;)
MC
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uED7OdtmIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Watch out so Erland don't see this! ;-)
> I haven't used it, but I do know that among others, Erland have issues
> with this. It can run in two modes where one does detach and attach (the
> only mode available for 2000).
> The other mode does some object scripting and then transfer the data. I
> believe this is where most of the problems are.
> SSIS also have some options to transfer server level objects, like logins,
> jobs etc. This can be fine, but unfortunately for logins the SID isn't the
> same on dest server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:8900F35F-E714-4D73-902B-2104E8F950A8@.microsoft.com...
>> Isnt there a copy db task in SSIS?
>>
>> MC
>>
>> "Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
>> news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Tom wrote:
>> Replication would not be appropriate, since we want a point-in-time
>> copy of the data and not a constantly refreshed copy.
>> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
>> <database> WITH COPY_ONLY.
>|||On Apr 10, 1:08=A0am, Jeffrey Williams <jeff.williams3...@.verizon.ent>
wrote:
> Tom wrote:
> > Replication would not be appropriate, since we want a point-in-time
> > copy of the data and not a constantly refreshed copy.
> Are you on SQL Server 2005? =A0If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
Jeffrey,
This was EXACTLY what I needed. I somehow missed the section in the
docs on MSDN that gave the description of this option. As it turns
out, this actually is important. While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out. This is also true of log-
based backups using BACKUP from within T-SQL code.
Thanks again!!|||<<While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out.>>
What a weird design. BE cripples the product and usability, for no gain. Glad that COPY_ONLY solves
this for you, though!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom" <tom@.drdabbles.us> wrote in message
news:d8bee064-e931-49f5-a3d8-e329f96e6c04@.u69g2000hse.googlegroups.com...
On Apr 10, 1:08 am, Jeffrey Williams <jeff.williams3...@.verizon.ent>
wrote:
> Tom wrote:
> > Replication would not be appropriate, since we want a point-in-time
> > copy of the data and not a constantly refreshed copy.
> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
Jeffrey,
This was EXACTLY what I needed. I somehow missed the section in the
docs on MSDN that gave the description of this option. As it turns
out, this actually is important. While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out. This is also true of log-
based backups using BACKUP from within T-SQL code.
Thanks again!!

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK = 13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
--
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
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JY
Jon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegr oups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>
|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
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
|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
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
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

converting varchar to date from asp.net to sort

for some odd reason our other programmer used varchar datatype to store dates. he claims it gives him more control. now i am trying to sort it based on date. so i create a procedure:

CREATE PROCEDURE GetAllWeekEnding

AS
Select convert(datetime, we) as we2 FROM tblArchive order by we2
GO

if i use the convert function in the procedure, i'll get an error msg when i run the code. this is the code i am using.

Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL"))
Dim MyCommand As SqlCommand

MyCommand = New SqlCommand("GetAllWeekEnding", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
MyConnection.Open()

Dim mydr As SqlDataReader = MyCommand.ExecuteReader()
While mydr.Read()
DropDownList1.Items.Add(mydr("we2"))
End While

mydr.Close()
MyConnection.Close()

the error message is: No accessible overloaded 'ListItemCollection.Add' can be called without a narrowing conversion

any ideas?the .Add method expects a ListItem or a String. Try:

DropDownList1.Items.Add(mydr("we2").ToString())

Is there any reason you are not just binding to myDr?|||<<<<Is there any reason you are not just binding to myDr? >>>
yes, when i bind to the listbox, i am getting date and time.

i can get rid of the time by using:

While mydr.Read()
DropDownList1.Items.Add(Format(mydr("we2"), "M/dd/yyyy").ToString)
End While|||You could easily use Convert to convert the datetime to a date only (of type varchar()) in SQL Server in the SP if the only purpose of this SP is to feed this DDL.|||<<<<<You could easily use Convert to convert the datetime to a date only (of type varchar()) in SQL Server in the SP if the only purpose of this SP is to feed this DDL.
>>>>
the problem is that the date is already in varchar datatype in the database. on the aspx page, i want to load all the dates and i want it sorted by date. the only way for me to show the dates on the aspx page sorted by date is to convert the date from varchar to datetime, like this:

CREATE PROCEDURE GetAllWeekEnding

AS
Select Distinct convert(datetime, we) as we2 FROM tblArchive order by we2 desc
GO

once varchar is converted to datetime, it not only shows just the date but also time so i cannot bind it to a dropDownList.

if i just bind the orginal varchar on the DropDownList witout converting it to DateTime, it's not going to be sorted because you cannot sort a varchar like you sort a datetime|||CREATE PROCEDURE GetAllWeekEnding

AS
Select Distinct CONVERT(nvarchar(20),convert(datetime, we),101) as we2 FROM tblArchive order by convert(datetime, we) desc
GO

There is no rule that the representation you SELECT needs to be the same as you use to ORDER BY.

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