Using Insert with 'Select clause'

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

Transact-SQL (2000)

Using Insert with 'Select clause'

Author

Topic

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-03-17 : 16:01:17

Hi,
I have 2 tables: Tab A with 100 columns and Tab B with 10 columns. I have to populate Tab A with Tab B values. As I need to populate only 10 columns in Tab A, I was hoping to this would work:

insert into TAB_A
(EMPLID, COURSE_START_DT, COURSE, SESSION_NBR, COURSE_TITLE,
COURSE_END_DT ,INTERNAL_EXTERNAL,
SCHOOL_CODE, SCHOOL, DT_NEEDED)
select
EMPLID, COURSE_START_DT, COURSE, SESSION_NBR, ' ' ,
COURSE_END_DT ,'I',
' ', ' ', ' '
from Tab_B

:but because there are many not-null fields, I get an error message listing each column name saying- Cannot insert NULL!

Now I thought the other approach would be to list all column names in Tab A and pass blank values for coulmns in Select clause of Tab B which seems to work but I don't want to type all 100 column names.

What confuses me is that when I do data entry from the front-end (web page) I only key 10 coulmn values and it saves it - which I attribute for code behind page - i am looking into this now.

Meanwhile, Is there an easier way to do this?
Thanks,
Sarat

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-17 : 16:06:04

Alter the table you are inserting into to have default values for all of the non-null columns, then you should be fine.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-17 : 16:27:33

I find the following to be handy though, just save it someplace and drag it out when you need it. You can simply modify the code to get even more sophisticated (like if you want to use a case clause to determine if it's char, numeric or dates).

USE NorthWind

DECLARE @TBName sysname, @TBName2 sysname

SELECT @TBName = 'Orders', @TBName2 = 'Orders2'

SELECT SQL FROM (

SELECT 'INSERT INTO ' + @TBName2 + ' ( ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT ') ' As SQL, TABLE_NAME, 2 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TBName
UNION ALL
SELECT 'SELECT ' + '''' + ' ' + '''' + ' AS ' + COLUMN_NAME As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + '''' + ' ' + '''' + ' AS ' + COLUMN_NAME As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT ' FROM ' + TABLE_NAME As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TBName
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order

Brett

8-)

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-03-17 : 19:23:48

I think your script is awesome.
Problem is I have bunch of Not Null fields (about 90). So the script will give me the required insert-select for the columns I need to populate but Not Null columns will not let me insert the data coz they need a value.
I tried altering but I can alter only one column at a time which is again a pain coz I have to do that 90 times. Shouldn't I be able to alter multiple columns in one run?
I think I will write a script which returns all not null columns in a table and assign some default value while doing Insert-Select. Why is this so cumbersome?
Thanks,
Sarat.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-17 : 20:13:15

It does. It is supplying the value space (' ') for every column. The reference I made to making it more soffisticated by using a case statement to determine what field to supply. I didn't have the name of the table to insert into, that would be against your table. Did you execute the sample? The Select would be against the table 10 columns. It places the default space in every column. This will not be 100% correct, because you may have numeric or datetime columns. You need to accomodate for that. That's wheer the Case statement comes in to play.

Chaeck out BOL for INFORMATION_SCHEMA.Columns.

Good Luck

Brett

8-)

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-03-18 : 11:20:43

Ya, I had the table names switched + I had to convert for numeric fields and now it works beautifully!!
Thank you so much!!
Sarat.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-18 : 11:57:00

Great. I'm glad you got it to work....NOW, think of the extensions of what you can do with this...I use SQL to generate SQL all the time (I hate typing, well except here of course).

A good DBA is a lazy DBA

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources