SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


copy/paste multiple columns


copy/paste multiple columns

Author
Message
r.v.schaik
r.v.schaik
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 93
Comments posted to this topic are about the item copy/paste multiple columns
David McKinney
David McKinney
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5333 Visits: 2094
I like it -> 5 stars from me. Nice idea, and easily extensible. I'm not sure I'd have gone for the dynamic sql...I'd have left it to the user to go to the right database. But I understand the reasons for this choice. The disadvantage is that it makes it slightly more fiddly to extend.
Micha-1016108
Micha-1016108
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 378
Very helpfully, especialy with the shurtcut.
WHY do you have posted this not 5 years ago!!

BUT I always get an Error when I marked for example schema.tablename and press the short cut.
I have a german system but the Error ist like Syntax Error near by ".".

In German:
Meldung 102, Ebene 15, Status 1, Zeile 1
Falsche Syntax in der Nähe von '.'.
David McKinney
David McKinney
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5333 Visits: 2094
Micha-1016108 (10/10/2013)
Very helpfully, especialy with the shurtcut.
WHY do you have posted this not 5 years ago!!

BUT I always get an Error when I marked for example schema.tablename and press the short cut.
I have a german system but the Error ist like Syntax Error near by ".".

In German:
Meldung 102, Ebene 15, Status 1, Zeile 1
Falsche Syntax in der Nähe von '.'.


It's because it's a string parameter. If you put quotes around it, it will work OK (or if you leave out the schema.)

So you can put

PORTFOLIO
or
'dbo.PORTFOLIO'
r.v.schaik
r.v.schaik
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 93
Micha-1016108 (10/10/2013)
Very helpfully, especialy with the shurtcut.
WHY do you have posted this not 5 years ago!!


Thanks! Next time I will do my best to think about these things 5 years earlier :-D
This little script has been born out of years of frustration about not being able to (easily) select multiple columns. I was actually a bit disappointed myself that I did not think about this earlier.
Dave Vroman
Dave Vroman
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 727
Superb. Love it. I added a couple of columns because i'm constantly doing JOINs and PROCEDUREs.

EXEC(
'SELECT b.Name AS ColumnStart,
'','' + b.Name AS ColumnNext,
''['' + b.Name + '']'' AS ColumnStartBr,
'',['' + b.Name + '']'' AS ColumnNextBr,
''??.'' + b.Name AS ColumnJoin,
'', ??.['' + b.Name + '']'' AS ColumnJoinBr,
'''' + UPPER(t.name) + CASE t.name WHEN ''varchar'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''nvarchar'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''binary'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''varbinary'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''sysname'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''text'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''ntext'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''char'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''sql_variant'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''decimal'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '','' + CAST(b.precision AS VARCHAR(5))+ '')''
ELSE '''' END AS [Declare]
FROM ' + @Database + '.sys.objects a
INNER JOIN ' + @Database + '.sys.columns b ON a.object_id=b.object_id
INNER JOIN ' + @Database + '.sys.types t ON b.system_type_id = t.system_type_id
INNER JOIN ' + @Database + '.sys.schemas d ON a.schema_id=d.schema_id
WHERE a.Object_ID = OBJECT_ID(''' + @Database + '.' + @Schema + '.' + @Object + ''')
AND d.name = ''' + @Schema + '''
'
)


I use the "??" as a simple tag for aliases in joins.
bgrossnickle
bgrossnickle
Right there with Babe
Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)

Group: General Forum Members
Points: 740 Visits: 344
How do you "Put the stored procedure under a shortcut. "?
liver.larson
liver.larson
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 115
nifty.

Is there any way to structure the shortcut so you don't have to wrap the parameter in ' ', so I could just highlight my database.schema.objectname and execute?
DaniSQL
DaniSQL
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 945
bgrossnickle (10/11/2013)
How do you "Put the stored procedure under a shortcut. "?



After you installed the script:

1. Open SSMS
2. Select Tools | Options | Keyboard | Query Shortcuts
3. Choose any shortcut and type sp_ColumnSelect on the stored procedure column on the right and then press enter.
Greg Drake
Greg Drake
Right there with Babe
Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)

Group: General Forum Members
Points: 735 Visits: 536
I really like the multi-column copy/paste trick you came up with in the results. Also, the tempdb integration is very simple.

I have a similar script that I have begun modifying again based on what I liked in yours. Thank you for sharing! Maybe I'll put mine up some time, too.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search