September 12, 2017 at 11:01 pm
Hai all,
Im newbie in here and I want to ask about can we create Stored Procedure Looping to insert table from Source to target ?
If possible..
I want to copy data from
example: Source1 : select * From [master].[los].name_1
it will result column : name,age,address
Source2 : select * From [master].[los].job_2
it will result column : position,start_work,end_work
Target1 : [tempdb].[dblos].name_1
with the same column : name,age,address
Target2: [tempdb].[dblos].job_2
it will result column : position,start_work,end_work
Copy from Source1 to Target1 then Source2 to Target2, and so on
thank you very much for your help..
September 13, 2017 at 2:34 am
Before anything, I do need to ask, why are you storing data in the system databases? The master database should not be used to store your own data; you should create your own database and store the data in that. The same also applies TempDB, however, this is even worse. TempDB is rebuilt EVERY TIME you start the instance. This means that if you create a bunch of tables in TempDB, and store data it in,when you restart your instance/reboot the server/lose power/etc that data is GONE. You NEED to store your data elsewhere, in your own database.
I don't know where looping comes into your question, but for "copying" data look up the INSERT INTO...SELECT FROM and SELECT... INTO... Syntaxes on your preferred search engine. That should give you a head start. If you don't understand what it's telling you to do, post back here with questions. However, please PLEASE consider storing your data elsewhere; you're asking for a world of hurt right now.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 13, 2017 at 3:26 am
Thom A - Wednesday, September 13, 2017 2:34 AMBefore anything, I do need to ask, why are you storing data in the system databases? The master database should not be used to store your own data; you should create your own database and store the data in that. The same also applies TempDB, however, this is even worse. TempDB is rebuilt EVERY TIME you start the instance. This means that if you create a bunch of tables in TempDB, and store data it in,when you restart your instance/reboot the server/lose power/etc that data is GONE. You NEED to store your data elsewhere, in your own database.I don't know where looping comes into your question, but for "copying" data look up the INSERT INTO...SELECT FROM and SELECT... INTO... Syntaxes on your preferred search engine. That should give you a head start. If you don't understand what it's telling you to do, post back here with questions. However, please PLEASE consider storing your data elsewhere; you're asking for a world of hurt right now.
Hai Thom,
Yes you're right about it.
Its just example..
I didn't really put the data at that system database..
I put it at database A and want to copy into Database B in 1 server
September 13, 2017 at 3:41 am
unas_sasing - Wednesday, September 13, 2017 3:26 AMHai Thom,
Yes you're right about it.
Its just example..
I didn't really put the data at that system database..I put it at database A and want to copy into Database B in 1 server
Ok, well, like i said, have a look at INSERT INTO... SELECT FROM... and SELECT ... INTO... FROM... . The former inserts data into an existing table, while the latter creates and inserts data into a new table. So it depends what you require.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 13, 2017 at 4:16 am
Thom A - Wednesday, September 13, 2017 3:41 AMunas_sasing - Wednesday, September 13, 2017 3:26 AMHai Thom,
Yes you're right about it.
Its just example..
I didn't really put the data at that system database..I put it at database A and want to copy into Database B in 1 server
Ok, well, like i said, have a look at INSERT INTO... SELECT FROM... and SELECT ... INTO... FROM... . The former inserts data into an existing table, while the latter creates and inserts data into a new table. So it depends what you require.
I try like this declare @TABLE_NAME VARCHAR(MAX)
declare @SOURCE VARCHAR(MAX)
declare @COUNT INT
declare @SELECT_COUNT VARCHAR(MAX)
declare @SELECT NVARCHAR(MAX)
declare @truncate NVARCHAR(MAX)
declare @IS_EXEC CHAR (1)
DECLARE @EXEC SQL_VARIANT
DECLARE @SOURCE_SI_DB_Name VARCHAR (50)
DECLARE @SOURCE_SI_Schema_Name VARCHAR (50)
DECLARE @SOURCE_SI_TABLE_Name VARCHAR (50)
SET @truncate = ''
SET @SELECT = ''
SET @IS_EXEC = @EXEC
SET @COUNT = 0
--UPDATE UDM.[DBO].[Log_Data]
--SET [RowCount] = 0,StartTime = NULL,EndTime = NULL,Status = ''
DECLARE @sTarget_INP_DB_Name VARCHAR(50), @sTarget_INP_Schema_Name VARCHAR(50), @sTarget_INP_Table_Name VARCHAR(50), @sTgtColumnList VARCHAR(50)
EXECUTE [Usp_getschemainfo]
@sTarget_INP_DB_Name,
@sTarget_INP_Schema_Name,
@sTarget_INP_Table_Name,
'',
@sTgtColumnList output;
PRINT @sTarget_INP_DB_Name
PRINT @sTarget_INP_Schema_Name
PRINT @sTarget_INP_Table_Name
PRINT @sTgtColumnList
begin try
--SELECT TABLE_NAME,Source FROM UDM.DBO.[Log_Data]
--WHERE [Status] <> 'SUCCESS'
--cleansing datafeed
DECLARE CRS CURSOR
FOR
--SELECT ID FROM UDM.DBO.[Log_Data] WHERE Active = 1
----SELECT * FROM DWIM_INP.IN_PROC_LOS.INP_1017_17001_ALTFLAG
----select * from DWIM_XFM.[XFM_LOS].[XFM_1017_17001_ALTFLAG]
select * INTO #SCHEMAINFO
from Ptbc_Dwh_Schema_Info
order by columnseq
--select * from dwim_srce_img..syscolumns
SELECT DISTINCT DATABASENAME,TABLENAME,COLUMNNAMES,B.NAME
--SELECT *
FROM #SCHEMAINFO A
INNER JOIN dwim_srce_img..syscolumns B ON COLUMNNAMES = NAME
WHERE DATABASENAME='DWIM_INP' AND SCHEMANAME = 'IN_PROC_LOS'
DECLARE @TBL NVARCHAR(1000)
OPEN CRS
FETCH NEXT FROM CRS INTO @TBL
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TABLE_NAME = TABLE_NAME,@SOURCE = DWIM_XFM FROM XFM_LOS
--WHERE ID = @TBL
--select @truncate = 'TRUNCATE TABLE '+@TABLE_NAME
IF @IS_EXEC = 'Y'
BEGIN
EXEC (@truncate)
END
ELSE
BEGIN
PRINT(@truncate)
END
SELECT @SELECT =
'INSERT INTO [' + @sTarget_INP_DB_Name + '].[' + @sTarget_INP_Schema_Name + '].[' + @sTarget_INP_Table_Name + ']' + '
( ' + @sTgtColumnList + ' ) ' +
'SELECT '+ @sSrcColumnList + 'FROM ' + @SOURCE_SI_DB_Name + '.' + @SOURCE_SI_Schema_Name + '.' + @SOURCE_SI_TABLE_Name + ' as Source with(nolock); '
SET @sSQLTxtAll = @sSQLTxtAll + @sSQLTxt + ' ; '
EXEC (@sSQLTxtAll)
IF @IS_EXEC = 'Y'
BEGIN
--UPDATE UDM.DBO.[Log_Data]
--SET StartTime = GETDATE(), EndTime = NULL,[Status] = 'RUNNING'
--WHERE ID = @TBL
EXEC (@SELECT)
--EXEC (@SELECT_COUNT)
--UPDATE UDM.DBO.[Log_Data]
--SET [RowCount] = @@ROWCOUNT, EndTime = GETDATE(),[Status] = 'SUCCESS'
--WHERE ID = @TBL
END
ELSE
BEGIN
PRINT(@SELECT)
PRINT(@SELECT_COUNT)
END
--exec master..xp_cmdshell @del
FETCH NEXT FROM CRS INTO @TBL
END
CLOSE CRS
end try
begin catch
UPDATE UDM.DBO.[Log_Data]
SET [RowCount] = NULL, EndTime = GETDATE(),[Status] = ERROR_MESSAGE()
WHERE ID = @TBL
end catch
--exec [dbo].[SP_FCP_SAM_POPULATION_GROUP]
--exec [dbo].[SP_FCP_TRANSACTION_CODE]
--exec [dbo].[SP_PRE_FCP_TRANSACTIONS]
--exec dbo.SP_PRE_FCP_ACCOUNT
DEALLOCATE CRS
September 13, 2017 at 5:04 am
declare @TABLE_NAME VARCHAR(MAX)
declare @SOURCE VARCHAR(MAX)
declare @COUNT INT
declare @SELECT_COUNT VARCHAR(MAX)
declare @SELECT NVARCHAR(MAX)
declare @truncate NVARCHAR(MAX)
declare @IS_EXEC CHAR (1)
DECLARE @EXEC VARCHAR (100)
DECLARE @SOURCE_SI_DB_Name VARCHAR (50)
DECLARE @SOURCE_SI_Schema_Name VARCHAR (50)
DECLARE @SOURCE_SI_TABLE_Name VARCHAR (50)
DECLARE @sSrcColumnList VARCHAR(100)
DECLARE @sSQLTxtAll VARCHAR (100)SET @truncate = ''
SET @SELECT = ''
SET @IS_EXEC = @EXEC
SET @COUNT = 0
SET @SOURCE_SI_DB_Name = 'DWIM_SRCE_IMG'
SET @SOURCE_SI_Schema_Name ='SRCE_IMG_LOS'
SET @SOURCE_SI_TABLE_Name = 'SIM_17_001_ALTFLAG'
DECLARE @sTarget_INP_DB_Name VARCHAR(50), @sTarget_INP_Schema_Name VARCHAR(50), @sTarget_INP_Table_Name VARCHAR(50), @sTgtColumnList VARCHAR(50)
EXECUTE [Usp_getschemainfo]
@sTarget_INP_DB_Name,
@sTarget_INP_Schema_Name,
@sTarget_INP_Table_Name,
'',
@sTgtColumnList output;
PRINT @sTarget_INP_DB_Name
PRINT @sTarget_INP_Schema_Name
PRINT @sTarget_INP_Table_Name
PRINT @sTgtColumnList
BEGIN
DROP TABLE #SCHEMAINFO
select * INTO #SCHEMAINFO
from Ptbc_Dwh_Schema_Info
order by columnseq
--cleansing datafeed
DECLARE CRS CURSOR
FOR
SELECT DISTINCT DATABASENAME,TABLENAME,COLUMNNAMES,B.NAME
--SELECT *
FROM #SCHEMAINFO A
INNER JOIN dwim_srce_img..syscolumns B ON COLUMNNAMES = NAME
WHERE DATABASENAME= @SOURCE_SI_DB_Name AND SCHEMANAME = @SOURCE_SI_Schema_Name AND TABLENAME= @SOURCE_SI_TABLE_Name
AND COLUMNNAMES NOT IN ('EFFECTIVE_DATE','EXPIRY_DATE')
DECLARE @TBL NVARCHAR(1000)
OPEN CRS
FETCH NEXT FROM CRS INTO @sSrcColumnList--@TBL
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sSrcColumnList = (SELECT DISTINCT COLUMNNAMES
FROM #SCHEMAINFO A
INNER JOIN dwim_srce_img..syscolumns B ON COLUMNNAMES = NAME
WHERE DATABASENAME= @SOURCE_SI_DB_Name AND SCHEMANAME = @SOURCE_SI_Schema_Name AND TABLENAME= @SOURCE_SI_TABLE_Name
AND COLUMNNAMES NOT IN ('EFFECTIVE_DATE','EXPIRY_DATE'))
SELECT @SELECT =
'INSERT INTO [' + @sTarget_INP_DB_Name + '].[' + @sTarget_INP_Schema_Name + '].[' + @sTarget_INP_Table_Name + ']' + '
( ' + @sTgtColumnList + ' ) ' +
'SELECT '+ @sSrcColumnList + 'FROM ' + @SOURCE_SI_DB_Name + '.' + @SOURCE_SI_Schema_Name + '.' + @SOURCE_SI_TABLE_Name + ' as Source with(nolock); '
EXEC (@sSQLTxtAll)
IF @IS_EXEC = 'Y'
BEGIN
EXEC (@SELECT)
END
ELSE
BEGIN
PRINT(@SELECT)
PRINT(@SELECT_COUNT)
END
FETCH NEXT FROM CRS INTO @sSrcColumnList--@TBL
END
END
CLOSE CRS
DEALLOCATE CRS
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply