Minor Insert Into issue - SQL Server Forums

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)

Minor Insert Into issue

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-10-20 : 12:17:04

Hi folks,

I'd like to perform something like an Insert Into Table1 Select * from Table2 where both table1 and table2 have an autoincrementing identity column. Is there an easy way of just saying "insert everything but the ID column" instead of having to specify every column to insert?

Thanks!

Mike

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 14:19:25

no.

if you have a large number of columns use this to get the names:


Declare @ColumnList varchar(8000)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name 
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'TableName' 

SELECT @ColumnList

Go with the flow & have fun! Else fight the flow

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-10-20 : 20:01:32

I've tried several variants of the code that you suggest, yet I keep getting a column miscount/mismatch error. Again, both tables are identical, so I figure that it should be as follows... but it isn't working out:

DECLARE @ColumnList varchar(400)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'Associates' AND column_name <> 'ID'

INSERT INTO TempAssociates SELECT @ColumnList FROM associates

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-21 : 06:33:06

have you tried this?

exec('INSERT INTO TempAssociates SELECT ' + @ColumnList + 'FROM associates')

Go with the flow & have fun! Else fight the flow

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-10-21 : 08:55:41

Thanks. Using the above, no syntax errors are detected but the Exec statement is disregarded. As a matter of fact, even if I intentionally screw up the syntax (example: Exec ('Insett Into TempAssociates ...", I still get no syntax error given that the statement is disregarded....

morleyhill
Starting Member

19 Posts

Posted - 2004-10-21 : 09:26:32

Run the following SQL in Query Analyzer and post back what gets output in the Messages tab of the Results Pane-

Declare @ColumnList1 varchar(8000)
Declare @ColumnList2 varchar(8000)
SELECT @ColumnList1 = COALESCE(@ColumnList1 + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'Associates' AND column_name <> 'ID'
SELECT @ColumnList2 = COALESCE(@ColumnList2 + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = TempAssociates' AND column_name <> 'ID'
PRINT 'INSERT INTO TempAssociates SELECT ' + @ColumnList1 + 'FROM associates'
if @ColumnList1 <> @ColumnList2 PRINT 'Tables are not the same!'

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-10-21 : 09:36:16

Result: Query batch completed. No errors, no comments.

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-10-21 : 09:42:28

I just ran Query analyzer on the following to try to play with the syntax (I took out the required spaces in the Exec statement):
DECLARE @ColumnList varchar(400)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name
From INFORMATION_SCHEMA.Columns WHERE table_name = 'Associates'
AND column_name <> 'ID' ORDER BY ordinal_Position

EXEC('INSERT INTo TempAssociates SELECT' + @ColumnList + 'FROM associates')

It returned: Line 1: Incorrect syntax near 'SELECTFROM'. This means that @ColumnList is empty?

morleyhill
Starting Member

19 Posts

Posted - 2004-10-21 : 09:54:19

OK,

I know this is obvious but I'm going to say it anyway- Check you're connected to the correct database on the drop-down in the centre of the button bar!

Then, run this and see what you get-

SELECT column_name
From INFORMATION_SCHEMA.Columns
WHERE table_name = 'Associates'

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-10-21 : 09:58:46

Yep, connected to the right DB. The query returned:
column_name
--------------------------------------------------------------------------------------------------------------------------------
ID
Name
Acronym
ParticipationType
DirectParticipation
IndirectParticipation
Perception
CreditCardCostSharing
LabCostSharing
Active
Payable
HIPDelegation
CarrierPaymentDelegation
CashAccounting
OnStandBy
IndependentAccounting

(16 row(s) affected)

morleyhill
Starting Member

19 Posts

Posted - 2004-10-21 : 10:11:22

Now run this-

SELECT column_name
From INFORMATION_SCHEMA.Columns
WHERE table_name = 'TempAssociates'

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-10-21 : 10:19:11

column_name
--------------------------------------------------------------------------------------------------------------------------------
ID
Name
Acronym
ParticipationType
DirectParticipation
IndirectParticipation
Perception
CreditCardCostSharing
LabCostSharing
Active
Payable
HIPDelegation
CarrierPaymentDelegation
CashAccounting
OnStandBy
IndependentAccounting

(16 row(s) affected)

which confirms that the tables have identical column sets.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-21 : 10:54:32

change
EXEC('INSERT INTo TempAssociates SELECT' + @ColumnList + 'FROM associates')
to
EXEC('INSERT INTO TempAssociates (' + @ColumnList + ') SELECT ' + @ColumnList + ' FROM associates')

i think it should be ok

Go with the flow & have fun! Else fight the flow

morleyhill
Starting Member

19 Posts

Posted - 2004-10-21 : 10:56:39

So, now try this-

DECLARE @ColumnList varchar(400)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name
From INFORMATION_SCHEMA.Columns WHERE table_name = 'Associates'
AND column_name <> 'ID' ORDER BY ordinal_Position
PRINT '~~~' + @ColumnList + '~~~'

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-10-21 : 11:08:00

To morleyhill: the results were
~~~Name, Acronym, ParticipationType, DirectParticipation, IndirectParticipation, Perception, CreditCardCostSharing, LabCostSharing, Active, Payable, HIPDelegation, CarrierPaymentDelegation, CashAccounting, OnStandBy, IndependentAccounting~~~

But changing the structure of the Exec statement worked as per spirit1's last post.

Thanks for all your help, I really appreciate it!

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 11:51:36

Don't you need
SET IDENTITY_INSERT MyTargetTableName ON
INSERT INTO MyTargetTableName
SELCET A,B,C...
FROM MySourceTableName
SET IDENTITY_INSERT MyTargetTableName OFF

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-21 : 11:56:25

i don't think he needs that because he wants to insert all but id. no??

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 12:06:45

Darn it, you're right. I was thinking how annoying it is that whenever I [d]do[/b] use
SET IDENTITY_INSERT MyTargetTableName ON
SQL insists I have a column list on the INSERT bit ... Getting old, memory going, sorry!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources