INSERT w/SELECT *and* User Fields

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 w/SELECT *and* User Fields

Author

Topic

Jeepaholic
Starting Member

36 Posts

Posted - 2002-09-18 : 18:40:34

Hi there...couple of table structures:

SOURCE
------
Field1
Field2
Field3

DESTINATION
-----------
ID (identity)
Name
Field1
Field2
Field3

The Name column in the destination is user generated, and this is where my trouble is. I need to copy data from the Source to the Destination and include the Name column in the insert.

It's easy enough to insert the Source into Destination without the Name column:

INSERT Destination (Field1, Field2, Field3)
SELECT Field1, Field2, Field3
FROM Source
WHERE Field1 = 'HI'

...but I can't seem to figure out the Name column.

Should I use a temporary table somehow? I've tried by haven't managed to nail it yet, and haven't seen any examples on-line. Thanks for any thoughts!

chadmat
The Chadinator

1974 Posts

Posted - 2002-09-18 : 18:58:24

I don't think there is enough info here to anser this. Are you saying you don't know the name value when you make the insert? If that is the case, you need to insert null for name (Assuming name is nullable) and update it later when you know the value.

-Chad

Jeepaholic
Starting Member

36 Posts

Posted - 2002-09-18 : 19:00:14

No, sorry...I definitely know the Name variable. It is user entered, and ready for insertion at the time this query will execute.

chadmat
The Chadinator

1974 Posts

Posted - 2002-09-18 : 19:06:05

Then I definitely don't understand what the problem is?

Is this a Stored Proc and/or some vb/asp client?

declare @name varchar(100)
set @name = 'Jeepaholic'

INSERT into Destination
SELECT @name, Field1, Field2, Field3
FROM Source
WHERE Field1 = 'HI'

-Chad

Jeepaholic
Starting Member

36 Posts

Posted - 2002-09-18 : 19:30:39

That problem was that I simply wasn't aware that you could do what you posted. Thanks for the response, works perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources