How to generate a sql script to populate a table into production from developement? RSS

• • Reply
  

  AppDevForMe

  Participant

  1394 Points

  1327 Posts

  How to generate a sql script to populate a table into production from developement?

  Nov 16, 2012 01:34 AM|LINK

  Is their a way to select data into a sql query? For example if I have a table named WeekDays. Is their a way to generate a sql script which would re-create the WeekDays table? You see, I need to migrate a table of data into production, and would like to have a script recreate and populate the table for me.

  DayId    DayName

  1           Monday
  2           Tuesday
  3           Wedensday
  4          Thursday
  5          Friday
  6          Saturday
  7          Sunday

• • Reply
  

  sandeepmitta...

  Contributor

  6779 Points

  1058 Posts

  Re: How to generate a sql script to populate a table into production from developement?

  Nov 16, 2012 06:40 AM|LINK

  create  table WeekDays(
  	DayId int, DayName varchar(10)
  )
  
  ;with cte as (
  	select 1 as DayId, datename(dw, convert(datetime, '19000101')) as DayName
  	union all
  	select DayId + 1, datename(dw, convert(datetime, cast(19000101 + DayId as varchar))) from cte where DayId<7
  )
  insert into WeekDays
  select * from cte
  
  select * from WeekDays

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
• • Reply
  

  Sailaja Redd...

  Member

  452 Points

  81 Posts

  Re: How to generate a sql script to populate a table into production from developement?

  Nov 16, 2012 07:02 AM|LINK

  Hi ,

  Could you explain how 7 rows are inserted into cte with the following code.

  ;WITH CTE AS (
          SELECT 1 AS DAYID, DATENAME(DW, CONVERT(DATETIME, '19000101')) AS DAYNAME
          UNION ALL
          SELECT DAYID + 1, DATENAME(DW, CONVERT(DATETIME, CAST(19000101 + DAYID AS VARCHAR))) FROM CTE WHERE DAYID<7
  )

  Thanks in Advance,

  Sailaja.
  
  

  Thanks,
  Sailaja.
  MyTechnical Blog
  
• • Reply
  

  sandeepmitta...

  Contributor

  6779 Points

  1058 Posts

  Re: How to generate a sql script to populate a table into production from developement?

  Nov 16, 2012 10:48 AM|LINK

  Sailaja ReddyM

  Hi ,

  Could you explain how 7 rows are inserted into cte with the following code.

  ;WITH CTE AS (
          SELECT 1 AS DAYID, DATENAME(DW, CONVERT(DATETIME, '19000101')) AS DAYNAME
          UNION ALL
          SELECT DAYID + 1, DATENAME(DW, CONVERT(DATETIME, CAST(19000101 + DAYID AS VARCHAR))) FROM CTE WHERE DAYID<7
  )

  Thanks in Advance,

  Sailaja.

  this way is called recursive cte, here we are incrementing dayid by 1 in recursion till DAYID<7 to generate DAYID 1 to 7.

  For more on recursive CTE, also refer below links

  http://itdeveloperzone.blogspot.in/2012/11/generate-list-of-dates-in-sql-server.html
  http://itdeveloperzone.blogspot.in/2011/03/hierarchy-of-employees-in-sql-server.html

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Nov 16, 2012 10:52 AM
  • Edited by sandeepmittal11 on Nov 16, 2012 10:54 AM
  • Edited by sandeepmittal11 on Nov 16, 2012 10:55 AM
• • Reply
  

  AppDevForMe

  Participant

  1394 Points

  1327 Posts

  Re: How to generate a sql script to populate a table into production from developement?

  Nov 16, 2012 12:51 PM|LINK

  No this is not what I am after.

  Isn't their some way to generate a script which will contain the actual data from the table. Scripts can be generated to recreate a table. However I would like to have a script which would contain the hard coded data to be inserted into the database.

• • Reply
  

  limno

  All-Star

  117324 Points

  8002 Posts

  Moderator

  MVP

  Re: How to generate a sql script to populate a table into production from developement?

  Nov 16, 2012 02:01 PM|LINK

  You can generate script for your table structure and data from SSMS 2008 and above.

  Here is the one I generated for you:

  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WeekDays]') AND type in (N'U'))
  BEGIN
  CREATE TABLE [dbo].[WeekDays](
  	[DayId] [int] NULL,
  	[DayName] [varchar](10) NULL
  ) ON [PRIMARY]
  END
  GO
  SET ANSI_PADDING OFF
  GO
  INSERT [dbo].[WeekDays] ([DayId], [DayName]) VALUES (1, N'Monday')
  INSERT [dbo].[WeekDays] ([DayId], [DayName]) VALUES (2, N'Tuesday')
  INSERT [dbo].[WeekDays] ([DayId], [DayName]) VALUES (3, N'Wednesday')
  INSERT [dbo].[WeekDays] ([DayId], [DayName]) VALUES (4, N'Thursday')
  INSERT [dbo].[WeekDays] ([DayId], [DayName]) VALUES (5, N'Friday')
  INSERT [dbo].[WeekDays] ([DayId], [DayName]) VALUES (6, N'Saturday')
  INSERT [dbo].[WeekDays] ([DayId], [DayName]) VALUES (7, N'Sunday')

  
  
  

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
  • Marked as answer by AppDevForMe on Nov 20, 2012 04:23 AM
• • Reply
  

  sandeepmitta...

  Contributor

  6779 Points

  1058 Posts

  Re: How to generate a sql script to populate a table into production from developement?

  Nov 17, 2012 04:27 AM|LINK

  If you are using Sql Server 2008, then you can do it easily with Sql Server Management Studio. Refer below link to generate table script with data

  http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by AppDevForMe on Nov 20, 2012 04:22 AM
  • Edited by Chen Yu - MSFT on Nov 20, 2012 09:00 AM
  • Marked as answer by Chen Yu - MSFT on Nov 20, 2012 09:00 AM