Insert whole column instead of 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

General SQL Server Forums

New to SQL Server Programming

Insert whole column instead of row.

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-10 : 08:37:27

Hello there.

Is there a way i can insert into a table and populate a whole column instead of a row.

for eg

Create Table days (

[-60] int,
[-30] int,
[0] int,
[+11] int,
[+25] int,
[+26] int,
[+55] int,
[+60] int)

then i want to insert values from select statement and populated the first column instead of row.

Regards

Rob

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-10 : 08:43:39

its ok sorted it.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-10 : 08:45:08

Looks weird

insert table(col1) select col_Y from anotherTable

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-10 : 08:49:15

AFAIK there is no way to do that in a built in statement. (unless it's a calculated column with a default)

What you do is create the column (either allowing null values or with a default)

Then UPDATE the table SET the value for that column to whatever the relationship is

You can certainly do the whole UPDATE in one statement as long as you can Form a sufficient relationship.
Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-10 : 09:07:53

no not got it. lol

when i insert the first time, it looks all good.

but when i insert the second. i get a new rows with null values.

eg

NULL 51 NULL NULL NULL NULL NULL NULL
NULL 394 NULL NULL NULL NULL NULL NULL
NULL 2 NULL NULL NULL NULL NULL NULL
NULL 38 NULL NULL NULL NULL NULL NULL
NULL 218 NULL NULL NULL NULL NULL NULL
51 NULL NULL NULL NULL NULL NULL NULL
394 NULL NULL NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL NULL NULL
38 NULL NULL NULL NULL NULL NULL NULL
218 NULL NULL NULL NULL NULL NULL NULL

I only want to update the first 5 rows. how would i get round this.?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-10 : 09:26:14

there is no concept of First X rows.

But for your example -- you only want to update the rows where the first column is NULL? Does this help you out at all?


UPDATE f SET
    [colA] = ot.[val]
    , [colB] = ot.[val2]
FROM
    theTable AS f
    JOIN otherTable as ot ON ot.[relationship] = f.[relationship]
WHERE
    f.[SomeCondition] = [SomeCondition]

If not post the table definition.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources