How to insert data from a table into the same table?

[sinCity99]

Hi,

I need to copy data from one table into the same table but with some different values as well. Also i'm having trouble since row_timestamp cannot be null.

Example: Table has columns A, A1, A2, A3, A4, A5

I need columns A1, A2, A3 to be copied and i also want to insert new values into A4 and A5.

How can I do this?

So far i have:

----
insert into Table (A1, A2, A3)
select A1, A2, A3 from Table
where A=apple

set A=pear,
A4='XXX',
A5='YYY'
row_timestamp = default
------

 

[bborissov]

insert into Table (A1, A2, A3, A4, A5)
select A1, A2, A3, NewValueForA4, NewValueForA5
from Table
where A=apple

That is enough. If you have DEFAULT value for row_timestamp it will be added.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[sinCity99]

When i do:

insert into Table (A, A1, A2, A3, A4, A5)
select 'pear',A1, A2, A3, 'XXX', 'YYY', 'default'
from Table
where A=apple

I get:
Incorrect syntax near the keyword 'default'.

How can you do the above if 'XXX', 'YYY', 'default' does not come from table Table?

 

[gmmastros]

Usually... if there is a default value for a column set within the table, then you can override the default by setting it's value, or...

don't include the column in the list of columns, and it will get the default value.

Can you reply with the results of this query...

Select Column_Default
From   Information_Schema.Columns
Where  Table_Name = 'YourTableNameHere'
       And Column_Name = 'YourColumnNameHere'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sinCity99]

I get NULL.

 

[gmmastros]

Hmmm..... What do you get for this:

Select Data_Type
From   Information_Schema.Columns
Where  Table_Name = 'YourTableNameHere'
       And Column_Name = 'YourColumnNameHere'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sinCity99]

i get timestamp.

Is the query below the correct syntax though?

insert into Table (A, A1, A2, A3, A4, A5)
select 'pear',A1, A2, A3, 'XXX', 'YYY', 'default'
from Table
where A=apple

How can you do the above if 'XXX', 'YYY', 'default' does not come from table Table?

 

[gmmastros]

the syntax is correct, except you have 6 coluns listed on line 1, and 7 pieces of data in the select line. Other than that, your fine.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sinCity99]

Sorry i left out a column from the top line but it still doesn't work. I keep getting

Incorrect syntax near the keyword 'default'.

 

[gmmastros]

TimeStamp columns are automatically set for you. So, remove the TimeStamp column from the first line, and remove the default part from the second line.

For example:

-- Create a test table to play with
Create Table Test(A1 int, TS TimeStamp)

-- Put one row in to the table
Insert Into Test(A1) Select 1

-- Add a new row based on an existing row, but hard coding a value
Insert Into Test(A1)
Select 99
From   Test
where  A1 = 1

-- See what is in the table
Select * From Test

-- Cleanup after ourselves
Drop Table Test

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sinCity99]

Thanks, it was the timestamp columns that were throwing the errors!