INSERT based on another row . . .

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT based on another row . . .

Author

Topic

Wilco
Starting Member

9 Posts

Posted - 2007-09-14 : 11:32:51

I'm having trouble putting together the basic structure for an INSERT statement that creates a row based on another row (so basically copying all fields in a given row, except for say the primary key and one or two other fields). Searching hasn't yielded much success yet, so I thought I'd throw my question up here.

Any input appreciated!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-14 : 11:38:43

insert into Table1(<columns>)
select <columns>
from yourTable
where <put condition here that checks existance of another row>

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Wilco
Starting Member

9 Posts

Posted - 2007-09-14 : 12:00:45

What if I want to specify values for additional fields? Take this example:


INSERT INTO my_table (
    col_01,
    col_02,
    col_03)
SELECT col_01,
    col_02,
    col_03
FROM my_table
WHERE (col_01 = @col_01)

Now say I want to insert a specific value for "col_04" either as a parameter or simply a static value like "asdf" - where in the query would I add this (or is this even possible within one query)?

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2007-09-14 : 12:03:38

INSERT INTO my_table (
col_01,
col_02,
col_03,
col_04)
SELECT col_01,
col_02,
col_03,
'static_value'
FROM my_table
WHERE (col_01 = @col_01)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 12:03:40

INSERT INTO my_table (
col_01,
col_02,
col_03)
SELECT col_01 + 5, -- or any other value
col_02,
col_03
FROM my_table
WHERE col_01 = @col_01

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources