How do I insert a datetime value into a SQL database table where the type of the column is datetime?
Matthew Dresser
7,83577 gold badges6464 silver badges108108 bronze badges
sama
8 Answers
The following should work and is my recommendation (parameterized query):
Thorsten DittmarThorsten Dittmar
48k44 gold badges7373 silver badges121121 bronze badges
and execute the query. Sean B
DateTime.Now is to insert current Datetime.
8,15422 gold badges2121 silver badges3434 bronze badges
ReshmaReshma
53011 gold badge1414 silver badges3333 bronze badges
It's more standard to use the format yyyy-mm-dd hh:mm:ss (IE: 2009-06-23 19:30:20)
Using that you won't have to worry about the format of the date (MM/DD/YYYY or DD/MM/YYYY). It will work with all of them.
AndreaAndrea
1,85122 gold badges1313 silver badges1313 bronze badges
It's been awhile since I wrote this stuff, so this may not be perfect. but the general idea is there.
WARNING: this is unsanitized. You should use parameters to avoid injection attacks.
EDIT: Since Jon insists.
JoelJoel
17.2k22 gold badges5454 silver badges8181 bronze badges
This is an older question with a proper answer (please use parameterized queries) which I'd like to extend with some timezone discussion. For my current project I was interested in how do the
datetime The worlds easyest game answers. columns handle timezones and this question is the one I found.
Turns out, they do not, at all.
datetime column stores the given DateTime as is, without any conversion. It does not matter if the given datetime is UTC or local.
You can see for yourself:
What this will print will of course depend on your time zone but most importantly the read values will all have
Kind = Unspecified . The first and second output line will be different by your timezone offset. Second and third will be the same. Using the 'o' format string (roundtrip) will not show any timezone specifiers for the read values.
Example output from GMT+02:00:
Also note of how the data gets truncated (or rounded) to what seems like 10ms.
joonasjoonas
you can send your DateTime value into SQL as a String with its special format. this format is 'yyyy-MM-dd HH:mm:ss'
Example: CurrentTime is a variable as datetime Type in SQL. And dt is a DateTime variable in .Net.
Sep 3, 2012 - ElsaWin 6.0 Plus VW 01.2017 Multilingual Size: 15.6 GB Year / Release Date: 2017. The database contains information only in German! The AUDI base 03.2017 is compatible with the versions ElsaWin 4.x - 6.x. The database compatibility control files for versions 4.x - 5.x (control # XX.xml) were created manually. Language: Multilingual (Russian is present). Dec 2, 2012 - ElsaWin 5.30 Plus Multilanguage FULL PACK VW 01.2016 - SEAT 01.2015 - SKODA. But i've a problem with Volkswagen Database. Elsawin database 03.2017 multilanguage. Apr 20, 2017 - Free download 2017 ElsaWin VW, Audi, Skoda, Seat 6.0 and 2016. Is there any possibility of installing Elsawin 5.3, with databases. AU 03.2017 DE.txt. File Type:.txt. But i have ELSAWIN 6.0 installed and i use VW 2017/1 without problems?? You need to have elsa 6.0 and audi database 2016 for that, for me it works perfect.
PedPakPedPak
Convert the existing
DateTime object to a string with single quotes around it. I don't think it really matters what format it is, as long as it is valid.
Something like this:
Optionally you can create a new format and pass it as a parameter to RomCoo
data.ToString(format) ;
1,60622 gold badges1616 silver badges2727 bronze badges
DanDan
atfergsatfergs
1,58011 gold badge1010 silver badges1515 bronze badges
I am working (or fixing bugs) on an application which was developed in VS 2005 C#. The application saves data to a SQL server 2005. One of insert SQL statement tries to insert a time-stamp value to a field with GetDate() TSQL function as date time value.
The reason to use GetDate() function is that the SQL server may be at a remove site, and the date time may be in a difference time zone. Therefore, GetDate() will always get a date from the server. As the function can be verified in SQL Management Studio, this is what I get:
One thing I realize is that the length is not up to the milliseconds, i.e., 19 is actually for '2010-06-10 14:04:48'. Anyway, the issue I have right now is that after the insert, the fieldDt actually has a date time value up to minutes, for example, '2010-06-10 14:04:00'. I am not sure why. I don't have permission to update or change the table with a trigger to update the field.
My question is that how I can use a INSERT T-SQL to add a new row with a date time value ( SQL server's local date time) with a precision up to milliseconds?
David.Chu.caDavid.Chu.ca
15.9k5858 gold badges131131 silver badges179179 bronze badges
5 Answers
Check your table. My guess is that the FieldDT column has a data type of SmallDateTime which stores date and time, but with a precision to the nearest minute. If my guess is correct, you will not be able to store seconds or milliseconds unless you change the data type of the column.
G MastrosG Mastros
20.4k33 gold badges3939 silver badges5353 bronze badges
I would guess that you are not storing the GetDate() value in a DateTime field. If you store the value in a datetime field you will get the maximum precision allowed by the DateTime type. Additionally, DateTime is a binary type (a double actually) so 19 means 19 bytes, not 19 characters.
Try to create a simple table with a Datetime field like this
And add a date with
When you execute a select you will get back a value including milliseconds. The following query
returns
2010-06-11 00:38:46.660
Panagiotis KanavosPanagiotis Kanavos
61.4k55 gold badges8686 silver badges119119 bronze badges
Maybe this would work instead of getdate - SYSDATETIME()look here if you can find what you need - http://msdn.microsoft.com/en-us/library/ms188383.aspx
VishalVishal
6,8251414 gold badges6464 silver badges120120 bronze badges
As you're on SQL 2005, don't forget the getutcdate() function to ensure that, regardless of where your servers are actually located, you have a constant time reference.
Imagine, you have the server in the UK in winter (i.e. GMT+0), and save a record at 10:30am. You then cut over to a SQL server hosted in California (GMT+8) and 8 hours later save another record.
Using getdate(), both saves record the same time '10:30:00'. Using getutcdate(), the first save records at '10:30:00', the second save records '18:30:00'.
Not really answering the question, but important in your circumstances.
Neil MossNeil Moss
5,04511 gold badge1818 silver badges3434 bronze badges
You can use like this in procedure and If there is no procedure use only getdate().
insert into [dbo].[Tbl_User] (UserId,Uvendoremail,UAddress,Ddob,DMobile, DEmail,DPassword,DAddress,CreatedDate) values (@userid,@vendoremail@address,@dob,@mobile,@email,@dpassword,@daddress,getdate())
Nirav RavaniNirav Ravani
Not the answer you're looking for? Browse other questions tagged visual-studiotsql or ask your own question.-->
APPLIES TO: SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse
Adds one or more rows to a table or a view in SQL Server. For examples, see Examples.
SyntaxArguments
WITH <common_table_expression>
Specifies the temporary named result set, also known as common table expression, defined within the scope of the INSERT statement. The result set is derived from a SELECT statement. For more information, see WITH common_table_expression (Transact-SQL).
TOP (expression) [ PERCENT ]
Specifies the number or percent of random rows that will be inserted. expression can be either a number or a percent of the rows. For more information, see TOP (Transact-SQL).
INTO
Is an optional keyword that can be used between INSERT and the target table.
server_name
Applies to: SQL Server 2008 through SQL Server 2017.
Is the name of the linked server on which the table or view is located. server_name can be specified as a linked server name, or by using the OPENDATASOURCE function.
When server_name is specified as a linked server, database_name and schema_name are required. When server_name is specified with OPENDATASOURCE, database_name and schema_name may not apply to all data sources and is subject to the capabilities of the OLE DB provider that accesses the remote object.
database_name
Applies to: SQL Server 2008 through SQL Server 2017.
Is the name of the database.
schema_name
Is the name of the schema to which the table or view belongs.
table_or view_name
Is the name of the table or view that is to receive the data.
A table variable, within its scope, can be used as a table source in an INSERT statement.
The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view. For example, an INSERT into a multi-table view must use a column_list that references only columns from one base table. For more information about updatable views, see CREATE VIEW (Transact-SQL).
rowset_function_limited
Applies to: SQL Server 2008 through SQL Server 2017.
Is either the OPENQUERY or OPENROWSET function. Use of these functions is subject to the capabilities of the OLE DB provider that accesses the remote object.
WITH ( <table_hint_limited> [.. n ] )
Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required.
READPAST, NOLOCK, and READUNCOMMITTED are not allowed. For more information about table hints, see Table Hints (Transact-SQL).
Important
The ability to specify the HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD, or UPDLOCK hints on tables that are targets of INSERT statements will be removed in a future version of SQL Server. These hints do not affect the performance of INSERT statements. Avoid using them in new development work, and plan to modify applications that currently use them.
Specifying the TABLOCK hint on a table that is the target of an INSERT statement has the same effect as specifying the TABLOCKX hint. An exclusive lock is taken on the table.
(column_list)
Is a list of one or more columns in which to insert data. column_list must be enclosed in parentheses and delimited by commas.
If a column is not in column_list, the Database Engine must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. The Database Engine automatically provides a value for the column if the column:
column_list must be used when explicit values are inserted into an identity column, and the SET IDENTITY_INSERT option must be ON for the table.
Sql Server Insert Datetime Value
OUTPUT Clause
Returns inserted rows as part of the insert operation. The results can be returned to the processing application or inserted into a table or table variable for further processing.
The OUTPUT clause is not supported in DML statements that reference local partitioned views, distributed partitioned views, or remote tables, or INSERT statements that contain an execute_statement. The OUTPUT INTO clause is not supported in INSERT statements that contain a <dml_table_source> clause.
VALUES
Introduces the list or lists of data values to be inserted. There must be one data value for each column in column_list, if specified, or in the table. The value list must be enclosed in parentheses.
If the values in the Value list are not in the same order as the columns in the table or do not have a value for each column in the table, column_list must be used to explicitly specify the column that stores each incoming value.
You can use the Transact-SQL row constructor (also called a table value constructor) to specify multiple rows in a single INSERT statement. The row constructor consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma. For more information, see Table Value Constructor (Transact-SQL).
DEFAULT
Forces the Database Engine to load the default value defined for a column. If a default does not exist for the column and the column allows null values, NULL is inserted. For a column defined with the timestamp data type, the next timestamp value is inserted. DEFAULT is not valid for an identity column.
expression
Is a constant, a variable, or an expression. The expression cannot contain an EXECUTE statement.
When referencing the Unicode character data types nchar, nvarchar, and ntext, 'expression' should be prefixed with the capital letter 'N'. If 'N' is not specified, SQL Server converts the string to the code page that corresponds to the default collation of the database or column. Any characters not found in this code page are lost.
derived_table
Is any valid SELECT statement that returns rows of data to be loaded into the table. The SELECT statement cannot contain a common table expression (CTE).
execute_statement
Is any valid EXECUTE statement that returns data with SELECT or READTEXT statements. For more information, see EXECUTE (Transact-SQL).
The RESULT SETS options of the EXECUTE statement cannot be specified in an INSERT..EXEC statement.
If execute_statement is used with INSERT, each result set must be compatible with the columns in the table or in column_list.
execute_statement can be used to execute stored procedures on the same server or a remote server. The procedure in the remote server is executed, and the result sets are returned to the local server and loaded into the table in the local server. In a distributed transaction, execute_statement cannot be issued against a loopback linked server when the connection has multiple active result sets (MARS) enabled.
If execute_statement returns data with the READTEXT statement, each READTEXT statement can return a maximum of 1 MB (1024 KB) of data. execute_statement can also be used with extended procedures. execute_statement inserts the data returned by the main thread of the extended procedure; however, output from threads other than the main thread are not inserted.
You cannot specify a table-valued parameter as the target of an INSERT EXEC statement; however, it can be specified as a source in the INSERT EXEC string or stored-procedure. For more information, see Use Table-Valued Parameters (Database Engine).
<dml_table_source>
Specifies that the rows inserted into the target table are those returned by the OUTPUT clause of an INSERT, UPDATE, DELETE, or MERGE statement, optionally filtered by a WHERE clause. If <dml_table_source> is specified, the target of the outer INSERT statement must meet the following restrictions:
The compatibility level of the database must be set to 100 or higher. For more information, see OUTPUT Clause (Transact-SQL).
<select_list>
Is a comma-separated list specifying which columns returned by the OUTPUT clause to insert. The columns in <select_list> must be compatible with the columns into which values are being inserted. <select_list> cannot reference aggregate functions or TEXTPTR.
Note
Any variables listed in the SELECT list refer to their original values, regardless of any changes made to them in <dml_statement_with_output_clause>.
<dml_statement_with_output_clause>
Is a valid INSERT, UPDATE, DELETE, or MERGE statement that returns affected rows in an OUTPUT clause. The statement cannot contain a WITH clause, and cannot target remote tables or partitioned views. If UPDATE or DELETE is specified, it cannot be a cursor-based UPDATE or DELETE. Source rows cannot be referenced as nested DML statements.
WHERE <search_condition>
Is any WHERE clause containing a valid <search_condition> that filters the rows returned by <dml_statement_with_output_clause>. For more information, see Search Condition (Transact-SQL). When used in this context, <search_condition> cannot contain subqueries, scalar user-defined functions that perform data access, aggregate functions, TEXTPTR, or full-text search predicates.
DEFAULT VALUES
Applies to: SQL Server 2008 through SQL Server 2017.
Forces the new row to contain the default values defined for each column.
BULK
Applies to: SQL Server 2008 through SQL Server 2017.
Used by external tools to upload a binary data stream. This option is not intended for use with tools such as SQL Server Management Studio, SQLCMD, OSQL, or data access application programming interfaces such as SQL Server Native Client.
FIRE_TRIGGERS
Applies to: SQL Server 2008 through SQL Server 2017.
Specifies that any insert triggers defined on the destination table execute during the binary data stream upload operation. For more information, see BULK INSERT (Transact-SQL).
CHECK_CONSTRAINTS
Applies to: SQL Server 2008 through SQL Server 2017.
Specifies that all constraints on the target table or view must be checked during the binary data stream upload operation. For more information, see BULK INSERT (Transact-SQL).
KEEPNULLS
Applies to: SQL Server 2008 through SQL Server 2017.
Specifies that empty columns should retain a null value during the binary data stream upload operation. For more information, see Keep Nulls or Use Default Values During Bulk Import (SQL Server).
KILOBYTES_PER_BATCH = kilobytes_per_batch
Specifies the approximate number of kilobytes (KB) of data per batch as kilobytes_per_batch. For more information, see BULK INSERT (Transact-SQL).
ROWS_PER_BATCH =rows_per_batch
Applies to: SQL Server 2008 through SQL Server 2017.
Indicates the approximate number of rows of data in the binary data stream. For more information, see BULK INSERT (Transact-SQL).
Note
A syntax error is raised if a column list is not provided.
Remarks
For information specific to inserting data into SQL graph tables, see INSERT (SQL Graph).
Best Practices
Use the @@ROWCOUNT function to return the number of inserted rows to the client application. For more information, see @@ROWCOUNT (Transact-SQL).
Best Practices for Bulk Importing DataUsing INSERT INTO..SELECT to Bulk Import Data with Minimal Logging
You can use
INSERT INTO <target_table> SELECT <columns> FROM <source_table> to efficiently transfer a large number of rows from one table, such as a staging table, to another table with minimal logging. Minimal logging can improve the performance of the statement and reduce the possibility of the operation filling the available transaction log space during the transaction.
Minimal logging for this statement has the following requirements:
Rows that are inserted into a heap as the result of an insert action in a MERGE statement may also be minimally logged.
Unlike the BULK INSERT statement, which holds a less restrictive Bulk Update lock, INSERT INTO..SELECT with the TABLOCK hint holds an exclusive (X) lock on the table. This means that you cannot insert rows using parallel insert operations.
Using OPENROWSET and BULK to Bulk Import Data
The OPENROWSET function can accept the following table hints, which provide bulk-load optimizations with the INSERT statement:
These optimizations are similar to those available with the BULK INSERT command. For more information, see Table Hints (Transact-SQL).
Data Types
When you insert rows, consider the following data type behavior:
Inserting Values into User-Defined Type Columns
You can insert values in user-defined type columns by:
Error Handling
You can implement error handling for the INSERT statement by specifying the statement in a TRY..CATCH construct.
If an INSERT statement violates a constraint or rule, or if it has a value incompatible with the data type of the column, the statement fails and an error message is returned.
If INSERT is loading multiple rows with SELECT or EXECUTE, any violation of a rule or constraint that occurs from the values being loaded causes the statement to be stopped, and no rows are loaded.
When an INSERT statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, the Database Engine handles these errors as if SET ARITHABORT is set to ON. The batch is stopped, and an error message is returned. During expression evaluation when SET ARITHABORT and SET ANSI_WARNINGS are OFF, if an INSERT, DELETE or UPDATE statement encounters an arithmetic error, overflow, divide-by-zero, or a domain error, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.
Interoperability
When an INSTEAD OF trigger is defined on INSERT actions against a table or view, the trigger executes instead of the INSERT statement. For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).
Limitations and Restrictions
When you insert values into remote tables and not all values for all columns are specified, you must identify the columns to which the specified values are to be inserted.
When TOP is used with INSERT the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in this statements. If you need to use TOP to insert rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause that is specified in a subselect statement. See the Examples section that follows in this topic.
INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted.
In Parallel Data Warehouse, the ORDER BY clause is invalid in VIEWS, CREATE TABLE AS SELECT, INSERT SELECT, inline functions, derived tables, subqueries and common table expressions, unless TOP is also specified.
Logging Behavior
The INSERT statement is always fully logged except when using the OPENROWSET function with the BULK keyword or when using
INSERT INTO <target_table> SELECT <columns> FROM <source_table> . These operations can be minimally logged. For more information, see the section 'Best Practices for Bulk Loading Data' earlier in this topic.
Security
During a linked server connection, the sending server provides a login name and password to connect to the receiving server on its behalf. For this connection to work, you must create a login mapping between the linked servers by using sp_addlinkedsrvlogin.
When you use OPENROWSET(BULK..), it is important to understand how SQL Server handles impersonation. For more information, see 'Security Considerations' in Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK..) (SQL Server).
Permissions
INSERT permission is required on the target table.
INSERT permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.
To execute INSERT with the OPENROWSET function BULK option, you must be a member of the sysadmin fixed server role or of the bulkadmin fixed server role.
Examples
Basic Syntax
Examples in this section demonstrate the basic functionality of the INSERT statement using the minimum required syntax.
A. Inserting a single row of data
The following example inserts one row into the
Production.UnitMeasure table in the AdventureWorks2012 database. The columns in this table are UnitMeasureCode , Name , and ModifiedDate . Because values for all columns are supplied and are listed in the same order as the columns in the table, the column names do not have to be specified in the column list*.*
B. Inserting multiple rows of data
The following example uses the table value constructor to insert three rows into the
Production.UnitMeasure table in the AdventureWorks2012 database in a single INSERT statement. Because values for all columns are supplied and are listed in the same order as the columns in the table, the column names do not have to be specified in the column list.
C. Inserting data that is not in the same order as the table columns
The following example uses a column list to explicitly specify the values that are inserted into each column. The column order in the
Production.UnitMeasure table in the AdventureWorks2012 database is UnitMeasureCode , Name , ModifiedDate ; however, the columns are not listed in that order in column_list.
Handling Column Values
Examples in this section demonstrate methods of inserting values into columns that are defined with an IDENTITY property, DEFAULT value, or are defined with data types such as uniqueidentifer or user-defined type columns.
D. Inserting data into a table with columns that have default values
The following example shows inserting rows into a table with columns that automatically generate a value or have a default value.
Column_1 is a computed column that automatically generates a value by concatenating a string with the value inserted into column_2 . Column_2 is defined with a default constraint. If a value is not specified for this column, the default value is used. Column_3 is defined with the rowversion data type, which automatically generates a unique, incrementing binary number. Column_4 does not automatically generate a value. When a value for this column is not specified, NULL is inserted. The INSERT statements insert rows that contain values for some of the columns but not all. In the last INSERT statement, no columns are specified and only the default values are inserted by using the DEFAULT VALUES clause.
E. Inserting data into a table with an identity column
The following example shows different methods of inserting data into an identity column. The first two INSERT statements allow identity values to be generated for the new rows. The third INSERT statement overrides the IDENTITY property for the column with the SET IDENTITY_INSERT statement and inserts an explicit value into the identity column.
Convert Date Time Sql ServerF. Inserting data into a uniqueidentifier column by using NEWID()
The following example uses the NEWID() function to obtain a GUID for
column_2 . Unlike for identity columns, the Database Engine does not automatically generate values for columns with the uniqueidentifier data type, as shown by the second INSERT statement.
G. Inserting data into user-defined type columns
The following Transact-SQL statements insert three rows into the
PointValue column of the Points table. This column uses a CLR user-defined type (UDT). The Point data type consists of X and Y integer values that are exposed as properties of the UDT. You must use either the CAST or CONVERT function to cast the comma-delimited X and Y values to the Point type. The first two statements use the CONVERT function to convert a string value to the Point type, and the third statement uses the CAST function. For more information, see Manipulating UDT Data.
Inserting Data from Other Tables
Examples in this section demonstrate methods of inserting rows from one table into another table.
H. Using the SELECT and EXECUTE options to insert data from other tables
The following example shows how to insert data from one table into another table by using INSERT..SELECT or INSERT..EXECUTE. Each is based on a multi-table SELECT statement that includes an expression and a literal value in the column list.
The first INSERT statement uses a SELECT statement to derive the data from the source tables (
Employee , SalesPerson , and Person ) in the AdventureWorks2012 database and store the result set in the EmployeeSales table. The second INSERT statement uses the EXECUTE clause to call a stored procedure that contains the SELECT statement, and the third INSERT uses the EXECUTE clause to reference the SELECT statement as a literal string.
I. Using WITH common table expression to define the data inserted
The following example creates the
NewEmployee table in the AdventureWorks2012 database. A common table expression (EmployeeTemp ) defines the rows from one or more tables to be inserted into the NewEmployee table. The INSERT statement references the columns in the common table expression.
J. Using TOP to limit the data inserted from the source table
The following example creates the table
EmployeeSales and inserts the name and year-to-date sales data for the top 5 random employees from the table HumanResources.Employee in the AdventureWorks2012 database. The INSERT statement chooses any 5 rows returned by the SELECT statement. The OUTPUT clause displays the rows that are inserted into the EmployeeSales table. Notice that the ORDER BY clause in the SELECT statement is not used to determine the top 5 employees.
If you have to use TOP to insert rows in a meaningful chronological order, you must use TOP together with ORDER BY in a subselect statement as shown in the following example. The OUTPUT clause displays the rows that are inserted into the
EmployeeSales table. Notice that the top 5 employees are now inserted based on the results of the ORDER BY clause instead of random rows.
Specifying Target Objects Other Than Standard Tables
Examples in this section demonstrate how to insert rows by specifying a view or table variable.
K. Inserting data by specifying a view
The following example specifies a view name as the target object; however, the new row is inserted in the underlying base table. The order of the values in the
INSERT statement must match the column order of the view. For more information, see Modify Data Through a View.
L. Inserting data into a table variable
The following example specifies a table variable as the target object in the AdventureWorks2012 database.
Inserting Rows into a Remote Table
Examples in this section demonstrate how to insert rows into a remote target table by using a linked server or a rowset function to reference the remote table.
M. Inserting data into a remote table by using a linked server
The following example inserts rows into a remote table. The example begins by creating a link to the remote data source by using sp_addlinkedserver. The linked server name,
MyLinkServer , is then specified as part of the four-part object name in the form server.catalog.schema.object.
Applies to: SQL Server 2008 through SQL Server 2017.
N. Inserting data into a remote table by using the OPENQUERY function
The following example inserts a row into a remote table by specifying the OPENQUERY rowset function. The linked server name created in the previous example is used in this example.
Applies to: SQL Server 2008 through SQL Server 2017.
O. Inserting data into a remote table by using the OPENDATASOURCE function
The following example inserts a row into a remote table by specifying the OPENDATASOURCE rowset function. Specify a valid server name for the data source by using the format server_name or server_nameinstance_name.
Applies to: SQL Server 2008 through SQL Server 2017.
P. Inserting into an external table created using PolyBase
Export data from SQL Server to Hadoop or Azure Storage. First, create an external table that points to the destination file or directory. Then, use INSERT INTO to export data from a local SQL Server table to an external data source. The INSERT INTO statement creates the destination file or directory if it does not exist and the results of the SELECT statement are exported to the specified location in the specified file format. For more information, see Get started with PolyBase.
Applies to: SQL Server 2017.
Bulk Loading Data from Tables or Data Files
Examples in this section demonstrate two methods to bulk load data into a table by using the INSERT statement.
Q. Inserting data into a heap with minimal logging
The following example creates a new table (a heap) and inserts data from another table into it using minimal logging. The example assumes that the recovery model of the
AdventureWorks2012 database is set to FULL. To ensure minimal logging is used, the recovery model of the AdventureWorks2012 database is set to BULK_LOGGED before rows are inserted and reset to FULL after the INSERT INTO..SELECT statement. In addition, the TABLOCK hint is specified for the target table Sales.SalesHistory . This ensures that the statement uses minimal space in the transaction log and performs efficiently.
R. Using the OPENROWSET function with BULK to bulk load data into a table
The following example inserts rows from a data file into a table by specifying the OPENROWSET function. The IGNORE_TRIGGERS table hint is specified for performance optimization. For more examples, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK..) (SQL Server).
Applies to: SQL Server 2008 through SQL Server 2017.
Overriding the Default Behavior of the Query Optimizer by Using Hints
Examples in this section demonstrate how to use table hints to temporarily override the default behavior of the query optimizer when processing the INSERT statement.
Caution
Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.
S. Using the TABLOCK hint to specify a locking method
The following example specifies that an exclusive (X) lock is taken on the Production.Location table and is held until the end of the INSERT statement.
Applies to: SQL Server, SQL Database.
Capturing the Results of the INSERT Statement
Examples in this section demonstrate how to use the OUTPUT Clause to return information from, or expressions based on, each row affected by an INSERT statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements.
T. Using OUTPUT with an INSERT statement
The following example inserts a row into the
ScrapReason table and uses the OUTPUT clause to return the results of the statement to the @MyTableVar table variable. Because the ScrapReasonID column is defined with an IDENTITY property, a value is not specified in the INSERT statement for that column. However, note that the value generated by the Database Engine for that column is returned in the OUTPUT clause in the INSERTED.ScrapReasonID column.
U. Using OUTPUT with identity and computed columns
The following example creates the
EmployeeSales table and then inserts several rows into it using an INSERT statement with a SELECT statement to retrieve data from source tables. The EmployeeSales table contains an identity column (EmployeeID ) and a computed column (ProjectedSales ). Because these values are generated by the Database Engine during the insert operation, neither of these columns can be defined in @MyTableVar .
V. Inserting data returned from an OUTPUT clause
The following example captures data returned from the OUTPUT clause of a MERGE statement, and inserts that data into another table. The MERGE statement updates the
Quantity column of the ProductInventory table daily, based on orders that are processed in the SalesOrderDetail table in the AdventureWorks2012 database. It also deletes rows for products whose inventories drop to 0. The example captures the rows that are deleted and inserts them into another table, ZeroInventory , which tracks products with no inventory.
W. Inserting data using the SELECT option
The following example shows how to insert multiple rows of data using an INSERT statement with a SELECT option. The first
INSERT statement uses a SELECT statement directly to retrieve data from the source table, and then to store the result set in the EmployeeTitles table.
X. Specifying a label with the INSERT statement
The following example shows the use of a label with an INSERT statement.
Y. Using a label and a query hint with the INSERT statement
This query shows the basic syntax for using a label and a query join hint with the INSERT statement. After the query is submitted to the Control node, SQL Server, running on the Compute nodes, will apply the hash join strategy when it generates the SQL Server query plan. For more information on join hints and how to use the OPTION clause, see OPTION (SQL Server PDW).
See Also
BULK INSERT (Transact-SQL)
DELETE (Transact-SQL) EXECUTE (Transact-SQL) FROM (Transact-SQL) IDENTITY (Property) (Transact-SQL) NEWID (Transact-SQL) SELECT (Transact-SQL) UPDATE (Transact-SQL) MERGE (Transact-SQL) OUTPUT Clause (Transact-SQL) Use the inserted and deleted Tables
This question already has an answer here:
Lets say i have a string like Below
now i want to insert this in sql server 2012.i am trying with the below query
i'm getting output like below
what seems to be the problem? am i missing something?
MD TAHMID HOSSAINMD TAHMID HOSSAIN
69855 gold badges2020 silver badges4343 bronze badges
marked as duplicate by praveen, Mikael Eriksson, Johann Blais, Patrick Hofman, dic19Feb 13 '14 at 12:28
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
3 Answers
For sql server 2012 you can use format syntax ('YYYY-MM-DD hh:mm:ss') to insert datetime refer this link
FORMAT ( value, format [, culture ] )
FORMAT (@date, 'YYYY-MM-DD hh:mm:ss','en-US')
MalaMala
use 'yyyy-MM-dd HH:mm:ss' format ie. '2014-02-13 16:35:45'when you are using Convert function then you should provide date format number also. for example Convert(smalldatetime, '25/12/2014 15:24', 103). 103 is the date format of dd/MM/yyyy HH:mm
ShellShell
5,46766 gold badges2727 silver badges5959 bronze badges
To my knowledge, SQL-Server lacks the ability to convert a custom-formatted date string to datetime. It has some built-in formats, but often they don't match the format you have to deal with. So if for instance you get a string like '2/14/2014 11:59:59 PM' from a file, you must modify it with string operations (substring especially) first to get to a built-in format before you can use CONVERT (or even CAST) on it.
Here is the link to CONVERT and its known formats: http://msdn.microsoft.com/de-de/library/ms187928.aspx.
Thorsten KettnerThorsten Kettner
54.1k33 gold badges2727 silver badges4545 bronze badges
Not the answer you're looking for? Browse other questions tagged sql-server or ask your own question.Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |