Insert value into the same table ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Insert value into the same table ?

Author

Topic

Dee
Starting Member

2 Posts

Posted - 2006-03-30 : 17:29:59

I'm trying to insert values into the same database from itself. Essentially, I want to add another row to the table for each row with reg_cat_id = 3. But in this row, I want the original registration_id to show up in the new row.

Here is my syntax below - this generates an error:

INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID, REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y', '05/05/2007')
SELECT REGISTRATION_ID, STAFF_ID, REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
FROM Registration_Category t1
WHERE (REG_CAT_ID = 3)
ORDER BY REGISTRATION_ID

Any suggestions?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-30 : 18:49:26

quote:
Originally posted by Dee

I want to add another row to the table for each row with reg_cat_id = 3. But in this row, I want the original registration_id to show up in the new row.

Very hard to undrstand ur requirement. Post some
1. sample data and
2. Data needed to be entered to the Table.

quote:

Originally posted by Dee

INSERT INTO Registration_Category
                      (REG_CAT_ID, REGISTRATION_ID, STAFF_ID, REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
VALUES     (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y', '05/05/2007')
                          SELECT     REGISTRATION_ID, STAFF_ID, REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
                           FROM         Registration_Category t1
                           WHERE     (REG_CAT_ID = 3)
                           ORDER BY REGISTRATION_ID

Ur formatted Query was not visible because it was not enclosed in "Code Tags"

Ur Insert Statement is incorrect

Insert Statement would be

Insert Into Tbl (<Fields list>) Values (<Values List>)

or

Insert Into Tbl (<Fields list>) Select <Fields List> from Tbl

For ur requirement (with my assumptions), I think a temp table is needed and first insert data to that and insert data from that to the original.

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-31 : 00:24:14

[code]INSERT INTO Registration_Category (REG_CAT_ID, REGISTRATION_ID, STAFF_ID, REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
SELECT 90, REGISTRATION_ID, 'test', '05/05/2007', 'Y', '05/05/2007'
FROM Registration_Category
WHERE REG_CAT_ID = 3[/code]

KH
Choice is an illusion, created between those with power, and those without.

Dee
Starting Member

2 Posts

Posted - 2006-03-31 : 09:30:36

quote:
Originally posted by khtan

INSERT INTO Registration_Category (REG_CAT_ID, REGISTRATION_ID, STAFF_ID, REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
SELECT 90, REGISTRATION_ID, 'test', '05/05/2007', 'Y', '05/05/2007'
FROM Registration_Category
WHERE REG_CAT_ID = 3

KH
Choice is an illusion, created between those with power, and those without.

Thanks! This actually worked. Looks like REGISTRATION_ID didn't have to have a table alias prefix. Also I didn't have to use a temp table. Thank you so much.

Dee

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources