My Insert Into Select is not working

Question

Here's what I have.

CREATE TABLE #wrdCombined
(id int,
word varchar(50)
)
INSERT INTO #wrdCombined (wrdID, Word)
SELECT wrdID, consonant + 'ab' as Word
FROM [dbo].[consonants]

I am trying to combine the values from the [dbo].[consonants] with a user inserted texts and then insert this into a temporary table. The error is: Msg 207, Level 16, State 1, Line 1
Invalid column name 'wrdID'.

Any help is much appreciated.

Answer 1

Look closer in your CREATE TABLE statement. You named the first column ID and not the wrdID. Fix it either in the INSERT or in the CREATE table to be the same.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Use

CREATE TABLE #wrdCombined
(id int,
word varchar(50)
)
INSERT INTO #wrdCombined (id, Word)
SELECT wrdID, consonant + 'ab' as Word
FROM [dbo].[consonants]

---

Many Thanks & Best Regards, Hua Min

Answer 3

Hi,

Try this

CREATE TABLE #wrdCombined
(id int,
word varchar(50)
)
INSERT INTO #wrdCombined (ID, Word)
SELECT wrdID, consonant + 'ab' as Word
FROM [dbo].[consonants]

Regards
Satheesh

Answer 4

Hi,

Try this

CREATE TABLE #wrdCombined
(id int,
word varchar(50)
)
INSERT INTO #wrdCombined (ID, Word)
SELECT wrdID, consonant + 'ab' as Word
FROM [dbo].[consonants]

Regards
Satheesh

Thank you so much for all the help. It worked. However, I've also discovered this:

SELECT wrdID, consonant + 'ab' as Word INTO #wrdCombined
FROM [lauj].[consonants]

Between the Select Into and Insert Into I have above, which one is more efficient?

Answer 5

If you don't have a table created in which you want to insert the data into then using SELECT INTO #tableName .. creates the table on the fly.

If you have the table already, you need to use INSERT INTO SELECT ... syntax as using SELECT INTO #tablename... returns error saying #tableName already exists.

If you create table at first then you could add all the constraints, keys as required.

Using SELECT INTO #tableName creates a simple table without any keys and constraints and you need to ALTER that table later if you need to add any constraints or keys as required (if you use it for other purposes).

Reg. efficiency piece - I don't think there would be any performance difference between the two (I might be wrong though).

Thanks!

Answer 6

Hi,

Try this

CREATE TABLE #wrdCombined
(id int,
word varchar(50)
)
INSERT INTO #wrdCombined (ID, Word)
SELECT wrdID, consonant + 'ab' as Word
FROM [dbo].[consonants]

Regards
Satheesh

Thank you so much for all the help. It worked. However, I've also discovered this:

SELECT wrdID, consonant + 'ab' as Word INTO #wrdCombined
FROM [lauj].[consonants]

Between the Select Into and Insert Into I have above, which one is more efficient?

The difference is

by this

SELECT wrdID, consonant + 'ab' as Word INTO #wrdCombined
FROM [lauj].[consonants]

it means you do not need to specifically create #wrdCombined at all before the insert.

But using insert, you do need to create #wrdCombined!

---

Many Thanks & Best Regards, Hua Min

Answer 7

Deepak, thanks for the insight.

A quick question regarding this temporary table creating and reading from again. I have the following statement.

SELECT wrdID, consonant + 'ab' as Word INTO #wrdCombined
FROM [lauj].[consonants]

SELECT * FROM #wrdCombined
WHERE #wrdCombined.Word = [dbo].[words].word

I have error on the second Select statement. Here's the error:

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "dbo.words.word" could not be bound.

However, when I tried this:

SELECT *
  FROM [dbo].[words]
  WHERE [dbo].[words].word = 'pab'

It works fine. So, why am I getting this error?

Answer 8

Try

SELECT a.* FROM #wrdCombined a,[dbo].[words] b
WHERE #wrdCombined.Word = [dbo].[words].word

---

Many Thanks & Best Regards, Hua Min

Answer 9

As dbo.Words and #wrdCombined are two separate tables, you need to JOIN them in FROM clause (as shown by Hua Min) before you can use columns from the respective tables.

Answer 10

Select INTO is quicker than INSERT INTO.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

SELECT wrdID, consonant + 'ab' as Word INTO #wrdCombined
FROM [lauj].[consonants]

SELECT * FROM #wrdCombined
WHERE #wrdCombined.Word = [dbo].[words].word

It works fine. So, why am I getting this error?

You don't have  [dbo].[words].Word in your from clause, so you cant really reference this table

Regards
Satheesh

Answer 12

You guys/gals are awesome! Many thanks for the all the help.