INSERT INTO vs SELECT INTO

by

  1. They do different things. Use INSERT when the table exists. Use SELECT INTO when it does not.

  2. Yes. INSERT with no table hints is normally logged. SELECT INTO is minimally logged assuming proper trace flags are set.

  3. In my experience SELECT INTO is most commonly used with intermediate data sets, like #temp tables, or to copy out an entire table like for a backup. INSERT INTO is used when you insert into an existing table with a known structure.

EDIT

To address your edit, they do different things. If you are making a table and want to define the structure use CREATE TABLE and INSERT. Example of an issue that can be created: You have a small table with a varchar field. The largest string in your table now is 12 bytes. Your real data set will need up to 200 bytes. If you do SELECT INTO from your small table to make a new one, the later INSERT will fail with a truncation error because your fields are too small.

More Related Contents:

  1. Comma separated results in SQL
  2. How to check if a column exists in a SQL Server table
  3. Understanding PIVOT function in T-SQL
  4. Using T-SQL, return nth delimited element from a string
  5. Get first day of week in SQL Server
  6. Using RegEx in SQL Server
  7. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  8. Confused about UPDLOCK, HOLDLOCK
  9. SQL Server 2008 Empty String vs. Space
  10. How do I use the CONCAT function in SQL Server 2008 R2?
  11. How to set a default value for an existing column
  12. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  13. TSQL: How to convert local time to UTC? (SQL Server 2008)
  14. SQL Server: combining multiple rows into one row
  15. How do I find a stored procedure containing ?
  16. SQL Recursive CTE: Finding objects linked by property
  17. How to convert number of minutes to hh:mm format in TSQL?
  18. Convert decimal time to hours and minutes
  19. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  20. How do I move a table into a schema in T-SQL
  21. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  22. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  23. How to wait for 2 seconds?
  24. What are row, page and table locks? And when they are acquired?
  25. Generate MD5 hash string with T-SQL
  26. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  27. Select statement to find duplicates on certain fields
  28. Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)
  29. Merge multiple rows into a single row
  30. SQL Server SELECT INTO @variable?

Leave a Comment