June 21, 2016 at 4:03 pm
Same Server, Different database
Source table
CREATE TABLE [dbo].[EvalOne](
[UserID] [int] NULL,
[Identifier] [nvarchar](10) NULL,
[LastName] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[Email] [nvarchar](100) NULL,
[Phone] [nvarchar](50) NULL
)
Sample junk data
INSERT INTO [dbo].[EvalOne] (([UserID],,[Identifier],[LastName],[FirstName],,[Email] ,[Phone])
VALUES (1, 'er434', 'Norway', 'from', 'swe@gfr444t.com', '13433433');
INSERT INTO [dbo].[EvalOne] (([UserID],,[Identifier],[LastName],[FirstName],,[Email] ,[Phone])
VALUES (2, 'egr434', 'Norways', 'from234', 'swe@gfr4444t.com', '23433433');
INSERT INTO [dbo].[EvalOne] (([UserID],,[Identifier],[LastName],,[FirstName],,[Email] ,[Phone])
VALUES (3, 'erf434', 'Norway1', 'from21', 'swe@gfr3323t.com', '33433433');
INSERT INTO [dbo].[EvalOne] (([UserID],,[Identifier],[LastName],[FirstName],,[Email] ,[Phone])
VALUES (4, 'er4f34', 'Norway2', 'from2', 'swe@gfr3323t.com', '543433433');
INSERT INTO [dbo].[EvalOne] (([UserID],,[Identifier],[LastName],[FirstName],,[Email] ,[Phone])
VALUES (5, 'er4d34', 'Norway3', 'from1', 'swe@gfrreret.com', '63433433');
Target Table
CREATE TABLE [dbo].[EvalTwo](
[UserID] [int] NULL,
[Identifier] [nvarchar](10) NULL,
[LastName] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[Email] [nvarchar](100) NULL,
[Phone] [nvarchar](50) NULL,
[CreateDate] [datetime2](7) NULL,
[modifiedby] [nvarchar](max) NULL
)
I have attempted to use both select and insert into
I initially attempted to use insert into to create a new table with two additional columns
Then I used SSIS into EvalOne
I attempted to use select into
select
[UserID] ,[Identifier] ,[FirstName],[LastName] ,[Phone], DateOne, UserNames
into TempTable.dbo.EvalTwo --IN 'TempTable.mdf'
select
[UserID] ,[Identifier] ,[FirstName],[LastName] ,[Phone],
(select getdate()) as DateOne, (select SUSER_SNAME()) as UserNames
from
into TempTable1.dbo.EvalOne
Receive the following error
Msg 207, Level 16, State 1, Line 2
Invalid column name 'UserID'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Identifier'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'FirstName'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'LastName'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Phone'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'DateOne'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'UserNames'.
attempted query in both databases
same error
Same server and database the following query works
insert into [dbo].[EvalTwo]
(UserID, Identifier, LastName, FirstName, Email, Phone, CreateDate, modifiedby )
select
UserID ,Identifier ,LastName ,FirstName ,Email ,Phone ,(select getdate()) as CreateDate, (select USER_NAME()) as modifiedby
from
[dbo].[EvalOne]
Change to different database same server same error as above
I am not sure which direction to go in.
This seems like a basic question.
I suspect it may be permissions
One database I am owner
the other I am not the owner
I can access and run the base query in each database.
I would appreciate any help with this
Thank You All
June 21, 2016 at 4:08 pm
Are your databases really called TempTable and TempTable1?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 21, 2016 at 4:11 pm
This construction is wrong:
select
[UserID] ,[Identifier] ,[FirstName],[LastName] ,[Phone], DateOne, UserNames
into TempTable.dbo.EvalTwo --IN 'TempTable.mdf'
select
[UserID] ,[Identifier] ,[FirstName],[LastName] ,[Phone],
(select getdate()) as DateOne, (select SUSER_SNAME()) as UserNames
from
into TempTable1.dbo.EvalOne
Please describe what you are trying to do.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 21, 2016 at 4:24 pm
What I am trying to do is move data using SQL from one database into another adding two derived columns
in this case Create date with the default of get date function and system user account or user name functions for the default value
the databases are not called temptable1 but I am using temptable database for staging tables for an ETL process. I can use SSIS to do this but the problem seems to be I cannot take data from one database into another.
As I mentioned in my original post that insert to works fine in the same database.
June 21, 2016 at 5:32 pm
OK, that helps. Here is a generalised form of your command, showing what you need to do. Obviously, you need to replace SourceDb and TargetDb with your source and target database names.
insert TargetDb.dbo.EvalTwo
(UserID, Identifier, LastName, FirstName, Email, Phone, CreateDate, modifiedby )
select
UserID ,Identifier ,LastName ,FirstName ,Email ,Phone , getdate(), USER_NAME()
from
SourceDb.dbo.EvalOne
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 21, 2016 at 5:46 pm
User Export got this
select
*
into TempTable.dbo.Evalthree --IN 'TempTable.mdf'
select
UserID , Identifier , FirstName , LastName , Phone,
(select getdate()) as "DateOne", (select SUSER_SNAME()) as "UserNames"
from
TempTable.dbo.EvalOne
The statement could not be parsed. (SQL Server Import and Export Wizard)
===================================
Deferred prepare could not be completed.
Statement(s) could not be prepared.
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Must specify table to select from. (Microsoft SQL Server Native Client 11.0)
------------------------------
Program Location:
at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)
at System.Data.OleDb.OleDbDataReader.BuildSchemaTableInfo(Object handle, Boolean filterITypeInfo, Boolean filterChapters)
at System.Data.OleDb.OleDbDataReader.GenerateSchemaTable(OleDbDataReader dataReader, Object handle, CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.DtsWizard.StepQuery.ParseStatement(Boolean bReportSuccess)
select
UserID , Identifier , FirstName , LastName , Phone, DateOne,UserNames
into TempTable.dbo.Evalthree --IN 'TempTable.mdf'
select
UserID , Identifier , FirstName , LastName , Phone,
(select getdate()) as DateOne, (select SUSER_SNAME()) as UserNames
from
TempTable.dbo.EvalOne
e statement could not be parsed. (SQL Server Import and Export Wizard)
===================================
Deferred prepare could not be completed.
Statement(s) could not be prepared.
Invalid column name 'UserNames'.
Invalid column name 'DateOne'.
Invalid column name 'Phone'.
Invalid column name 'LastName'.
Invalid column name 'FirstName'.
Invalid column name 'Identifier'.
Invalid column name 'UserID'. (Microsoft SQL Server Native Client 11.0)
------------------------------
Program Location:
at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)
at System.Data.OleDb.OleDbDataReader.BuildSchemaTableInfo(Object handle, Boolean filterITypeInfo, Boolean filterChapters)
at System.Data.OleDb.OleDbDataReader.GenerateSchemaTable(OleDbDataReader dataReader, Object handle, CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.DtsWizard.StepQuery.ParseStatement(Boolean bReportSuccess)
June 21, 2016 at 5:52 pm
Hi Phil that worked for the insert into
How different would the select into be in creating another table
June 21, 2016 at 6:02 pm
Something like this?
select UserID , Identifier , FirstName , LastName , Phone, getdate() 'DateOne', SUSER_SNAME() 'UserNames'
into TempTable.dbo.Evalthree --IN 'TempTable.mdf'
from TempTable.dbo.EvalOne
But I can't see why you would bother doing this! Why not select it directly? Why not create Evalthree as a permanent table and insert to it? If it needs to be empty before the insert, truncate it.
Also, you need to make sure that you check for the existence of Evalthree before executing the query, or you will receive an error.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply