SELECT INTO.....

• Ham

  SSC Veteran

  Points: 262

  More actions

  February 8, 2007 at 10:47 am

  #171337

  Hello,

  I have a table "DDE_DHQ" residing in database "EDPA", and I wish to copy some records from this table into a new table residing in another database named "EndoCohort". The two databases already exists in the DBMS. The new table that has to be created on the fly is called "Endo_Cohort_DHQ".

  This is what my query looks like so far...

  SELECT * INTO Endo_Cohort_DHQ

  FROM DDE_DHQ

  WHERE (SUBSTRING(Time_Stamp, 7, 4) = '2007')

  This works if the data is copied to a table in the working database, but I need to transfer data to a table residing in ANOTHER database ... how would I do that?

  Thanks for any help!!

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  February 8, 2007 at 11:02 am

  #688628

  Use the full naming convention, i.e. "EndoCohort.dbo.Endo_Cohort_DHQ". You can reference table objects like this:

  [ServerName].[DBName].[Owner/Schema].[Table].[Column]

• Robert Davis

  One Orange Chip

  Points: 28027

  More actions

  February 9, 2007 at 1:48 am

  #688747

  You don't need to use the ServerName if they are on the same server.

  Be aware that using Select Into has some pitfalls. For example, if a column has no values in it in the dataset returned by the query, the Select Into will try to create the column as 0 length causing it to fail. This is because SQL Server bases the new table schema on the dataset, not the underlying tables.

  Other pitfalls include:

  1. If a large column only contains small amounts of data in them, the new table will make the column much smaller which can cause errors later.
  2. Only the table is created. No dependent objects are created. No keys, constraints, defaults, indexes, triggers, etc.

  If you need the new table to be an exact replica of the original including all dependent objects, either use DTS or script out the table first and create it in the other database manually.

  If you only need to make sure that the table schema is correct, there is a way to trick SQL Server to base the new table off of the underlying table schema rather than the dataset returned by the query. You do the Select Into with a query that returns 0 records, and it will make an empty table that exactly matches the schema of the original table. Then simply insert the data using the real query.

  Like so:

  SELECT * INTO Database2.dbo.Endo_Cohort_DHQ

  FROM DDE_DHQ

  WHERE 1 = 2

  Insert INTO Database2.dbo.Endo_Cohort_DHQ

  SELECT *  FROM DDE_DHQ

  WHERE (SUBSTRING(Time_Stamp, 7, 4) = '2007')

  ---

  My blog: SQL Soldier[/url]
  SQL Server Best Practices: SQL Server Best Practices
  Twitter: @SQLSoldier
  My book: Pro SQL Server 2008 Mirroring[/url]
  Microsoft Certified Master: SQL Server, Data Platform MVP
  Database Engineer at BlueMountain Capital Management[/url]