SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to store the above result in another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tsaliki
Starting Member

India
19 Posts

Posted - 11/08/2012 :  00:15:19  Show Profile  Reply with Quote
i have a table with records and code as follows:

create table companieswithexec
(
[companyname] varchar(100),
[exec first Name 1] varchar(100),
[exec last Name 1] varchar (100),
[exec first Name 2] varchar(100),
[exec last Name 2] varchar (100),
[exec first Name 3] varchar(100),
[exec last Name 3] varchar (100),
[exec first Name 4] varchar(100),
[exec last Name 4] varchar (100),
[exec first Name 5] varchar(100),
[exec last Name 5] varchar (100),
[exec first Name 6] varchar(100),
[exec last Name 6] varchar (100),
[exec first Name 7] varchar(100),
[exec last Name 7] varchar (100)
)

insert into companieswithexec values ('sandisk corp','adams','paul','jacob','david','smith','alexander','russel','sasi','dravid','alex','ruth','kiran','geary','michael')


insert into companieswithexec values ('altech corp','george','eric','john','shyam','scott','todd','richard','joseph','mark','andrew','strauss','neil','johnson','steve')


insert into companieswithexec values ('xyz corp','mike','peter','saurav','jay','gary','james','lara','laxman','gilli','sachin','adam','waugh','mark','chris')


the code is :


DECLARE @COLNAME VARCHAR(50)
DECLARE @SQL VARCHAR(max)
DECLARE @COL_NBR VARCHAR(5)
DECLARE @COMPANY VARCHAR(100)
SET @COMPANY = 'sandisk corp,xyz corp,abc,altech corp '
SET @SQL = ''
-----------
DECLARE COL_CUR CURSOR FOR SELECT [NAME] FROM SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('companieswithexec') AND [NAME] LIKE 'exec first Name%'
OPEN COL_CUR FETCH NEXT FROM COL_CUR INTO @COLNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COL_NBR = (SELECT REPLACE(@COLNAME,'exec first Name ',''))
SET @SQL = @SQL + (SELECT 'SELECT [companieswithexec].[companyname] as [company],[exec first Name ' + @COL_NBR + '] AS [exec first Name],[exec last Name ' + @COL_NBR + '] AS [exec last Name] FROM [companieswithexec] ')
SET @SQL = @SQL + ' UNION '
FETCH NEXT FROM COL_CUR INTO @COLNAME
END
CLOSE COL_CUR
DEALLOCATE COL_CUR
SET @SQL = (SELECT REPLACE(@SQL + ' ',' UNION ',''))
SET @SQL = ';WITH CTE ([companyname],[exec first Name],[exec last Name]) AS (' + @SQL + ')'
SET @SQL = @SQL + 'SELECT * FROM CTE WHERE [companyname] IN (''' + replace(@COMPANY,',',''',''') + ''')'

EXEC (@SQL)


I wanted to store this output in another table.So i created other table as below and tried the below code but not working.So please help me

create table testexecutives
(
firstname varchar(100),
secondname varchar(100),
companyname varchar(max)
)



the code i tried is as follows:

;with cte1(Companyname,[exec first name],[exec Last Name])
as
(
DECLARE @COLNAME VARCHAR(50)
DECLARE @SQL VARCHAR(max)
DECLARE @COL_NBR VARCHAR(5)
DECLARE @COMPANY VARCHAR(100)
SET @COMPANY = 'sandisk corp,xyz corp,abc,altech corp '
SET @SQL = ''
-----------
DECLARE COL_CUR CURSOR FOR SELECT [NAME] FROM SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('companieswithexec') AND [NAME] LIKE 'exec first Name%'
OPEN COL_CUR FETCH NEXT FROM COL_CUR INTO @COLNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COL_NBR = (SELECT REPLACE(@COLNAME,'exec first Name ',''))
SET @SQL = @SQL + (SELECT 'SELECT [companieswithexec].[companyname] as [company],[exec first Name ' + @COL_NBR + '] AS [exec first Name],[exec last Name ' + @COL_NBR + '] AS [exec last Name] FROM [companieswithexec] ')
SET @SQL = @SQL + ' UNION '
FETCH NEXT FROM COL_CUR INTO @COLNAME
END
CLOSE COL_CUR
DEALLOCATE COL_CUR
SET @SQL = (SELECT REPLACE(@SQL + ' ',' UNION ',''))
SET @SQL = ';WITH CTE ([companyname],[exec first Name],[exec last Name]) AS (' + @SQL + ')'
SET @SQL = @SQL + 'SELECT * FROM CTE WHERE [companyname] IN (''' + replace(@COMPANY,',',''',''') + ''')'

EXEC (@SQL)

)

insert into sreenu (Companyname,firstname,LastName) values
select companyname,firstname,lastname from cte1



but i am getting error.

So can you please tell me how to store the the above output into another table.Thank you

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 11/08/2012 :  00:41:09  Show Profile  Reply with Quote
I modified some code......

Run below script once...

create table companieswithexec
(
[companyname] varchar(100),
[exec first Name 1] varchar(100),
[exec last Name 1] varchar (100),
[exec first Name 2] varchar(100),
[exec last Name 2] varchar (100),
[exec first Name 3] varchar(100),
[exec last Name 3] varchar (100),
[exec first Name 4] varchar(100),
[exec last Name 4] varchar (100),
[exec first Name 5] varchar(100),
[exec last Name 5] varchar (100),
[exec first Name 6] varchar(100),
[exec last Name 6] varchar (100),
[exec first Name 7] varchar(100),
[exec last Name 7] varchar (100)
)

insert into companieswithexec values ('sandisk corp','adams','paul','jacob','david','smith','alexander','russel','sasi','dravid','alex','ruth','kiran','geary','michael')


insert into companieswithexec values ('altech corp','george','eric','john','shyam','scott','todd','richard','joseph','mark','andrew','strauss','neil','johnson','steve')


insert into companieswithexec values ('xyz corp','mike','peter','saurav','jay','gary','james','lara','laxman','gilli','sachin','adam','waugh','mark','chris')


create table testexecutives
(
firstname varchar(100),
secondname varchar(100),
companyname varchar(max)
)



--the code is :
DECLARE @COLNAME VARCHAR(50)
DECLARE @SQL VARCHAR(max)
DECLARE @COL_NBR VARCHAR(5)
DECLARE @COMPANY VARCHAR(100)
DECLARE @tempTab varchar(50) = 'testexecutives'
SET @COMPANY = 'sandisk corp,xyz corp,abc,altech corp '
SET @SQL = ''
-----------
DECLARE COL_CUR CURSOR FOR SELECT [NAME] FROM SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('companieswithexec') AND [NAME] LIKE 'exec first Name%'
OPEN COL_CUR FETCH NEXT FROM COL_CUR INTO @COLNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COL_NBR = (SELECT REPLACE(@COLNAME,'exec first Name ',''))
SET @SQL = @SQL + (SELECT 'SELECT [companieswithexec].[companyname] as [company],[exec first Name ' + @COL_NBR + '] AS [exec first Name],[exec last Name ' + @COL_NBR + '] AS [exec last Name] FROM [companieswithexec] UNION ')
FETCH NEXT FROM COL_CUR INTO @COLNAME
END
CLOSE COL_CUR
DEALLOCATE COL_CUR
--PRINT @sql
SET @SQL = ';WITH CTE ([companyname],[exec first Name],[exec last Name]) AS (' + left(@SQL, len(@sql) - 6) + ')'
SET @SQL = @SQL + ' INSERT INTO '+ @tempTab + ' OUTPUT INSERTED.* SELECT * FROM CTE WHERE [companyname] IN (''' + replace(@COMPANY,',',''',''') + ''')'
select @SQL
EXEC (@SQL)

SELECT * FROM testexecutives

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000