Link to home
Start Free TrialLog in
Avatar of Sh M
Sh MFlag for United States of America

asked on

sql server, passing table name as a parameter to query

Hi,

I am trying to get the following query works but I keep getting all sort of error messages related to parameters I passed for table name. Then I tried to use a select stmt (where II have the parameter commented out) but still doesn't read the results select query as a table name....

how can this be fixed?

Here is the query:

 
Insert into TblSource (oldTableName)
Select Name From sys.tables 
Where type_desc ='USER_TABLE'
AND (Name like 'emp%')


Declare   @ID int
		, @srcTblName nvarchar(50)
		, @country    varchar(3)
		, @desTblName varchar(50)
		, @loopCntr	  int


Set @loopCntr = @@ROWCOUNT 
While @loopCntr	<> 0
Begin

Set @ID	= ( Select ID From TblSource Where ID = @loopCntr	)

	Set @srcTblName	= (	Select oldTableName
							From TblSource 
							Where  ID = @ID)  
											
	
	Set @Country	= (	Select Country
							From tblCountry 
							Where C_Abr = (	CASE substring(@srcTblName,6,2) 
													 WHEN 'U' THEN 'US'
													 WHEN 'U' THEN 'UK'
												END))
		
Set @desTblName = RIGHT(@srcTblName,LEN(@srcTblName) 	--don't pay much attention to this formula, imagin it works fine and return correct name.
											
	Update TblSource 
	Set Country	= @Country
	, desTblName	= @desTblName
	Where ID		= @ID
	
	Set @ID = (Select ID From TblSource Where oldTblName = 'employee') 
	set @srcTblName =(Select oldTblName From TblSource Where ID = @ID )
	If (@ID <> '')
	Begin
			
			Insert Into @desTblName (
						location_ID,
                        [country],
                        [Active]) 
                                    
			Select		Distinct (s.location) 
					    ,@Country
                        1
                        			
			From   (Select oldTblName From TblSource Where ID = @ID) AS s --@srcTblName --
			End 								
									
	End

	set @loopcntr = @loopcntr -1
	
END

Open in new window


Thanks in advance
Avatar of HainKurt
HainKurt
Flag of Canada image

you cannot use this

Insert Into @desTblName ...

instead use dynamic sql liek this:

declare @sql varchar(max) = "Insert Into " + @desTblName + " ...";
exec(@sql)
also this one looks wrong

Select Distinct (s.location),@Country, 1
From (Select oldTblName From TblSource Where ID = @ID) AS s --@srcTblName --

s subquery does not have s.location
Avatar of mmr159
mmr159

Some notes:
- This is not a "query."  Yes, it contains queries, but it also contains much more than that.  This is procedural code...
- It is very difficult for me to read your code.  Please use proper capitalization, consistent indentation, etc.


You cannot SELECT x,y,x FROM @variable_name.
You cannot SELECT x,y,x FROM table_name AS @variable_name.

In the case where you've commented out --@srcTblName... I don't even know why this is there.
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sh M

ASKER

HainKurt,

Are you actually using a single double quote or two single quote here?

declare @sql varchar(max) = "Insert Into " + @desTblName + " (location_ID,[country],[Active]) Select Distinct location, @Country, 1 From " + @oldTblName";
exec(@sql)
Avatar of Sh M

ASKER

mmr159
another try. I added comments
--tblsource has the following columns:
-- , @srcTblName	nvarchar(50)
-- , @country		nvarchar(3)
-- , @desTblName	nvarchar(50)

INSERT INTO tblSource (oldTableName)
SELECT name From sys.tables 
WHERE type_desc ='USER_TABLE'
AND (name LIKE 'emp%')

DECLARE		@id				int
			, @srcTblName	nvarchar(50)
			, @country		nvarchar(3)
			, @desTblName	nvarchar(50)
			, @loopCntr		int


SET	@loopCntr =	@@ROWCOUNT 
WHILE	@loopCntr	<>	0

BEGIN
SET	@ID = (SELECT id FROM tblSource WHERE id = @loopCntr)
SET	@srcTblName	= (	SELECT oldTableName
					FROM tblSource 
					WHERE id = @id)
  									
SET	@Country = (SELECT Country
				FROM tblCountry 
				WHERE C_Abr = (CASE substring(@srcTblName,6,2) 
										WHEN 'U' THEN 'US'
										WHEN 'U' THEN 'UK'
										END))		
SET	@desTblName = RIGHT(@srcTblName,LEN(@srcTblName) 	--don't pay much attention to this formula, imagin it works fine and return correct name.
	

	
-- I am populating the tblSource with a list of the
-- tables that are available for exporting their data.
-- the name of all the table sources and their table destinations will be stored in tblSource.
										
UPDATE	tblSource 
SET	country	= @country, desTblName	= @desTblName
WHERE id = @id


-- from all user tables saved to the tblsource I need to extract those which
-- has their destination as employee table.
-- I may have 3 src table X , Y and Z to export data from and all imported to employee table.
SET @id = (SELECT id FROM tblSource WHERE desTblName = 'employee') 
SET @srcTblName = (SELECT oldTblName FROM tblSource WHERE id = @id )
	
IF (@id <> '')
BEGIN
			
	INSERT INTO @desTblName (
			location_ID
			,[country]
			,[Active]) 
                                    
	SELECT DISTINCT	location
			,@Country

    -- I tried to use: FROM @srcTblName  but did not work 
	-- however the line below also is not working...
	-- I was hoping to pass the tablename to the FROM but not possible
    FROM  (SELECT oldTblName FROM tblSource WHERE id= @id) AS s 
END

SET @loopcntr = @loopcntr - 1 -- looping because I may have X, Y, Z tables....
	
END

Open in new window

Avatar of Alpesh Patel
Before execute just print and see the query is build proper. copy paste the query and execute it. it will show you all errors.

One thing when you uses inline query use ('') two single quote in place of single. and when you pass char values use 3 quotes.
>    another try. I added comments

Better.  My previous comment applies to this question but more importantly, coding in general.  You will appreciate clean, structured, commented code that you have to look at months or years down the road.

>    -- I tried to use: FROM @srcTblName  but did not work >
>    -- however the line below also is not working...
>    -- I was hoping to pass the tablename to the FROM but not possible
>    FROM  (SELECT oldTblName FROM tblSource WHERE id= @id) AS s

So if I understand the task, you are populating tblSource with tables in a database that begin with 'emp'.  You then determine the Country and destTblName and update tblSource for each record.  Finally, you insert the updated information into destTblName, which is the exact name as srcTblName???:

SET      @desTblName = RIGHT(@srcTblName,LEN(@srcTblName)

Also, this doesn't look right:

CASE substring(@srcTblName,6,2)
                                                            WHEN 'U' THEN 'US'
                                                            WHEN 'U' THEN 'UK'
                                                            END)

1) You have two options being compared to the same value.
2) You're selecting 2 characters out of a string and comparing them to 1.

-------------------

To answer your question, see HainKurt's answer.  You must use dynamic SQL to select from a variable that contains a table name, like

DECLARE @s VARCHAR(8000),
    @table_name VARCHAR(128)
SET @table_name = 'this_is_my_table_name'
SET @s = 'SELECT col1,col2,colx FROM ''' + @table_name + ''''
EXEC @s
-- PRINT @s -- If you get errors, print @s to see what it is, run in query analyzer or the like to debug.

-------------------

More importantly, I do not see why this has to be done with procedural code.  It really looks like you are doing a lot of work for a simple task.  This will result in slower, tough-to-maintain, buggy code.  Again, maybe you know something I don't.  But as I see it, this task should be done set-based.  Does the amount of tables that begin with emp really change that much?  How often are you doing this procedure?
sorry it should be a single ' :)

declare @sql varchar(max) = 'Insert Into ' + @desTblName + ' (location_ID,[country],[Active]) Select Distinct location, @Country, 1 From ' + @oldTblName;
exec(@sql);
did you ask the same question multiple times? I do remember the same or similar question before...
Avatar of Sh M

ASKER

hainkurt-- Haven't used this type of parametrized query before(if that is a correct name for it) and not sure if errors are related to the same issue. I actually find out that the problem is partially related to some of the tables not having the column names I listed in the insert statement. I probabely opena new ticket for it not to confuse anyone

mmr159: I have about 7 access databases, each have about 20 tables..about 6 years of data... they all need to be exported to a single sql database. It is going to be one time import but at the time of import I need to check if certain records do not exist in my current sql lookup table and then add them ( I have already populated the look up tables using latest database)....so it is not possible to use any tool to automate it..I think.

One of the issues I have and I was just telling HainKurt that I will raise a new ticket for it, (okay I ask my question here) is the fact that table Emp1, Emp2 and Emp3 may be different in some of the columns. example emp1 has Age column while Emp2 does not have it OR has it with diffeerent column name....and hence my yesterday questions as they cause issue because the column does not exist in the a given table....

I could get the procedural query I posted yesterday working except the issue I just explained...
I tried to get it working using code attached but it is still not working... I appreciate any sugestions:



Declare @age varchar(5)
, @age_Limit varchar(11)
SET @aGE = 'age'
SET @age_Limit ='age limit'

IF (@id <> '')
BEGIN
	

SET @counter = @@ROWCOUNT 

WHILE (@counter <> 0)
BEGIN

......		
	
Select @strSql = 'INSERT INTO MyDB.dbo.[' + @desTableName + '] (
			[Period]  
		,	[location]
		,	[Age]	)
                                    
	SELECT
		 ''' + @Type + ''' As Type			
	,	''' + @Period + '''	AS Period	
    ,   [location]	' +
	-- @type can be either xxxx or yyyy
    ', CASE WHEN ''' + @Type + ''' = ''XXXX'' 
						THEN
						(	(IF Exists(select [' + @Age + '] 
									from sys.columns 
									where Name = N[' + @Age + ']               
									and Object_ID = Object_ID(N[' + @srcTblName + '])
							) 
							BEGIN
								(CASE WHEN [' + @Age + '] = ''T'' THEN 2 ElSE 1 END) 
							END
						)
						ELSE
						(	(IF Exists(select [' + @age_Limit + ']
									from sys.columns 
									where Name = N[' + @age_Limit + ']               
									and Object_ID = Object_ID(N[' + @srcTblName + '])
							)
									 
							BEGIN
								(CASE WHEN [' + @age_Limit + '] = ''T'' THEN 2 ELSE 1 END)
							END
						)
		END  AS [Age] 
	From Test.dbo.[' + @srcTblName + ']'


	Execute sp_executesql @strSql
	END
	SET @counter = @counter -1 
END

Open in new window

Avatar of Sh M

ASKER

I have many tables with different names, I use sql functions to extract the destination table name. it is not just table that start with emp, but the query is used to extract many names:

source table       destination table    
------------------------------------------
Emp_US_2010    Employee                
Emp_FR_2010    Employee
Emp_UK_2009   Employee
Plan_US_2010   Plan
Plan_IT_2008     Plan

though I have to write separate inset into...select stmt for a unique destination table name and solving the issue above when they arise....
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Note on the above post:

You might already be close to done, so I'm not suggesting you have to start over.  Perhaps 50, 70, 90% of the table columns are identical and can be migrated with dynamic SQL, and you just need to handle a few special cases?
if this is only one time, just write sql statements... test it, if everything is ok, run insert statements...
if it will be automated, then maybe you should modify all underlying tables in all source db (add columns/rename table&columns etc) then write an application to deal with...

one time job, different source into a single sql db with an app / sp : does not look right...
Avatar of Sh M

ASKER

I have to correct myself...there are 6 years of data, 700 tables....

I did populate a single for example employee table from 90 related tables in 20 seconds though I spent a week to write the query, figure the differences between the tables( using info schema during initial assessments,...) as well as searching for any records which was missing in the sql look up tables...

I just don't see myself sitting and typing a very very long script for each and every 700 tables even if they are all identical and without any issue.... The good thing is that I can now re-use the code and make minor modifications to populate other tables, still it may be time consuming but still less crazy than typing all those scripts....

the client has still not confirmed if all the columns from each and every tables is needed and he may change his mind later on, and I personally do not see the wisdom in following the good old path....
Avatar of Sh M

ASKER

all said,
what is this option:

"With a decent text editor, this could be done in a matter of hours.  I assume you're trying to do this without one, and it will be more difficult.  I'm not recommending, however, that you pick one for this project.  Something to consider for the future."


Thanks
I mean if you have a good, modal text editor like Vim, you can duplicate queries and make subtle changes really fast.  If you are using Management Studio or notepad, development time will be really slow.

The reason for doing the above method is simple.  It's easier to write, run, debug short and simple queries.

700 tables?? Yikes.  It sounds like your biggest time consumption on this project will be handling the special cases/schema differences.