July 16, 2012 at 12:48 pm
Given that someone, before me, used the wrong table name... this.that, could someone please tell me why the syntax is not working for both INSERT and INSERT INTO?
insert [this.that].dbo.table_name
insert into [this.that].dbo.table_name
The insert fails.. due to too many parts... it shows "this.that.dbo.table_name" not what whas typed in "[this.that].dbo.table_name".
July 16, 2012 at 12:51 pm
INSERT and INSERT INTO are identical - INTO is there only as a readability aid.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 16, 2012 at 1:13 pm
dwilliscp (7/16/2012)
Given that someone, before me, used the wrong table name... this.that, could someone please tell me why the syntax is not working for both INSERT and INSERT INTO?insert [this.that].dbo.table_name
insert into [this.that].dbo.table_name
The insert fails.. due to too many parts... it shows "this.that.dbo.table_name" not what whas typed in "[this.that].dbo.table_name".
Opps sorry.. been working with this code too long today.. the question SHOULD have read..
Given that someone, before me, used the wrong table name... this.that, could someone please tell me why the syntax is not working for both INTO and INSERT INTO?
select *
into [this.that].dbo.table_name
from #a
insert into [this.that].dbo.table_name
Select *
from #a
The insert fails.. due to too many parts... it shows "this.that.dbo.table_name" not what whas typed in "[this.that].dbo.table_name".
July 16, 2012 at 1:21 pm
both commands will work perfectly in SSMS;
are you running this through an ODBC driver, and the driver might be mis-translating the object names?
where is this code being executed?
Lowell
July 16, 2012 at 1:21 pm
dwilliscp (7/16/2012)
The insert fails.. due to too many parts... it shows "this.that.dbo.table_name" not what whas typed in "[this.that].dbo.table_name".
It's hard to know if you don't give the exact error, code and/or DDL.
July 16, 2012 at 1:23 pm
Lowell (7/16/2012)
both commands will work perfectly in SSMS;are you running this through an ODBC driver, and the driver might be mis-translating the object names?
where is this code being executed?
I created a database [this.that] and could not get the INSERT statement to work.
July 16, 2012 at 1:30 pm
Lynn Pettis (7/16/2012)
Lowell (7/16/2012)
both commands will work perfectly in SSMS;are you running this through an ODBC driver, and the driver might be mis-translating the object names?
where is this code being executed?
I created a database [this.that] and could not get the INSERT statement to work.
with this code, i tested it on 2008 and 2005 with not errors..., not sure what the OP is doing specifically.
create database [this.that]
GO
use [this.that]
select top 5 * into #a from sys.columns
select *
into [this.that].dbo.table_name
from #a
insert into [this.that].dbo.table_name
Select *
from #a
select * FROM [this.that].dbo.table_name
GO
use master
drop database [this.that]
Lowell
July 16, 2012 at 1:40 pm
I had no errors whatsoever with the database name of this.that
see below example
Create database [this.that];
GO
Use [this.that];
GO
Create table [this.that] (someid int);
GO
with E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b)
Insert Into [this.that].dbo.sometable (someid)
Select N
From E4;
Truncate Table sometable;
with E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b)
Insert [this.that].dbo.sometable (someid)
Select N
From E4;
Truncate table sometable;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 16, 2012 at 1:47 pm
Lowell (7/16/2012)
Lynn Pettis (7/16/2012)
Lowell (7/16/2012)
both commands will work perfectly in SSMS;are you running this through an ODBC driver, and the driver might be mis-translating the object names?
where is this code being executed?
I created a database [this.that] and could not get the INSERT statement to work.
with this code, i tested it on 2008 and 2005 with not errors..., not sure what the OP is doing specifically.
create database [this.that]
GO
use [this.that]
select top 5 * into #a from sys.columns
select *
into [this.that].dbo.table_name
from #a
insert into [this.that].dbo.table_name
Select *
from #a
select * FROM [this.that].dbo.table_name
GO
use master
drop database [this.that]
All right, add me to the list of confused. I ran your code above with no problems on SQL Server 2008 R2.
July 16, 2012 at 1:50 pm
Select ... Into ... is VERY different from Insert ... Select ...
The Select Into syntax will create the target table. If it already exists, you'll get an error message. The Insert Select syntax inserts into a table that already exists. If it doesn't exist yet, you'll get an error message.
They aren't equivalent statements.
You can run them in sequence Select Into then Insert Select, because the Select Into will create the table and the Insert Select will insert into it. But you can't do it the other way around, Insert Select then Select Into won't work, because the table doesn't exist yet when the Insert Select is run.
That looks like the most likely real error here. Nothing to do with the database name.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2012 at 1:52 pm
GSquared (7/16/2012)
Select ... Into ... is VERY different from Insert ... Select ...The Select Into syntax will create the target table. If it already exists, you'll get an error message. The Insert Select syntax inserts into a table that already exists. If it doesn't exist yet, you'll get an error message.
They aren't equivalent statements.
You can run them in sequence Select Into then Insert Select, because the Select Into will create the table and the Insert Select will insert into it. But you can't do it the other way around, Insert Select then Select Into won't work, because the table doesn't exist yet when the Insert Select is run.
That looks like the most likely real error here. Nothing to do with the database name.
I'm not going to look at further, but what didn't work for me was create the database, create the table, complete an insert into. The insert into failed with the same error the OP is reporting.
I run the code Lowell posted, and everything worked just great.
See, I'm confused.
July 16, 2012 at 2:01 pm
ok i think i see it.
if you run my previous sample code, including the DROP DATABASE,a dn THEN run the code snippets below, you get a misleading message:
it's not maintaining the quotename around the database object into the error message.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'this.that.dbo.table_name'.
insert into [this.that].dbo.table_name
Select *
from #a
select * FROM [this.that].dbo.table_name
Lowell
July 17, 2012 at 7:34 am
Lynn Pettis (7/16/2012)
GSquared (7/16/2012)
Select ... Into ... is VERY different from Insert ... Select ...The Select Into syntax will create the target table. If it already exists, you'll get an error message. The Insert Select syntax inserts into a table that already exists. If it doesn't exist yet, you'll get an error message.
They aren't equivalent statements.
You can run them in sequence Select Into then Insert Select, because the Select Into will create the table and the Insert Select will insert into it. But you can't do it the other way around, Insert Select then Select Into won't work, because the table doesn't exist yet when the Insert Select is run.
That looks like the most likely real error here. Nothing to do with the database name.
I'm not going to look at further, but what didn't work for me was create the database, create the table, complete an insert into. The insert into failed with the same error the OP is reporting.
I run the code Lowell posted, and everything worked just great.
See, I'm confused.
I'd have to see your exact script, the one that's failing, to pinpoint the problem.
The error message drops the [] around the database name, but that doesn't matter. What matters is if they're in the script, not whether they're in the error message.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 17, 2012 at 9:43 am
Ok.. I thought maybe I was missing something simple, sorry.
here is the error message..
Msg 117, Level 15, State 1, Line 3
The object name 'R2D2.this.that.dbo.ztb_test' contains more than the maximum number of prefixes. The maximum is 2.
and the SQL
select top 5 *
into R2D2.[this.that].dbo.ztb_test
from zt_sysjobs
I am using 2005 SQL Server management Studio.. this error is the same as a Stored Proc as it is in a query window.
July 17, 2012 at 9:57 am
Have you tried this?
select top 5 *
into [this.that].dbo.ztb_test
from zt_sysjobs
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply