Copy one row from tableA to tableB

I want to copy all columns from tblA into tblB where tblA.UniqueID =
parameter @.ID without having to list each column name in an insert
statement. Is this possible?
Thanks,
lqI forgot to mention all column names and data types are identical in
tblA and tblB except for the UniqueID which is PK in tblA and not PK in
tblB.
lq|||Sure,

INSERT INTO tblB
SELECT *
FROM tblA
WHERE UniqueID = @.ID

Stu|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I want to copy all columns from tblA into tblB where tblA.UniqueID =
> parameter @.ID without having to list each column name in an insert
> statement. Is this possible?

Under some circumstances, yes.

Then again, in application code, I think it is very bad practice to say:

INSERT tbl2 SELECT * FROM tbl1

SELECT * itself is bad practice, so are INSERT statements without listing
of target columns.

Why are this bad:

* You cannot see if a column is actually used.
* If the table defintion changes, the result of the SELECT changes.
* And for INSERT it is likely to result in the INSERT statment failing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 and Past a row

Hello all,

Is there a way for me to copy a row, do some modification in a certain row and insert it to the same table as a new row?

Forward with thousand thanks.

Best regards,

Tee Song Yann

Where, in code in an application ? YOu will have to be a bit more specific.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hello Jens,

I'm trap in a pure SQL query's world. Imagine you are performing the above task in SQL Server Management Studio.

Best regards,

Tee Song Yann

|||Then you can just open a table with "Open Table" and select a row by clicking on the left side of the row (Selecting the whole column), STRG+C , go to the last row, which indicates a new row, STRG+V. Unless you leave the row, you will be able to change the values within, if you want to discard the insert or changes you can press ESC.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||OR <ctrl>C and <ctrl>V|||

Sorry I think I guide you people to the wrong place.

Actually it should be "Imagine you are performing the above task in SQL Server Management Studio SQL Query".

Can I perform that with SQL statement only?

Thanks.

|||

OK, yes this is sure possible:

INSERT INTO SomeTable2
(ColumnListHereto Insert)
Select YourColumnsHere
From SomeTable
Where ConditionsTofilterYourRow

Instead of YourColumnsHere you can use any manipulation on the data you want like

REPLACE(SomeColumn, 'a','b'), LEFT(SomeOtherColum,3)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hello Jens,

Thanks for your help. Then I better post more question here. Wahaha.

Thanks again and best regards.

Tee Song Yann

Copy a row with one change?

Hello. I have a table that has 3 fields as a primary key - JobID (int),
PONumber (char 16) and PORevision (tinyint). I need to make a new row with
the same data except the PORevision field should be increased by 1. I have
tried a few things but nothing seems to work. I could do it in my vb.net
code but I am sure it would be better in the sql database.
Thanks,
Gerry
INSERT INTO YourTable (jobid, ponumber, porevision, other_col, ...)
SELECT jobid, ponumber, porevision+1, other_col, ...
FROM YourTable
WHERE ...
David Portas
SQL Server MVP

Copy a row with one change?

Hello. I have a table that has 3 fields as a primary key - JobID (int),
PONumber (char 16) and PORevision (tinyint). I need to make a new row with
the same data except the PORevision field should be increased by 1. I have
tried a few things but nothing seems to work. I could do it in my vb.net
code but I am sure it would be better in the sql database.
Thanks,
GerryINSERT INTO YourTable (jobid, ponumber, porevision, other_col, ...)
SELECT jobid, ponumber, porevision+1, other_col, ...
FROM YourTable
WHERE ...
David Portas
SQL Server MVP
--

Copy a row with one change?

Hello. I have a table that has 3 fields as a primary key - JobID (int),
PONumber (char 16) and PORevision (tinyint). I need to make a new row with
the same data except the PORevision field should be increased by 1. I have
tried a few things but nothing seems to work. I could do it in my vb.net
code but I am sure it would be better in the sql database.
Thanks,
GerryINSERT INTO YourTable (jobid, ponumber, porevision, other_col, ...)
SELECT jobid, ponumber, porevision+1, other_col, ...
FROM YourTable
WHERE ...
--
David Portas
SQL Server MVP
--|||Or you could make the PORevision field an identity and
the system would do it for you.
>--Original Message--
>Hello. I have a table that has 3 fields as a primary
key - JobID (int),
>PONumber (char 16) and PORevision (tinyint). I need to
make a new row with
>the same data except the PORevision field should be
increased by 1. I have
>tried a few things but nothing seems to work. I could
do it in my vb.net
>code but I am sure it would be better in the sql
database.
>Thanks,
>Gerry
>
>.
>

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 table date

I am trying to to take data from a table where the Row data contains 24
columns representing hors 12 midnight to 11 PM see (sample below) into a
table that puts 1 hour per row?
Initial table
Name Date 12Min 1Min 2Min 3Min … 11Min
Mickey 12/2/05 625.4 153.2 84635…
Convbert to another table
Name Date Min
Mickey 12/2/05 00:00:00 625.4
Mickey 12/2/05 01:00:00 153.2
Mickey 12/2/05 02:00:00 84635Use unions.
e.g. (note: while you don't need to dateadd(hour,0,[Date]), but this
will work if the datatype is datetime or varchar)
select Name, dateadd(hour, 0, [Date]) as [Date], 12Min as Min from yourtable
union all
select Name, dateadd(hour, 1, [Date]) as [Date], 1Min as Min from yourtable
union all
...
union all
select Name, dateadd(hour, 11, [Date]) as [Date], 11Min as Min from
yourtable
Jim Abel wrote:
> I am trying to to take data from a table where the Row data contains 24
> columns representing hors 12 midnight to 11 PM see (sample below) into a
> table that puts 1 hour per row?
> Initial table
> Name Date 12Min 1Min 2Min 3Min … 11Min
> Mickey 12/2/05 625.4 153.2 84635…
> Convbert to another table
> Name Date Min
> Mickey 12/2/05 00:00:00 625.4
> Mickey 12/2/05 01:00:00 153.2
> Mickey 12/2/05 02:00:00 84635
>