Wsrtjtyk

Please assist with ideas on how I can make this script dynamic to be able to loop through all tables in a database and do the same thing. The script renames a database with suffix '_Old' then crates a new database. It then copies tables into the new database and moves the data into the newly created table.

I am not good with scripting so I have no idea how to tackle this challenge. Ay help is welcome.

USE Test

GO

-- 1. RENAME CURRENT TABLE

EXEC sp_rename 'dbo.Customer', 'Customer_Old'

-- 2. CREATE NEW TABLE

 CREATE TABLE [dbo].[Customer](

 [co_cde] [smallint] NULL,

 [cust_no] [varchar](50) NOT NULL,

 [customer_key] [bigint] NULL,

 [UCN] [bigint] NULL,

 [main_system] [varchar](12) NOT NULL,

 [premium_model] [varchar](4) NULL,

 [prim_ofcr_ind] [varchar](5) NULL,

 [prim_ofcr_ind0] [varchar](5) NULL,

 [current_model] [varchar](5) NULL,

 [new_model] [varchar](5) NULL,

 [legal_type] [varchar](200) NULL,

 [legal_name] [varchar](255) NULL,

 [fullname] [varchar](85) NULL,

 [title] [varchar](200) NULL,

 [initials] [varchar](50) NULL,

 [firstname] [varchar](100) NULL,

 [surname] [varchar](100) NULL,

 [id_no] [bigint] NULL,

 [date_of_birth] [date] NULL,

 [age] [smallint] NULL,

 [deceased_date] [date] NULL,

 [cust_rec_open_date] [date] NULL,

 [cust_type] [varchar](2) NULL,

 [customer_type] [varchar](40) NULL,

 [source_cust_type] [varchar](40) NULL,

 [cust_stat] [varchar](12) NULL,

 [gender] [varchar](1) NULL,

 [race] [varchar](10) NULL,

 [email_addr] [varchar](124) NULL,

 [cell_no] [varchar](20) NULL,

 [cntct_tel_no] [varchar](20) NULL,

 [pers_tel_no] [varchar](20) NULL,

 [bus_tel_no] [varchar](20) NULL,

 [pri_seg] [varchar](3) NULL,

 [pri_sub_seg] [varchar](3) NULL,

 [income_amt] [numeric](21, 2) NULL,

 [income_estimate] [numeric](21, 2) NULL,

 [mth_cr_turnover] [numeric](21, 2) NULL,

 [premium_income] [numeric](21, 2) NULL,

 [zip_cde] [varchar](27) NULL,

 [branch_cde] [smallint] NULL,

 [rsk_cat_cde] [varchar](1) NULL,

 [grad_type] [varchar](9) NULL,

 [high_edu_lvl] [varchar](9) NULL,

 [qual_speciality] [varchar](9) NULL,

 [kyc_ind] [varchar](3) NULL,

 [fr_rating] [varchar](9) NULL,

 [bank_relationship] [varchar](6) NULL,

 [bank_relationship_type] [varchar](12) NULL,

 [med_pages] [smallint] NULL,

 [fast_mover] [smallint] NULL,

 [hypersegmentation] [smallint] NULL,

 [income_model] [varchar](25) NULL,

 [nav_income_band] [varchar](50) NULL,

 [VSI] [smallint] NULL,

 [main_banked] [smallint] NULL,

 [household_key] [bigint] NULL,

 [household_relationship] [varchar](20) NULL,

 [household_category] [varchar](25) NULL,

 [mkt_cell] [varchar](3) NULL,

 [mkt_sms] [varchar](3) NULL,

 [mkt_phone] [varchar](3) NULL,

 [mkt_post] [varchar](3) NULL,

 [mkt_email] [varchar](3) NULL,

 [mkt_fnb] [varchar](3) NULL,    

 [qpwr] [bit] NULL,

 [qpwf] [bit] NULL,

 [qpcf] [bit] NULL,

 [qpbf] [bit] NULL,

 [qpwfs] [bit] NULL,

 [qpcfs] [bit] NULL,

 [qpbfs] [bit] NULL

 ) ON [PRIMARY] 

GO

-- 3. GET COLUMNS FROM CURRENT TABLE (dbo.Customer_Old)

 IF OBJECT_ID('tempdb..#tbl_current') IS NOT NULL DROP TABLE #tbl_current

 CREATE TABLE #tbl_current (id TINYINT IDENTITY(1,1) NOT NULL, column_name VARCHAR(30) NOT NULL)



INSERT INTO #tbl_current

SELECT column_name  

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'Customer_Old' and table_schema = 'dbo'

-- 4. GET COLUMNS FROM NEW TABLE (dbo.Customer)

IF OBJECT_ID('tempdb..#tbl_new') IS NOT NULL DROP TABLE #tbl_new



CREATE TABLE #tbl_new (id TINYINT IDENTITY(1,1) NOT NULL, column_name VARCHAR(30) NOT NULL)



INSERT INTO #tbl_new SELECT column_name  

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'Customer' and table_schema = 'dbo'

-- 5. BUILD INSERT AND SELECT STATEMENT

IF OBJECT_ID('tempdb..#final_columns') IS NOT NULL DROP TABLE #final_columns

  SELECT a.column_name AS old_col,

  b.column_name AS new_col,

   CASE WHEN a.column_name IS NULL     

  THEN b.column_name



 WHEN b.column_name IS NOT NULL THEN a.column_name

  WHEN b.column_name IS NULL THEN NULL         

 END AS new_columns,

  CASE 

  WHEN a.column_name IS NULL THEN 'NULL AS ' + b.column_name

  WHEN b.column_name IS NOT NULL THEN b.column_name +' AS '+ a.column_name

  WHEN b.column_name IS NULL       THEN NULL        

  END 

 AS col_sel   INTO #final_columns    

   FROM #tbl_current a

  FULL JOIN #tbl_new b

  ON a.column_name = b.column_name



 DECLARE @rowcnt TINYINT, @sql VARCHAR(MAX)



 IF OBJECT_ID('tempdb..#tbl_final') IS NOT NULL DROP TABLE #tbl_final

  CREATE TABLE #tbl_final (id TINYINT IDENTITY(1,1) NOT NULL, column_name VARCHAR(30) NOT NULL, column_insert VARCHAR(50) NOT NULL)



INSERT INTO #tbl_final

SELECT new_columns AS column_name,

col_sel     AS column_insert

FROM #final_columns

WHERE new_columns IS NOT NULL



SET @rowcnt = @@rowcount 



IF @rowcnt > 0

 BEGIN

DECLARE @i TINYINT = 1, 

    @tbl_columns VARCHAR(MAX) = '',

    @ins_columns VARCHAR(MAX) = ''



WHILE @i <= @rowcnt

BEGIN

IF @i = 1 

BEGIN

    SET @tbl_columns = (SELECT column_name   FROM #tbl_final WHERE id = @i)

    SET @ins_columns = (SELECT column_insert FROM #tbl_final WHERE id = @i)

END

ELSE BEGIN

    SET @tbl_columns = @tbl_columns + ', ' + (SELECT column_name   

FROM #tbl_final WHERE id = @i)

    SET @ins_columns = @ins_columns + ', ' + (SELECT column_insert 

FROM #tbl_final WHERE id = @i)

END

SET @i = @i + 1

END



PRINT @tbl_columns

PRINT @ins_columns   

 END



SET @sql = 'INSERT INTO dbo.Customer ('+ @tbl_columns +')

    SELECT '+ @ins_columns +'

 FROM dbo.Customer_Old   

   '

 PRINT @sql

 --EXEC(@sql)

You could also use SELECT INTO statement. Build up your table names in a cursor, which loops the system.objects or INFORMATION_SCHEMA.TABLES. It will use the same column names and types when you keep the original name (no alias).

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=sql-server-2017