How to store the above result in another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to store the above result in another table

Author

Topic

tsaliki
Starting Member

19 Posts

Posted - 2012-11-08 : 00:15:19

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-08 : 00:41:09

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources