Solved

sql server, passing table name as a parameter to query

Posted on 2011-09-26
18
260 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:shmz
  • 6
  • 6
  • 5
  • +1
18 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 36653097
you cannot use this

Insert Into @desTblName ...

instead use dynamic sql liek this:

declare @sql varchar(max) = "Insert Into " + @desTblName + " ...";
exec(@sql)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36653805
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
0
 
LVL 7

Expert Comment

by:mmr159
ID: 36654089
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.
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 300 total points
ID: 36654662
maybe you want to say this

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

Author Comment

by:shmz
ID: 36676300
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)
0
 

Author Comment

by:shmz
ID: 36684938
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

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36707643
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.
0
 
LVL 7

Expert Comment

by:mmr159
ID: 36708890
>    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?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36710733
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);
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 51

Expert Comment

by:HainKurt
ID: 36710743
did you ask the same question multiple times? I do remember the same or similar question before...
0
 

Author Comment

by:shmz
ID: 36714076
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

0
 

Author Comment

by:shmz
ID: 36714101
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....
0
 
LVL 7

Assisted Solution

by:mmr159
mmr159 earned 200 total points
ID: 36716241
> 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.

What you are doing is an extract, transform, load (ETL).  You have records in approximately 140 (7x20) tables that need to go into multiple tables a single database depending on their existence in the destination.

I think we need to take a step back and look at the bigger picture.  Since this is a one-time task and has many special-case situations (column matching), you should do this manually.  That is, write each query as a standalone statement.  It looks like you are trying to save time by getting fancy with dynamic SQL, but it's not working out because of all the special cases.  In the end, you will probably save time by explicitly moving records one table at a time.

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.

You can use INFORMATION_SCHEMA.COLUMNS to help you with returning the names of each table.  See here:

http://codesnippets.joyent.com/posts/show/337

This will help you build the queries you need to get this done.
0
 
LVL 7

Expert Comment

by:mmr159
ID: 36716253
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?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36717310
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...
0
 

Author Comment

by:shmz
ID: 36899809
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....
0
 

Author Comment

by:shmz
ID: 36899818
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
0
 
LVL 7

Expert Comment

by:mmr159
ID: 36903312
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now