May 18, 2017 at 3:09 am
I have table which will is having the table names in it. I have to now import those table in to new table using the tables name in it. I am new to SSIS can any help with this or Guide me. Thanks in advance.
May 18, 2017 at 3:16 am
I'm afraid I don't really see what it is your asking here. I appreciate English isn't your first language, however, I'm struggling to determine what your goal is. From what I understand you have a table which contains table names. Then, I'm not sure what it is you want to do, as you seem to be saying you simply want to put that data in a different table.
Do you, perhaps, have a table which contains a lot of data and each row has a table name next to it? You then want to create each table that is referenced in your table column, and then insert the data for those relevant tables from your master table?
Please explain further. Providing sample data, and what you expect to do with it may help greatly here.
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 18, 2017 at 3:30 am
Yes what you are saying is right.
I have table which has tables names in its column. What i have to do is use that names for the column then extract the data from that table and insert into master table.
Hope i am clearing my self to you?
May 18, 2017 at 3:45 am
Is the table of table names simply just that (a table, with one column, containing table names)? Are the surrogate tables and the master table exactly the same format? Is this a process that will be run more than once; if so, how will you avoid duplication of data (i.e. surrogate tables will be cleared after import, check record already exists (if so, how?))?
We can't see your tables, or data, so we need concise details on your set up, goal, and how you might want the process to work (even if you don't know how to implement it). 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 18, 2017 at 4:31 am
Is the table of table names simply just that (a table, with one column, containing table names)? Yes it is.
Are the surrogate tables and the master table exactly the same format? Yes it is.
Is this a process that will be run more than once? No.
example ;
I have a table A which contains multiple table names it. i am assigning it to a Variable @tablename in a foreach loop container
i now have use a table Variable @tablename to extract data for multiple tables
Select * from @tablename and insert it into a new table @newtable using SSIS
Insert into @newtablename
Select * from @tablename.
May 18, 2017 at 4:32 am
Thank you and appreciate your efforts in understanding the problem and helping me in solving it.
May 18, 2017 at 4:51 am
I'm not sure you'll really need SSIS to do this task then. you could achieve this my use Dynamic SQL.
If your tables really are as simple as you imply then something like this might well do the job:DECLARE @sql varchar(MAX);
SET @sql =
(SELECT CHAR(10) + 'INSERT INTO [MasterTable]' + CHAR(10) +
'SELECT * FROM [' + REPLACE(REPLACE(TN.Tablename,'[',''),']','') + '];' --Strip square brackets out as well, to try and avoid SQL injection
FROM [TableOfTableNames] TN
WHERE TN.Tablename IN (SELECT t.[name] from sys.tables t) --Check that the table is real to try and avoid SQL injection
FOR XML PATH(''));
PRINT (@SQL); --Check the SQL
--EXEC @sql; --Commented out, as this is not going to work on my environment
This assumes that all the tables are on the same database, and the same (and default) schema. Perhaps this might get you on the right path.
Does this help at all?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 18, 2017 at 4:58 am
I had done this how ever i have to achieve this using SSIS and add a Derived column for it? is there a way to pass my tablename to variable and add a derived column to it and then insert the data to new table using the mapping ?
May 18, 2017 at 5:02 am
syedmasood.f - Thursday, May 18, 2017 4:58 AMI had done this how ever i have to achieve this using SSIS and add a Derived column for it? is there a way to pass my tablename to variable and add a derived column to it and then insert the data to new table using the mapping ?
You could run the T-SQL using an Execute SQL Task in the Control Flow. Why does this need to be done in SSIS using a dataflow, when T-SQL alone could complete the task at hand? Are there further requirements?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 18, 2017 at 7:20 am
yes i want to add a derived column to it to assign a unique identifier
May 18, 2017 at 7:32 am
syedmasood.f - Thursday, May 18, 2017 7:20 AMyes i want to add a derived column to it to assign a unique identifier
Will these be made up of column values, or is it simply an incrementing integer value? You can still achieve this in T-SQL.
For example, if it's something like an amalgamation of column values + table name you could change the SQL to be:DECLARE @sql varchar(MAX);
SET @sql =
(SELECT CHAR(10) + 'INSERT INTO [MasterTable]' + CHAR(10) +
--CONVERT not required if column already is a varchar.
'SELECT TN.TableName+CONVERT(varchar(20),[YourColumn])+CONVERT(varchar(20),[YourOtherColumn]),* FROM [' + REPLACE(REPLACE(TN.Tablename,'[',''),']','') + '];' --Strip square brackets out as well, to try and avoid SQL injection.
FROM [TableOfTableNames] TN
WHERE TN.Tablename IN (SELECT t.[name] from sys.tables t) --Check that the table is real to try and avoid SQL injection
FOR XML PATH(''));
PRINT (@SQL); --Check the SQL
--EXEC @sql; --Commented out, as this is not going to work on my environment
If it's simply an INDENTITY column, then:DECLARE @sql varchar(MAX);
SET @sql =
(SELECT CHAR(10) + 'INSERT INTO [MasterTable] ([YourColumn],[YourNextColumn]/*And the rest of your columns, but excluding the ID column, in the order they are in in your surrogate tables)*/)' + CHAR(10) +
'SELECT * FROM [' + REPLACE(REPLACE(TN.Tablename,'[',''),']','') + '];' --Strip square brackets out as well, to try and avoid SQL injection.
FROM [TableOfTableNames] TN
WHERE TN.Tablename IN (SELECT t.[name] from sys.tables t) --Check that the table is real to try and avoid SQL injection
FOR XML PATH(''));
PRINT (@SQL); --Check the SQL
--EXEC @sql; --Commented out, as this is not going to work on my environment
The key thing is to keep things simple. There's really no need to use SSIS to complicate it here. If it is more complex, then I'm missing some of the picture and you'll need to elaborate and provide samples.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 19, 2017 at 5:13 am
If you must use SSIS to do this, you can use an Execute SQL task to read the list of table names into an object variable.
Then use a Foreach loop container, with that object variable as the Enumerator. Inside the Foreach loop you do your file processing.
I'm not sure how you intend to create a unique identifier using a derived column, but perhaps it could be done.
May 19, 2017 at 5:24 am
Yes i am able to as you guided thanks to both for help and guidance. special thanks to Thom for understanding the requirement and help me thank you both.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply