pass through query syntax for INSERT statement to sql table from access
sql server 2008 linked table:
Trying to insert records from access table to sql server linked table
the following is getting an error message at:
convert([Employees Information2].[Birthdate] & '/2013', 'MM/DD/YYYY')
INSERT INTO dbo_anniv_bd ( [Last Name], [First Name], [Date Hired], [Birthdate] )
SELECT [Employees Information2].[Last Name], [Employees Information2].[First Name], convert([Employees Information2].[Date Hired],'MM/DD/YYYY'), convert([Employees Information2].[Birthdate] & '/2013', 'MM/DD/YYYY')
FROM [Employees Information2];
Thanks
fordraiders
Trying to insert records from access table to sql server linked table
the following is getting an error message at:
convert([Employees Information2].[Birthdate] & '/2013', 'MM/DD/YYYY')
INSERT INTO dbo_anniv_bd ( [Last Name], [First Name], [Date Hired], [Birthdate] )
SELECT [Employees Information2].[Last Name], [Employees Information2].[First Name], convert([Employees Information2].[Date Hired],'MM/DD/YYYY'), convert([Employees Information2].[Birthdate] & '/2013', 'MM/DD/YYYY')
FROM [Employees Information2];
Thanks
fordraiders
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What error do you get?
ASKER
syntax on these 2 lines:
format([Employees Information2].[Date Hired],"MM/DD/YYYY"),
format(([Employees Information2].[Birthdate] & "/2013"), "MM/DD/YYYY")
format([Employees Information2].[Date Hired],"MM/DD/YYYY"),
format(([Employees Information2].[Birthdate] & "/2013"), "MM/DD/YYYY")
When inserting to sql server I always format data as "d-mmm-yyyy" That way SQL Server never gets confused as to the day, month and year.
What is the datatype of the columns these two are going into?
The second format is having /2013 concatenated to it. What does the string look like prior to the /2013 being added on the end and what is the datatype?
Kelvin
What is the datatype of the columns these two are going into?
The second format is having /2013 concatenated to it. What does the string look like prior to the /2013 being added on the end and what is the datatype?
Kelvin
ASKER
kelvin, I made a setup query to format the fields before inserting records to view as I need first.
then took the new formatted query and used that as the insert query. worked fine.
then took the new formatted query and used that as the insert query. worked fine.
ASKER
thanks
Pleased to have helped think it through.
Kelvin
Kelvin
ASKER
INSERT INTO dbo_anniv_bd ( [Last Name], [First Name], [Date Hired], [Birthdate] )
SELECT [Employees Information2].[Last Name], [Employees Information2].[First Name],
format([Employees Information2].[Date Hired],"MM/DD/YYYY"),
format(([Employees Information2].[Birthdate] & "/2013"), "MM/DD/YYYY")
FROM [Employees Information2];
thanks
fordraiders